oracle 频繁操作删除对索引的影响吗,delete语句对索引的影响之分析

所用测试表与上篇文章相同。

删除第一个叶节点中的key值对应的记录：

SQL> delete from test_idx

2 where object_id<500

3 /

已删除8行。

SQL> conn system/oracle

已连接。

重新导出第一个数据块：

SQL> alter system dump datafile 4 block 229;

系统已更改。

打开导出文件，可以发现被删除的记录对应的key值被标记了一个字符“D”，但数据并未被真正删除

row#0[8023]flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 03 3b

col 1; len 6; (6): 01 00 00 a4 00 00

row#1[8010] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 03 3c

col 1; len 6; (6): 01 00 00 a4 00 01

row#2[7997] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 0c

col 1; len 6; (6): 01 00 00 a4 00 02

row#3[7984] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 0e

col 1; len 6; (6): 01 00 00 a4 00 03

row#4[7971] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 0f

col 1; len 6; (6): 01 00 00 a4 00 04

row#5[7958] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 11

col 1; len 6; (6): 01 00 00 a4 00 05

row#6[7945] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 12

col 1; len 6; (6): 01 00 00 a4 00 06

row#7[7932] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 14

col 1; len 6; (6): 01 00 00 a4 00 07

row#8[7919] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 07 06

再向表中添加记录，使其对应索引中的key值被加入第一个叶节点：

SQL> conn scott/tiger

已连接。

SQL> insert into test_idx

2 select * from all_objects

3 where object_id<500

4 /

已创建8行。

SQL> commit;

提交完成。

SQL> conn system/oracle

已连接。

SQL> alter system dump datafile 4 block 229;

系统已更改。

查看新的导出文件内容，注意上面被标记为删除的那些键值的变化：

row#0[1709] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 03 3b--数据与删除前相同

col 1; len 6; (6): 01 00 00 df 00 00--rowid发生改变

row#1[1722] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 03 3c

col 1; len 6; (6): 01 00 00 df 00 01

row#2[1735] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 04 0c

col 1; len 6; (6): 01 00 00 df 00 02

row#3[1748] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 04 0e

col 1; len 6; (6): 01 00 00 df 00 03

row#4[1761] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 04 0f

col 1; len 6; (6): 01 00 00 df 00 04

row#5[1774] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 04 11

col 1; len 6; (6): 01 00 00 df 00 05

row#6[1787] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 04 12

col 1; len 6; (6): 01 00 00 df 00 06

row#7[1800] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 04 14

col 1; len 6; (6): 01 00 00 df 00 07

row#8[7919] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 07 06--原来未被删除的数据

col 1; len 6; (6): 01 00 00 a4 00 08--rowid未发生改变

我们发现，删除标记没有了，虽然键值还是原来的(因为我们添加的记录与刚才删除的相同)，但是rowid已经发生改变(侧面说明表中新添加的记录不是严格重用其对应的删除记录所占用的空间)，而原来未被删除的键值的rowid没有发生变化。

如果新添加的数据不和原来删除的重复呢？

我们继续实验：

SQL> conn scott/tiger

已连接。

SQL> delete from test_idx

2 where object_id<800

3 /

已删除9行。

SQL> commit;

提交完成。

SQL> conn system/oracle

已连接。

SQL> alter system dump datafile 4 block 229;

系统已更改。

查看导出文件内容：

row#0[1709] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 03 3b

col 1; len 6; (6): 01 00 00 df 00 00

row#1[1722] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 03 3c

col 1; len 6; (6): 01 00 00 df 00 01

row#2[1735] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 0c

col 1; len 6; (6): 01 00 00 df 00 02

row#3[1748] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 0e

col 1; len 6; (6): 01 00 00 df 00 03

row#4[1761] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 0f

col 1; len 6; (6): 01 00 00 df 00 04

row#5[1774] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 11

col 1; len 6; (6): 01 00 00 df 00 05

row#6[1787] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 12

col 1; len 6; (6): 01 00 00 df 00 06

row#7[1800] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 04 14

col 1; len 6; (6): 01 00 00 df 00 07

row#8[7919] flag: ---D--, lock: 2, len=13

col 0; len 3; (3): c2 07 06

col 1; len 6; (6): 01 00 00 a4 00 08

row#9[7906] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 09 57

col 1; len 6; (6): 01 00 00 a4 00 09

row#10[7893] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 09 58

col 1; len 6; (6): 01 00 00 a4 00 0a

……

row#476[1839] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 12 2d

col 1; len 6; (6): 01 00 00 a9 00 4e

row#477[1826] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 12 2e

col 1; len 6; (6): 01 00 00 a9 00 4f

row#478[1813] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 12 2f

col 1; len 6; (6): 01 00 00 aa 00 00

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 229 maxblk 229

我们再添加记录，然后导出数据块：

SQL> alter table test_idx modify created null;

表已更改。

SQL> alter table test_idx modify last_ddl_time null;

表已更改。

SQL> insert into test_idx(owner,object_id,object_name)

2 values('LAW',345,'HELLO')

3 /

已创建 1 行。

SQL> commit;

提交完成。

SQL> conn system/oracle

已连接。

SQL> alter system dump datafile 4 block 229;

系统已更改。

查看导出文件内容：

row#0[1696] flag: ------, lock: 2, len=13

col 0; len 3; (3): c2 04 2e--块中的第一个key值发生改变，继续查看可以发现，块中的所有键值经过了重新排列

col 1; len 6; (6): 01 00 00 df 00 08

row#1[7906] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 09 57

col 1; len 6; (6): 01 00 00 a4 00 09

row#2[7893] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 09 58

col 1; len 6; (6): 01 00 00 a4 00 0a

row#3[7880] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 09 5a

col 1; len 6; (6): 01 00 00 a4 00 0b

row#4[7867] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 09 5b

col 1; len 6; (6): 01 00 00 a4 00 0c

row#5[7854] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 0a 02

col 1; len 6; (6): 01 00 00 a4 00 0d

row#6[7841] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 0a 04

col 1; len 6; (6): 01 00 00 a4 00 0e

row#7[7828] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 0a 06

col 1; len 6; (6): 01 00 00 a4 00 0f

row#8[7815] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 0a 08

col 1; len 6; (6): 01 00 00 a4 00 10

row#9[7802] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 0a 0a

col 1; len 6; (6): 01 00 00 a4 00 11

row#10[7789] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 0a 0c

col 1; len 6; (6): 01 00 00 a4 00 12

row#11[7776] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 0a 0e

col 1; len 6; (6): 01 00 00 a4 00 13

……

row#467[1852] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 12 2b

col 1; len 6; (6): 01 00 00 a9 00 4d

row#468[1839] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 12 2d

col 1; len 6; (6): 01 00 00 a9 00 4e

row#469[1826] flag: ------, lock: 0, len=13

col 0; len 3; (3): c2 12 2e

col 1; len 6; (6): 01 00 00 a9 00 4f

row#470[1813] flag: ------, lock: 0, len=13--row＃比原来减少8

col 0; len 3; (3): c2 12 2f

col 1; len 6; (6): 01 00 00 aa 00 00

----- end of leaf block dump -----

End dump data blocks tsn: 4 file#: 4 minblk 229 maxblk 229

整个数据块中，标记删除的已经都被清空，现在第一个叶节点只剩下471个键值(原来最后一个row#为478)，键值也经过了重新排序。

结论：表中记录被删除后，索引中对应的键值不会被删除(即所占用空间不会释放)，而只是加上删除标记。

如果以后有新的键值(一个或多个)加进这个数据块，则块中标记删除的键值都会清除。注意索引中键值的删除与事务提交没有关系。

update语句相当于先delete再insert，实验步骤与结果与上面相同。