DML操作对索引的影响

delete操作

delete是对整行数据的操作，只要有索引存在，都会被影响（列值为null除外），delete删除数据的同时，索引叶块上对应的索引条目也会被打上删除标记，类似于逻辑删除，也就是说索引条目并没有被清理，仍然保存在索引块中，被标记删除的条目随后会被重用或者被延迟块处理过程清除，但是这部分空间很难被重用，除非插入适合该索引条目的数据，才能在相同的索引键位置插入数据。

下面看看数据被删除后索引块数量的情况

SQL> create table test as select * from dba_objects where object_id < 30000;

Table created.

SQL> create index idx_test_id on test(object_id);

Index created.

SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_
name;
INDEX_NAME		       LEAF_BLOCKS     BLOCKS
------------------------------ ----------- ----------
IDX_TEST_ID				65	   80

索引创建后，占据80数据块，其中叶块65个，下面删除一部分数据

SQL> delete from test where object_id > 20000;

9999 rows deleted.

SQL> commit;

Commit complete.

SQL> begin
  2  dbms_stats.gather_table_stats('qbuser','test');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_
name;
INDEX_NAME		       LEAF_BLOCKS     BLOCKS
------------------------------ ----------- ----------
IDX_TEST_ID				43	   80

删除一部分数据后，叶块数量减少，但整个索引占据的块数依然是没有变化，叶块数量减少是由于前面删除的数据object_id是连续的，删除后部分索引块都会变成空块，也就会从叶块上脱落，如果删除不连续的数据，叶块数量不会发生变化

SQL> drop table test;

Table dropped.

SQL> create table test as select * from dba_objects where object_id < 30000;

Table created.

SQL> create index idx_test_id on test(object_id);

Index created.

SQL> select i