oracle中删除记录时将索引一块删除吗？

秦一

我想关注的焦点不应该是何时被物理删除，何谓物理删除，只不过告诉可以在这里写入了，难道还要从硬盘上把其记录的磁信息抹掉

焦点应该是索引的空间如何被重用与回收以及其对性能和空间管理的影响。

首先，空间何时被重用，假设索引已创建，如果删除了一条记录，再插入数据时，如果索引码不完全一致，是不会重用这个空间的。经过了大量的删除，更新，插入后，对性能是否有影响呢？说对性能没影响，我推测应该是相对于之前的操作说的。确切的说，对性能是没影响，但是重建索引的话对性能会有提高.比如说索引范围扫描，不重建索引的话，扫描的块数应该与之前相差无几，但如果重建索引后，应该填补了那些‘空洞’，使扫描的的块数减少吧。

其次，空间何时被回收，前面已经讲的很明白，索引的FREELIST上的块必须是空的，不能留有索引条目，这时此块才能够被重用。所以如果不重建的话，有可能造成空间的浪费。


推测一下oracle的插入过程，当插入数据时，依据索引码找到叶节点，这时或者重用，或者使用空闲空间，或者分裂叶节点，在FREELIST上找到空闲块使用。比如如果最初索引码为3，4，7。删除4这个索引码，再插入5这个索引码，我想即使与4 不同，应该也是可以重用4这个空洞的。不知对否？

biti_rainy

SQL> create table t as select * from dba_objects where object_id is not null;

表已创建。

SQL> create index t_index on t(object_id);

索引已创建。
SQL> desc t
名称                                      是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(18)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)


SQL> select FILE_ID,EXTENT_ID,BLOCK_ID       from  dba_extents where segment_name = 'T_INDEX';

   FILE_ID  EXTENT_ID   BLOCK_ID
---------- ---------- ----------
         9          0        569
         9          1        577
         9          2        585
         9          3        593
         9          4        601
         9          5        609
         9          6        617
         9          7        625
         9          8        633
         9          9        641

已选择10行。

SQL> select min(object_id) from t;

MIN(OBJECT_ID)
--------------
             2
由于是ASSM 类型的表空间，我通过其他方式找到索引叶子节点的开始位置为  block_id  573


SQL> alter system dump datafile 9 block 573;

kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: -----, lock: 0   8024表示block中的偏移bytes，也就是位置
col 0; len 2; (2):  c1 03   --------这个值是2
col 1; len 6; (6):  02 40 00 6d 00 1f
row#1[8012] flag: -----, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  02 40 02 2f 00 26
row#2[8000] flag: -----, lock: 0
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  02 40 00 94 00 16



SQL> delete from t where object_id = 2;

已删除 1 行。

SQL> commit;

提交完成。


SQL> alter system dump datafile 9 block 573;

系统已更改。
row#0[8024] flag: ---D-, lock: 2    ---D-表示被删除
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  02 40 00 6d 00 1f
row#1[8012] flag: -----, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  02 40 02 2f 00 26
row#2[8000] flag: -----, lock: 0
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  02 40 00 94 00 16



SQL> insert into t values(1,1,1,1,1,1,sysdate,sysdate,1,1,1,1,1);

已创建 1 行。

SQL> commit;

提交完成。


SQL>  alter system dump datafile 9 block 573;


kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[1822] flag: -----, lock: 2     -插入位置发生了变化，相当于是update提升到了pctfree保留部分
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  02 40 01 be 00 00
row#1[8012] flag: -----, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  02 40 02 2f 00 26
row#2[8000] flag: -----, lock: 0
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  02 40 00 94 00 16



SQL> delete t where object_id = 1;

已删除 1 行。

SQL> commit;

提交完成。

SQL> insert into t values(1,1,1,1,1,1,sysdate,sysdate,1,1,1,1,1);

已创建 1 行。

SQL> commit;

提交完成。

SQL>

row#0[1810] flag: -----, lock: 2    我们发现，即使删除后立即插入相同的值，也不会立即重用以前的空间1822，而是依然在新的位置1810
col 0; len 2; (2):  c1 02
col 1; len 6; (6):  02 40 01 be 00 01
row#1[8012] flag: -----, lock: 0
col 0; len 2; (2):  c1 04
col 1; len 6; (6):  02 40 02 2f 00 26
row#2[8000] flag: -----, lock: 0
col 0; len 2; (2):  c1 05
col 1; len 6; (6):  02 40 00 94 00 16


如果继续测试下去会发生适当的时候block会重整
结论：
所谓重用 的观点应该针对block来说而不应该针对row所在的位置
只要key  value 落在该block可接受的范围内都可以重用，而不必关心是否完全相同
对于block里面的记录来说，物理位置并不是跟key顺序有关，可能由于 delete/update 等而发身变化

秦一

恩，澄清了一个观点，俺以前以为在块内也是顺序排列的。

顺便问一下，alter system dump 的结果显示在那里？

秦一

还有，使用show_space之前要执行那个脚本？

biti_rainy

1：索引的 pctfree 是用来插入新的可容纳入该block可接受的范围的key value 的
当然pctfree也会跟 initrans  --->  maxtrans 的 ITL 有关

2：删除时候的 索引上的记录被标记为 D  ,该block上被查询的时候该记录存在，若该block上一旦发生下一个事务，该被标记删除记录立即被清除

btw: alter system dump 结果在 user  trace  file 里啊，我没有运行 show_space

Yong Huang

最初由 biti_rainy 发布
[B]2：删除时候的 索引上的记录被标记为 D  ,该block上被查询的时候该记录存在，若该block上一旦发生下一个事务，该被标记删除记录立即被清除
[/B]

Your test is quite interesting. I find that the D marker can even be cleared by a rollback. That is,

delete from table
dump the block and you see D marker
rollback
dump again and you won't see the marker

Dumping a data block is done with alter system, which is not a DDL. So you can rollback.

At first I thought clearing that marker was done by delayed block cleanout. But full scan on the table didn't clear it. So it's not related to block cleanout.

Yong Huang

biti_rainy

I find that the D marker can even be cleared by a rollback
At first I thought clearing that marker was done by delayed block cleanout

rollback 自然应该清除  D 标记，因为rollback 应该把凡是改变过的恢复原样

假如delay  block cleanout ,而回滚段被覆盖，只是能看出事务结束了，而无法知道是 提交还是回退了，所以rollback不会 delay  block  cleanout

AmorousPoem

SQL> select FILE_ID,EXTENT_ID,BLOCK_ID from dba_extents where segment_name = 'T_INDEX';

FILE_ID EXTENT_ID BLOCK_ID
---------- ---------- ----------
9 0 569
9 1 577
9 2 585
9 3 593
9 4 601
9 5 609
9 6 617
9 7 625
9 8 633
9 9 641

已选择10行。

SQL> select min(object_id) from t;

MIN(OBJECT_ID)
--------------
2

由于是ASSM 类型的表空间，我通过其他方式找到索引叶子节点的开始位置为 block_id 573 [/COLOR]
=========================

这个开始位置的blockid 573是如何确定的呢？有什么标准吗？[

lcmlsj

从以下大概可以说明第一个叶块号：
DBA Ranges :
  --------------------------------------------------------
   0x020001ad  Length: 5      Offset: 0      
   0x020001b2  Length: 5      Offset: 5      
   0x020001b7  Length: 5      Offset: 10     
  
   0:Metadata   1:Metadata   2:Metadata   3:25-50% free
   4:FULL   5:FULL   6:FULL   7:FULL
   8:FULL   9:FULL   10:FULL   11:FULL
   12:FULL   13:FULL   14:FULL
0，1，3用作段头，第三块不满，可以认为是branch块，那第四块为第一页块。

wdjk1999

索引其实好复杂啊，头大