/ num_rows) * 100, 2) as "RT%"
5 from dba_tables
6 where table_name = 'T_ITDBA_1';
TABLE_NAME NUM_ROWS CHAIN_CNT RT%
------------------------------ ---------- ---------- ----------
T_ITDBA_1 1291136 0 0
Elapsed: 00:00:00.06
经过查询，没有产生链表！那么这种更新，很有可能数据直接转移到新的块上还是？这种现象到底什么原因呢？对于这个问题，我重新实验
SQL> drop table T_ITDBA_1;
Table dropped.
Elapsed: 00:00:02.24
SQL> create table t_itdba_1 tablespace itdba_1 as select * from dba_extents;
Table created.
Elapsed: 00:00:16.58
SQL> exec dbms_stats.gather_table_stats(user,'T_ITDBA_1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.23
SQL> select bytes/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';
BYTES/1024 SEGMENT_NAME
---------- ------------------------------
896 T_ITDBA_1
Elapsed: 00:00:00.25
SQL>
查询该表数据行于数据块之间的对应关系
75159 6 18589 87
75159 6 18620 110
75159 6 18626 108
75159 6 18635 76
75159 6 18657 97
75159 6 18574 129
75159 6 18577 132
75159 6 18582 120
OBJECT_ID FILE_ID BLOCK_ID COUNT(BLOCK_ID)
---------- ---------- ---------- ---------------
75159 6 18588 127
75159 6 18598 90
75159 6 18604 78
75159 6 18616 104
75159 6 18617 107
75159 6 18627 105
数据很多,我把它存放到一张表里
SQL> create table t_itdba_01 as
select object_id, file_id, block_id, count(block_id) countt
2 3 from (select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id
4 5 6 7 FROM T_ITDBA_1)
8 group by object_id, file_id, block_id;
Table created.
Elapsed: 00:00:00.55
SQL> update T_ITDBA_1 set SEGMENT_NAME='abcde000000000000000000000';
10294 rows updated.
Elapsed: 00:00:00.26
SQL> commit;
Commit complete.
Elapsed: 00:00:00.05
SQL> exec dbms_stats.gather_table_stats(user,'T_ITDBA_1');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.21
SQL> select table_name,
2 num_rows,
3 CHAIN_CNT,
4 ROUND((CHAIN_CNT / num_rows) * 100, 2) as "RT%"
5 from dba_tables
6 where table_name = 'T_ITDBA_1';
TABLE_NAME NUM_ROWS CHAIN_CNT RT%
------------------------------ ---------- ---------- ----------
T_ITDBA_1 10294 0 0
Elapsed: 00:00:00.06
SQL> select bytes/1024 ,segment_name from dba_segments where segment_name='T_ITDBA_1';
BYTES/1024 SEGMENT_NAME
---------- ------------------------------
960 T_ITDBA_1
Elapsed: 00:00:00.01
SQL>
占用空间增加,创建映射表
SQL> create table t_itdba_02 as
2 select object_id, file_id, block_id, count(block_id) countt
3 from (select rowid,
4 dbms_rowid.rowid_object(rowid) object_id,
5 dbms_rowid.rowid_relative_fno(rowid) file_id,
6 dbms_rowid.rowid_block_number(rowid) block_id
7 FROM T_ITDBA_1)
8 group by object_id, file_id, block_id;
Table created.
Elapsed: 00:00:00.51
SQL>
比对数据
?
?
SQL> select a.block_id, b.countt - a.countt
2 from t_itdba_01 a, t_itdba_02 b
3 where a.block_id = b.block_id
4 and (b.countt - a.countt) <> 0;
no rows selected
Elapsed: 00:00:00.00
SQL>
占用的空间增加,使用的块数没增加,难道仅仅使用块的free部分,碎片到底怎么产生的,还是我测试力度不够! 也就是说,数据库什么时候会用到delete 部分的数据空间??
1 exten 分配产生，2 行连接，3 行迁移
?
在分配上：做个实验
?
update T_ITDBA_1 set owner='2dfadafsfsafdsafdsf';
?
?
SQL> select segment_name,extent_id,block_id,bytes from dba_extents where segment_name= 'T_ITDBA_1';
SEGMENT_NAME EXTENT_