Oracle中的B樹索引
B樹索引是Oracle的預設的索引型別。因為表中的行識別符號(rowid)和相關的列值儲存在一個平衡的樹狀結構的索引塊中，所以該索引型別被稱為B樹索引。使用Oracle的B樹索引有以下幾個原因.
.提高SQL語句的效能
.強制執行主鍵和唯一鍵約束的唯一性
.減少透過主鍵和外來鍵約束關聯的父表和子表間潛在的鎖定問題
Oracle如何使用B樹索引
為了充分理解B樹索引的內部實現，以便在建立資料庫應用程式時能做出明智的索引決定。將舉例說明，首先建立測試表cust
SQL> create table cust( 2 cust_id number, 3 last_name varchar2(30), 4 first_name varchar2(30)); Table created.
在last_name列上建立B樹索引
SQL> create index cust_idx1 on cust(last_name); Index created.
向表cust中插入資料
SQL> insert into cust(cust_id,last_name,first_name) 2 select rownum rn ,a.last_name,a.first_name from hr.employees a 3 union 4 select rownum+(107*1) rn ,a.last_name,a.first_name from hr.employees a 5 union 6 select rownum+(107*2) rn ,a.last_name,a.first_name from hr.employees a 7 union 8 select rownum+(107*4) rn ,a.last_name,a.first_name from hr.employees a 9 union 10 select rownum+(107*5) rn ,a.last_name,a.first_name from hr.employees a 11 union 12 select rownum+(107*6) rn ,a.last_name,a.first_name from hr.employees a 13 union 14 select rownum+(107*7) rn ,a.last_name,a.first_name from hr.employees a 15 union 16 select rownum+(107*8) rn ,a.last_name,a.first_name from hr.employees a 17 union 18 select rownum+(107*9) rn ,a.last_name,a.first_name from hr.employees a 19 union 20 select rownum+(107*10) rn ,a.last_name,a.first_name from hr.employees a; 1070 rows created. SQL> commit; Commit complete. SQL> select distinct last_name,first_name from cust where rownum<11; LAST_NAME FIRST_NAME ------------------------------ ------------------------------ Austin David Banda Amit Atkinson Mozhe Bissot Laura Ande Sundar Bates Elizabeth Bell Sarah Bernstein David Baer Hermann Baida Shelli 10 rows selected.
插入資料後，確保該表的統計資訊是最新的，以便為查詢最佳化器提供足夠的資訊，從而做出如何檢索資料的更好決定，執行如下命令收集表的統計資訊:
SQL> exec dbms_stats.gather_table_stats(ownname=>'JY',tabname=>'CUST',cascade=>true); PL/SQL procedure successfully completed.
不建議使用analyze語句(帶compute和estimate子句)來收集統計資訊。提供此功能只是為了向後相容。
當向表中插入資料時，Oracle將分配由物理資料庫塊組成的區。Oracle還將為索引分配資料塊。對於每個插入到表中的記錄，Oracle還將建立一個包含rowid和列值的索引條目(本例中是rowid和last_name列的值)。每個索引項的rowid指向儲存該表的列值的資料檔案和資料塊號。
當從一個表及其對應的索引選擇資料時，存在三種情況。
.SQL查詢所需的所有表的資料都在索引結構中。因此，只需要訪問索引塊。不需要從表中讀取資料塊。
.查詢所需的所有資訊沒有都包含在索引塊中。因此，查詢最佳化器選擇既訪問索引塊也要訪問表塊來檢索需要的資料，以滿足查詢條件。
.查詢最佳化器選擇不訪問索引。因此只訪問表塊。
場景1.所有的資料位於索引塊中
這裡將介紹兩種情況。在每種情況下，執行查詢需要的所有資料，包括返回給使用者的資料，以及在where子句中被評估的資料，都位於該索引中。
.索引範圍掃描(index range scan):如果最佳化器確定它使用索引結構檢索查詢所需的多個行時是有效的，那麼就使用這種掃描。索引範圍掃描被廣泛用於各種各樣的情況。
.索引快速全掃描(index fast full scan):如果最佳化器確定表中的大部分行需要進行檢索，那麼就使用這種掃描。但所有需要的資訊都儲存在索引中。由於索引結構通常比表結構小，最佳化器確定全索引掃描(比全表掃描)更高效。這種情況對統計(count)值的查詢是很常見的。
首先演示的是索引範圍掃描。在這種情況下，執行下面的查詢:
select last_name from cust where last_name='Austin';
為了在該查詢中返回資料，Oracle最小需要讀取多少塊，也就是說為了滿足此查詢，訪問物理塊最有效的方式是什麼，最佳化器可以選擇讀取表結構的每個塊。然而，這會導致很大的IO開銷，因此，它不是檢索資料的最最佳化方法。
對於這個例子，檢索資料最有效的方法是使用索引結構。要返回包含last_name列中值為Austin的行，Oracle將需要讀取3個索引塊。透過使用Oracle的autotrace(自動跟蹤)實用程式，可以確認。
SQL> set autotrace on
SQL> select last_name from cust where last_name='Austin';
LAST_NAME
------------------------------
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
Austin
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3472749082
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 80 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| CUST_IDX1 | 10 | 80 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LAST_NAME"='Austin')
此輸出顯示，Oracle只需要使用cust_idx1索引來檢索資料，以滿足查詢的結果集。不需要訪問表中的資料塊，只需要訪問索引塊。這對於給定的查詢，這是特別高效的索引策略。當索引包含查詢所需的所有列值時，它被稱為覆蓋索引。
下面列出為這個例子使用自動跟蹤所顯示的統計資訊:
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 653 bytes sent via SQL*Net to client 624 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
一致獲取(consistent gets)的值表示有三個讀記憶體操作。資料庫塊獲取(db block gets)加一致獲取等於總的記憶體讀取操作。由於索引塊已經在記憶體中，因此返回此查詢的結果集不需要物理讀取。此外，有10行進行了處理，這與cust表中last_name為Austin的記錄數相符。
下面顯示導致執行索引快速全掃描的一個例子。
select count(last_name) from cust;
使用set autotrace on生成執行計劃。下面是相應的輸出:
SQL> select count(last_name) from cust; COUNT(LAST_NAME) ---------------- 1070 Execution Plan ---------------------------------------------------------- Plan hash value: 2246355899 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 8 | | | | 2 | INDEX FAST FULL SCAN| CUST_IDX1 | 1070 | 8560 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------------
此輸出顯示，確定表內的計數只用到了索引結構。在這種情況下，最佳化器確定採取索引快速全掃描比全表掃描更高效。
Statistics ---------------------------------------------------------- 48 recursive calls 0 db block gets 91 consistent gets 0 physical reads 0 redo size 559 bytes sent via SQL*Net to client 624 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 1 rows processed
場景2.索引中不包含所有資訊
考慮這樣一種情況:假設需要從cust表獲得更多資訊。首先，回顧一下前面的查詢語句，並且還要在查詢結果中返回first_name列。現在，要獲得新增的資料元素，就需要訪問表本身。下面是新的查詢語句:
select last_name,first_name from cust where last_name='Austin';
使用set autotrace on,並執行前面的查詢語句:
SQL> alter system flush buffer_cache;
System altered.
SQL> select last_name,first_name from cust where last_name='Austin';
LAST_NAME FIRST_NAME
------------------------------ ------------------------------
Austin David
Austin David
Austin David
Austin David
Austin David
Austin David
Austin David
Austin David
Austin David
Austin David
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2100940648
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 150 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CUST | 10 | 150 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | CUST_IDX1 | 10 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LAST_NAME"='Austin')
此輸出資訊指示，cust_idx1索引是透過一次索引範圍掃描(index range scan)訪問的。索引範圍掃描標識出滿足此查詢結果所需的索引塊。此外，表是過table access by index rowid batched來讀取的。透過索引的rowid訪問表，表示Oracle利用儲存在索引中的rowid找到表塊包含的相應行。把rowid對映到相應的表塊，這些塊中含有last_name值為Austin的資料。由於我們清空了buffer cache了，這樣查詢共執行了6次物理讀取，9次記憶體讀取。
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 9 consistent gets 6 physical reads 0 redo size 896 bytes sent via SQL*Net to client 624 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed
場景3.只有表塊被訪問
在某些情況下，即使有索引存在，Oracle也會確定只使用表塊比透過索引訪問更為有效。當Oracle檢查表內的每一行時，這被稱為全表掃描。
例如，執行此查詢:
SQL> select * from cust;
下面是相應的執行計劃和統計資訊:
Execution Plan ---------------------------------------------------------- Plan hash value: 260468903 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1070 | 19260 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| CUST | 1070 | 19260 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 204 recursive calls 9 db block gets 389 consistent gets 20 physical reads 1080 redo size 38869 bytes sent via SQL*Net to client 1405 bytes received via SQL*Net from client 73 SQL*Net roundtrips to/from client 33 sorts (memory) 0 sorts (disk) 1070 rows processed
此輸出顯示，需要一致讀取(consistent gets)389個塊以及資料庫塊獲取(db block gets)9個塊和物理讀取20個塊。Oracle檢索表中的每一行以返回滿足查詢所需的結果。在這種情況下，必須讀取表中已使用的所有塊，Oracle無法使用索引來加快資料檢索。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2732688/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle如何建立B樹索引Oracle索引
- 【Mysql】InnoDB 中的 B+ 樹索引MySql索引
- Oracle如何實現B樹索引Oracle索引
- Oracle如何管理帶約束的B樹索引Oracle索引
- B樹在資料庫索引中的應用剖析資料庫索引
- B樹索引的內部結構索引
- 淺談MySQL的B樹索引與索引優化MySql索引優化
- Mysql InnoDB B+樹索引和雜湊索引的區別？ MongoDB 為什麼使用B-樹?MySql索引MongoDB
- 搞懂MySQL InnoDB B+樹索引MySql索引
- 平衡樹索引（b-tree index）索引Index
- Elasticsearch 中為什麼選擇倒排索引而不選擇 B 樹索引Elasticsearch索引
- B樹索引和點陣圖索引的結構介紹索引
- MySQL索引為什麼使用B+樹?MySql索引
- 點陣圖索引（Bitmap Index）——從B*樹索引到點陣圖索引索引Index
- 雜湊，二叉樹，紅黑樹，B樹，B+樹，LSM樹等資料結構做索引比較二叉樹資料結構索引
- MySQL索引-B+樹（看完你就明白了）MySql索引
- oracle的B-tree索引結構分析Oracle索引
- 一分鐘掌握MySQL的InnoDB引擎B+樹索引MySql索引
- 跳槽必看MySQL索引：B+樹原理揭秘與索引優缺點分析MySql索引
- MySQL資料庫索引選擇使用B+樹MySql資料庫索引
- 面試題：MySQL索引為什麼用B+樹？面試題MySql索引
- 面試 (MySQL 索引為啥要選擇 B+ 樹)面試MySql索引
- mysql索引為啥要選擇B+樹 (下)MySql索引
- mysql索引為啥要選擇B+樹 (上)MySql索引
- 資料庫索引為什麼使用B+樹？資料庫索引
- Oracle中B-Tree、Bitmap和函式索引使用案例總結Oracle函式索引
- 二叉樹、B樹以及B+樹二叉樹
- 平衡二叉樹，B樹，B+樹二叉樹
- B 樹和 B+樹的區別， 為什麼 MySQL 要使用 B+樹MySql
- 『分享』兩篇講 B-樹 B+ 樹的文章
- 資料庫索引為什麼用B+樹實現？資料庫索引
- Mysql索引資料結構為什麼是B+樹？MySql索引資料結構
- 徹底搞懂MySQL為什麼要使用B+樹索引MySql索引
- CMU資料庫(15-445)-實驗2-B+樹索引實現(中）刪除資料庫索引
- oracle樹中prior的用法Oracle
- B樹與B+樹區別辨析
- 教你如何成為Oracle 10g OCP - 第九章 物件管理(6) - B樹索引的訪問Oracle 10g物件索引
- 主鍵為聯合主鍵時，索引B+樹結構索引