Oracle最常用的B樹索引的5種存取方法
今天我們討論下Oracle資料庫中最常用的B樹索引,首先我們先來看一下Oracle資料庫裡B樹索引的結構。
我們可以看出,Oracle資料庫裡的B樹索引就好像一顆倒長的樹,它包含兩種類型的資料區塊。 一種是索引分支塊(L1-1,L1-2),另一種是索引葉子塊(L0-1,L0-2,L0-3,L0-4,L0-5,L0-6)。 索引分支塊包含指向相應索引分支塊和葉子塊的指標和索引索引值列.索引索引值列不一定是完整的索引索引值, 它可能只是索引索引值的首碼,只要Oracle 能通過這些首碼區分相應的索引分支塊,葉子塊就行,這樣Oracle就能夠既節省索引分支塊的儲存空間,又可以快速定位其下層的索引分支塊,葉子塊。索引分支塊最上層的那個塊就是所謂的索引根節點。就是圖中的最上層root包含BC的塊。在Oracle裡訪問B書索引的操作都必須從根節點開始,都會經曆一個根節點到分支塊再到葉子塊的過程。
索引葉子塊包含索引索引值和用於定位該索引索引值實際的資料行在表中的實際實體儲存體位置的rowid。
對於唯一性B樹索引而言,ROWID是儲存在索引行的行頭,所以此時Oracle不需要儲存該rowid的長度。
而對於非唯一性B樹索引而言,ROWID被當作額外的列與索引索引值列一起儲存,所以此時Oracle既要儲存rowid,同時又要儲存其長度,這意味著在同等條件下,唯一性B樹索引要比非唯一性B樹索引節省索引葉子塊的儲存空間。對於非唯一性索引而言,B樹索引的有序性體現在Oracle會按照索引索引值和rowid來聯合排序。Oracle索引葉子塊是雙向指標鏈表,它能把左右的索引葉子塊相互串連起來,而無須經曆一個根節點到分支塊再到葉子塊的過程遍曆。
正是由於B樹索引的結構特點,Oracle 資料庫中的B樹索引有以下優勢。
1.所有的索引葉子塊都在同一層,他們索引深度是相同的。這意味著訪問索引葉子塊的任何一個索引索引值所花費的時間幾乎相同。
2.Oracle能保證所有的B樹索引是自平衡的,不可能出現不同的索引葉子塊處於同一層的現象。
3.通過B樹索引訪問表裡行記錄的效率不會隨著相關表的資料量的遞增而明顯降低。
B樹索引的結構決定了在通過B樹索引訪問資料的過程是先訪問相關的B樹索引,然後根據訪問該索引後得到的rowid,再去訪問表對應的資料行記錄。如果訪問需要的資料通過B樹索引就可以得到,就不需要再訪問表了。訪問B樹索引和表都需要消耗IO。這就意味著在oracle中訪問索引的成本有兩部分組成,一部分是訪問B樹索引的成本(從根節點定位到分支塊,再定位到相關的葉子塊,最後對葉子塊執行掃描操作)
另一部分是方位表的成本(根據B樹索引得到ROWID再去表掃描對應的資料行所對應的資料區塊)。
B樹索引有5種存取方法。
1.索引唯一性掃描
索引唯一性掃描(INDEX UNIQUE SCAN)是針對唯一性索引(UNIQUE INDEX)的掃描,它僅僅適用於where 條件裡是等值查詢的SQL,因為掃描的對象是唯一性索引,所以索引唯一性掃描的結果至多隻會返回一條記錄。
2.索引範圍掃描
索引範圍掃描(INDEX RANGE SCAN)適用於所有類型的B樹索引,當掃描的對象是唯一性索引時,SQL的where條件一定是範圍查詢(between,<>);當掃描的對象是非唯一性索引時,SQL的where條件沒有限制,可以是=,between, <>等。索引範圍掃描的結果可能返回多條記錄。
在同等條件下,當目標索引的索引行的數量大於1時,索引範圍掃描所耗費的邏輯讀至少會比相應的索引唯一性掃描的邏輯讀多1.
我們做個實驗驗證下結論。
SQL>create table test as select * from emp;
SQL>select count(empno) from test;
COUNT(EMPNO)
-----------------
13
表test中列empno的非null值的數量為13,意味著在test表的列empno上建立B樹索引,索引行的數量一定大於1
在表test列empno上建立唯一性B樹索引idx_empno
SQL> create unique index idx_empno on test(empno);
Index created.
收集下test表和idx_empno索引
SQL> begin
2 dbms_stats.gather_table_stats('SCOTT','TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL>set autotrace traceonly
SQL>select * from test where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 3039750644
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 37 | 1 (0)| 0
0:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_EMPNO | 1 | | 0 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
1088 recursive calls
0 db block gets
164 consistent gets
23 physical reads
0 redo size
822 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
從上述執行計畫的內容可以看到,執行計畫走的是索引唯一性掃描,消耗的邏輯讀為164.
第二步驟,我們刪除唯一性索引idx_empno
SQL> DROP INDEX IDX_EMPNO;
建立非唯一性的B樹索引。
SQL> CREATE INDEX IDX_EMPNO ON TEST(EMPNO);
Index dropped.
再次收集統計資訊
SQL> begin
2 dbms_stats.gather_table_stats('SCOTT','TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
3 end;
4 /
PL/SQL procedure successfully completed.
再次清空buffer cache 和shared pool,千萬別在生產環境中執行。
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> select * from test where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 1320605699
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 0
0:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 37 | 2 (0)| 0
0:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMPNO | 1 | | 1 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
813 recursive calls
0 db block gets
165 consistent gets
27 physical reads
0 redo size
822 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
1 rows processed
從執行計畫的內容看,sql的執行計畫從之前的索引唯一性掃描變成索引範圍掃描,邏輯讀的值由164變為了165,比原來多掃描1次。
3.索引全掃描
索引全掃描(INDEX FULL SCAN)適用於所有的B樹索引。索引全掃描要掃描索引所有葉子塊的所有索引。在預設情況下,索引全掃描從左至右依次順序掃描索引所有葉子塊的所有索引,索引是有序,所以索引全掃描執行的結果也是有序的。
SQL> select empno from test
2 ;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 654388723
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | IDX_EMPNO | 14 | 56 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
556 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
從執行計畫中,我們看到統計資訊部分的“sorts(memory)”和“sorts(disk)”的值都為0.說明 SQL執行結果已經按照列empno排好序了。
4.索引快速全掃描
索引快速全掃描(INDEX FAST FULL SCAN )和索引全掃描非常類似,它適用所有B樹索引。
索引快速全掃描也要掃碼所有葉子塊的所有索引行。
他們區別如下::
1)索引快速全掃描只適用於CBO
2)索引快速全掃描可以使用多塊讀,也可以並存執行。
3)索引快速全掃描的執行結果不一定是有序的。這是因為索引快速全掃描時,oracle是根據索引行在磁碟上的實體儲存體順序來掃描的,而不是根據索引行的邏輯順序來掃描的。
5.索引跳躍式掃描
索引跳躍式掃描(INDEX SKIP SCAN)適用所有複合B樹索引。它使那些在where 條件中沒有對目標索引的前置列指定查詢條件但同時又對該索引的非前置列指定了查詢條件的目標SQL依然可以用上該索引。這就像是在掃描索引時跳過了他的前置列,直接從該索引的非前置列開始掃描一樣。
看一個例子:
SQL>create table test1(name varchar2(10),id number not null);
建立一個複合B樹索引
SQL>create index idx_naid on test1(name,id);
插入10000條記錄,5000條name列為test,5000條name列為prot
begin
for i in 1..5000 loop
insert into test1 values('test',i);
end loop;
commit;
end;
/
begin
for i in 1..5000 loop
insert into test1 values('prot',i);
end loop;
commit;
end;
/
SQL>SET AUTOTRACE TRACEONLY
SQL>exec dbms_stats.gather_table_stats('SCOTT','TEST1',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
SQL>select * from test1 where id=200;
Execution Plan
----------------------------------------------------------
Plan hash value: 4123651466
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 16 | 3 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_NAID | 2 | 16 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=200)
filter("ID"=200)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
505 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
例子中的where條件是id=200,只對複合B樹索引的第二列id指定了查詢條件,並沒有對索引的前置列name指定任何查詢條件。這裡沒有指定前置列的情況下還能使用上索引,是因為oracle自動對該索引的前置列的所有distinct值做了遍曆。
從例子中分析的過程看,oracle中的索引跳躍式掃描僅僅適用於那些目標索引前置列的distinct值數量較少,後續非前置列的可選擇性又非常好的情況。因為索引跳躍式掃描的執行效率一定會隨著目標索引前置列的distinct值數量的遞增而遞減。