Index Range Scan
2024-07-21 02:34:19
供稿：網友
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 readsSQL> 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 getsSQL> 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
