SQL> CREATE TABLE t1 NOLOGGING AS SELECT CAST(MOD(ROWNUM, 2) AS NUMBER) col1 FROM all_tab_columns;
SQL> CREATE INDEX ind1 ON t1(col1) NOLOGGING;
SQL> SET AUTOTRACE ON EXPLAIN STATISTICS
SQL> SELECT COUNT(*) FROM t1 WHERE col1 = 0;
COUNT(*)
----------
22599
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IND1' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
42 consistent gets
41 physical reads
SQL> SELECT COUNT(*) FROM t1 WHERE col1 = 2;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IND1' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
SQL> DELETE FROM t1;
45199 rows deleted.
SQL> COMMIT;
SQL> SELECT COUNT(*) FROM t1 WHERE col1 = 0;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IND1' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
47 consistent gets
SQL> SELECT COUNT(*) FROM t1 WHERE col1 = 2;
COUNT(*)
----------
0
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IND1' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
为何Index Range Scan 在表数据被删除索引“清空”后，仍然产生了很多的consistent I/O哪？
归结于Index Range Scan 的实现方式。
当大部分纪录(100% or 99%) 从表中删除后， 索引叶结点(Index Leaf Block)内索引项（index entry）被标记为删除(“D”),但索引枝节点(Index Branch Block)内数据（索引数值和对应的叶结点地址）并不改变； Oracle并不会将空的索引叶结点(Index Leaf Block)从 索引枝节点(Index Branch Block)内删去。
当使用Index Range Scan查找数值时，Oracle判定该数值是否落在某个索引枝节点(Index Branch Block)的数值范围内；然后根据存储在Branch block中的Leaf 地址去查找索引叶结点。
由于例子中，记录全部被删除，索引叶节点内的索引项index entry都被标记为删除"D",因此Oracle无法在当前索引叶结点内找到满足条件的纪录，将会继续查找下一个索引叶结点(Index Leaf Block)。[Note: 索引叶结点之间有双向指针]
这样本例中，Index Range Scan将会从第一个满足范围的索引枝节点进入索引叶结点，再顺序访问它的所有“右侧“兄弟索引叶结点；但直到访问了“右侧“的所有叶结点，也不能够找到Range Scan的出口（满足条件的数值或者比该数值大的数值）[B-tree索引数值顺序排序] ； 处理该问题的常见的方法是合并coalesce或者重建rebuild.
如下部分Block Dump片断:
leaf: 0x2880002d 679477293 (1: nrow: 552 rrow: 0)
Leaf block dump
===============
header address 2216058972=0x8416605c
kdxlespl 0
kdxlende 552
kdxlenxt 679477294=0x2880002e
kdxleprv 679477292=0x2880002c
kdxledsz 0
kdxlebksz 8032
row#0[8021] flag: ---D-, lock: 2
col 0; len 1; (1): 80
col 1; len 6; (6): 28 80 00 0d 00 e2
row#1[8010] flag: ---D-, lock: 2
col 0; len 1; (1): 80
col 1; len 6; (6): 28 80 00 0d 00 e4
row#2[7999] flag: ---D-, lock: 2
col 0; len 1; (1): 80
col 1; len 6; (6): 28 80 00 0d 00 e6
删除的索引都被标记为"flag: ---D-"
索引的平衡数结构
grep -i nrow: sprrprd1_ora_18380.trc
branch: 0x2880002a 679477290 (0: nrow: 86, level: 1)
leaf: 0x2880002b 679477291 (-1: nrow: 0 rrow: 0)
leaf: 0x2880002c 679477292 (0: nrow: 0 rrow: 0)
leaf: 0x2880002d 679477293 (1: nrow: 552 rrow: 0)
leaf: 0x2880002e 679477294 (2: nrow: 552 rrow: 0)
leaf: 0x2880002f 679477295 (3: nrow: 552 rrow: 0)
leaf: 0x28800030 679477296 (4: nrow: 552 rrow: 0)
leaf: 0x28800031 679477297 (5: nrow: 552 rrow: 0)
leaf: 0x28800032 679477298 (6: nrow: 552 rrow: 0)
删除纪录后，索引枝节点内数据无变化
Branch block dump
=================
header address 27619460=0x1a57084
kdxcolev 1
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 85
kdxcofbo 198=0xc6
kdxcofeo 6907=0x1afb
kdxcoavs 6709
kdxbrlmc 679477291=0x2880002b
kdxbrsno 0
kdxbrbksz 8056
row#36[7575] dba: 687865888=0x29000020
col 0; len 1; (1): 80
col 1; len 6; (6): 29 00 00 12 01 15
row#37[7562] dba: 687865889=0x29000021
col 0; len 1; (1): 80
col 1; len 6; (6): 29 00 00 14 00 3b
row#38[7549] dba: 687865890=0x29000022
col 0; len 1; (1): 80
col 1; len 6; (6): 29 00 00 15 01 f6
row#39[7536] dba: 687865891=0x29000023
col 0; len 1; (1): 80
col 1; len 6; (6): 29 00 00 17 01 1c
row#40[7522] dba: 687865892=0x29000024
col 0; len 2; (2): c1 02
col 1; len 6; (6): 28 80 00 0a 00 3c
row#41[7508] dba: 687865893=0x29000025
col 0; len 2; (2): c1 02
col 1; len 6; (6): 28 80 00 0b 01 a7
row#42[7494] dba: 687865894=0x29000026
col 0; len 2; (2): c1 02
col 1; len 6; (6): 28 80 00 0d 00 7d
row#43[7480] dba: 687865895=0x29000027
col 0; len 2; (2): c1 02
col 1; len 6; (6): 28 80 00 0e 01 e8
row#44[7466] dba: 687865896=0x29000028
col 0; len 2; (2): c1 02
col 1; len 6; (6): 28 80 00 10 00 be
|
