訪問索引的方法
首先要說明一點(diǎn)，以下提到是oracle數(shù)據(jù)庫最常用的B樹索引，oracle數(shù)據(jù)其他類型的索引暫不考慮，B樹索引就好像一棵倒長的樹，它包含兩種類型的數(shù)據(jù)塊，一種是索引分支塊，另一種是索引葉子塊。
索引分支塊包含指向響應(yīng)索引分支塊/葉子塊的指針和索引鍵值列（這里的指針是指相關(guān)分支塊/葉子塊的塊地址RDBA，每個(gè)索引分支塊都有兩種類型的指針，一種是lmc,另一種是索引分支塊的索引行記錄的指針。lmc是left Most Child的縮寫，每個(gè)索引分支塊都只有一個(gè)lmc,這個(gè)Imc指向的分支塊/葉子塊中所有索引鍵值列中最大值一定下于該lmc所在索引分支塊的索引索引鍵值列中的最小值；而索引分支塊的索引行記錄所記錄的指針指向的分支塊/葉子塊的所有索引鍵值列的最小值一定大于或等于該行記錄的索引鍵值列的值）在oracle里訪問B數(shù)索引的操作都必須從根節(jié)點(diǎn)開始，即都會(huì)經(jīng)歷一個(gè)從根節(jié)點(diǎn)到分支塊再到葉子塊的過程。
索引葉子塊包含被索引鍵值和用于定位該索引鍵值所在的數(shù)據(jù)行在表中的實(shí)際物理存儲(chǔ)位置的ROWID。對(duì)于唯一性B樹索引而言，rowid是存儲(chǔ)在索引行的行頭，所以此時(shí)并不需要額外存儲(chǔ)該rowid的長度。對(duì)于非唯一性B數(shù)索引而言，rowid被當(dāng)做額外的列與被索引的鍵值列一起存儲(chǔ)，所以此時(shí)oracle既要存儲(chǔ)rowid，同時(shí)又要存儲(chǔ)其長度，這意味著在同等條件下，唯一性B樹索引要比非唯一性B樹索引節(jié)省索引葉子塊的存儲(chǔ)空間。對(duì)于非唯一索引而言，B樹索引的有序性體現(xiàn)在oracle會(huì)按照被索引鍵值和相應(yīng)的rowid來聯(lián)合排序。oracle里的索引葉子塊時(shí)左右互聯(lián)的，即相當(dāng)于有一個(gè)雙向指針鏈表把這些索引葉子塊互相連接了一起。
正是由于上述特點(diǎn)，oracle數(shù)據(jù)庫中的B樹索引才具有如下優(yōu)勢(shì)：
（1）所有的索引葉子塊都在同一層，即他們距離索引根節(jié)點(diǎn)的深度是相同的，這也意味著訪問索引葉子塊的任何一個(gè)索引鍵值所花費(fèi)的時(shí)間幾乎相同。
（2）oracle會(huì)保證所有B樹索引都是自平衡的，即不可能出現(xiàn)不同索引葉子塊不處于同一層的現(xiàn)象。
（3）通過B樹索引訪問表的行記錄的效率并不會(huì)隨著相關(guān)表的數(shù)據(jù)量遞增而顯著降低，即通過走索引訪問數(shù)據(jù)的時(shí)間是可控的，基本穩(wěn)定的，這也是走索引和全表掃描的最大區(qū)別。全表掃描最大的劣勢(shì)就在于其訪問時(shí)間不可控，不穩(wěn)定，即全表掃描花費(fèi)的時(shí)間會(huì)隨著目標(biāo)表數(shù)據(jù)量的遞增而遞增。
B樹索引的上述結(jié)構(gòu)就決定了在oracle里通過B樹索引訪問數(shù)據(jù)的過程是先訪問相關(guān)的B樹索引，然后根據(jù)訪問該索引后得到的rowid再回表去訪問對(duì)應(yīng)的數(shù)據(jù)行記錄（當(dāng)然，如果目標(biāo)sql所訪問的數(shù)據(jù)通過訪問相關(guān)的B樹索引可以得到，那么就不再需要回表了）。訪問相關(guān)的B樹索引和回表需要消耗I/O，這意味著在oracle中訪問索引的成本由兩部分組成：一部分是訪問相關(guān)的B樹索引的成本（從根節(jié)點(diǎn)到相關(guān)的分支塊，再定位相關(guān)的葉子塊，最后對(duì)這些葉子塊執(zhí)行掃描操作）；另一部分是回表成本（根據(jù)得到的rowid再回表去掃描對(duì)應(yīng)的數(shù)據(jù)行所在的數(shù)據(jù)塊）
下面介紹oracle中一些常見的訪問B樹索引的方法
（1）索引唯一掃描
索引唯一性掃描（INDEX UNIQUE SCAN）是針對(duì)唯一性索引（UNIQUE INDEX)的掃描，它僅僅適用于where條件里是等值查詢的目標(biāo)sql。因?yàn)閽呙璧膶?duì)象是唯一性索引，所以索引唯一性掃描的結(jié)果至多只會(huì)返回一條記錄
(2)索引范圍掃描
索引范圍掃描(INDEX RANGE SCAN)適用于所有類型的B樹索引，當(dāng)掃描的對(duì)象是唯一性索引時(shí)，此時(shí)目標(biāo)sql的where條件一定是范圍查詢（謂詞條件為between，<，>)；當(dāng)掃描的對(duì)象是非唯一索引時(shí)，對(duì)目標(biāo)sql的where條件沒有限制（可以是等值查詢，也可以時(shí)范圍查詢）。索引范圍掃描的結(jié)果可能會(huì)返回多條記錄，其實(shí)這就是索引范圍掃描中范圍"范圍“二字的本質(zhì)含義。
需要注意的是，即使是針對(duì)同條件下相同的sql，當(dāng)目標(biāo)索引的索引行的數(shù)量大于1時(shí)，索引范圍掃描耗費(fèi)的邏輯讀會(huì)多于索引唯一掃描所耗費(fèi)的邏輯讀。這是因?yàn)樗形ㄒ粧呙杞Y(jié)果至多只返回一條記錄，所以oracle明確知道此時(shí)只需要訪問相關(guān)的葉子塊一次就可以直接返回了；但對(duì)于索引范圍掃描而言，因?yàn)槠鋻呙杞Y(jié)果可能會(huì)返回多條記錄，同時(shí)又因?yàn)樗饕乃饕袛?shù)量大于1，oracle為了確定索引范圍掃描的終點(diǎn)，就不得不去多訪問一次相關(guān)的葉子塊，所以在同等條件下，當(dāng)目標(biāo)索引的索引行的數(shù)量大于1時(shí)，索引范圍掃描所耗費(fèi)的邏輯讀至少會(huì)比相應(yīng)的索引唯一性的邏輯讀多1.
實(shí)驗(yàn)驗(yàn)證：
SQL> create table emp_temp as select * from emp;
Table created.
現(xiàn)在emp_temp中列empno的非null值的數(shù)量為13（這意味著如果在表emp_temp的列empno上建單鍵值的B樹索引，則該索引的索引行的數(shù)量一定大于1）
SQL> create unique index idx_emp_temp on emp_temp(empno);
Index created.
然后對(duì)表emp_temp和索引idx_emp_temp收集一下統(tǒng)計(jì)信息：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>10 0,cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
為了避免buffer cache 和數(shù)據(jù)字典緩存（DATA Dictionary Cache)對(duì)邏輯讀統(tǒng)計(jì)結(jié)果的影響。我們清空buffer cache和數(shù)據(jù)字典緩存
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
執(zhí)行計(jì)劃：
SQL> select * from emp_temp where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 3451700904
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |38 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP | 1 |38 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_EMP_TEMP | 1 | | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
67 consistent gets
13 physical reads
0 redo size
889 bytes sent via SQL*Net to client
512 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
從以上顯示可以看出，該sql的執(zhí)行計(jì)劃走的是索引唯一性掃描，其耗費(fèi)的邏輯讀是73.
現(xiàn)在我們drop掉唯一索引
SQL> drop index idx_emp_temp;
Index dropped.
然后在表emp_temp的列empno上創(chuàng)建一個(gè)單鍵值非唯一性同名B樹索引idx_emp_temp：
SQL> create index idx_emp_temp on emp_temp(empno);
Index created.
收集統(tǒng)計(jì)信息：
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEMP',estimate_percent=>10 0,cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> set autot traceonly
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> select * from emp_temp where empno=7369;
Execution Plan
----------------------------------------------------------
Plan hash value: 351331621
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |38 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_TEMP | 1 |38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_EMP_TEMP | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7369)
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
68 consistent gets
16 physical reads
0 redo size
1025 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
上述結(jié)果看出，此sql的執(zhí)行計(jì)劃已經(jīng)從之前的索引唯一掃描變成現(xiàn)在的索引范圍掃描，其耗費(fèi)的邏輯讀也從之前的73遞增到74，這說明在同等的條件下，當(dāng)目標(biāo)索引的索引行的數(shù)量大于1時(shí)，索引范圍掃描所耗費(fèi)的邏輯讀確實(shí)知識(shí)會(huì)比相應(yīng)的的索引唯一掃描多1.
(3)索引全掃描
索引全掃描（INDEX FULL SCAN）適用于所有類型的B樹索引（包括唯一性索引和非唯一性索引）。所謂“索引全掃描”，就是指要掃描目標(biāo)索引所有葉子塊的所有索引行。這里需要注意的是，索引全掃描需要掃描目標(biāo)索引的索引葉子塊，但這并不意味著需要掃描該索引的所有分支塊。在默認(rèn)情況下，oracle在做索引全掃描時(shí)只需要通過訪問必要的分支塊定位到位于最左邊的葉子塊的第一行索引行，就可以利用該索引葉子塊之間的雙向指針鏈表，從左到右依次順序掃描該索引所有葉子塊的所有索引行了。
既然默認(rèn)情況下，索引全掃描要從左到右依次順序掃描目標(biāo)索引所有葉子塊的所有索引行，而索引是有序的，所以索引全掃描的執(zhí)行結(jié)果也是有序的，并且是按照該索引的索引鍵值來排序，這也意味著走索引全掃描能夠既達(dá)到排序的效果，又同時(shí)避免對(duì)該索引的索引鍵值列的真正排序操作。
SQL> conn scott/scott;
Connected.
SQL> set autot on
SQL> select empno from emp;
EMPNO
----------
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
EMPNO
----------
7900
7902
7934
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 56 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
31 recursive calls
0 db block gets
62 consistent gets
2 physical reads
0 redo size
686 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
對(duì)于上述sql，表EMP的列empno上存在一個(gè)單鍵值B樹主鍵索引PK_EMP，所以列empno的屬性一定是NOT NULL，而該SQL的從查詢又只有empno，所以oracle此時(shí)可以走對(duì)主鍵索引PK_EMP的索引全掃描。
從上述顯示的內(nèi)容我們可以看出，該sql的執(zhí)行計(jì)劃確實(shí)走的是對(duì)主鍵索引pk_emp的索引全掃描，而且執(zhí)行結(jié)果已經(jīng)按照empno排好序了。注意到上述顯示內(nèi)容中“統(tǒng)計(jì)信息”部分“sort（memory）”和“sort disk”的值均為0，這說明雖然上述sql的執(zhí)行結(jié)果已經(jīng)按照empno列排好序，但是實(shí)際上oracle在這里并沒有執(zhí)行任何排序操作，所以走索引全掃描確實(shí)能夠既達(dá)到排序的結(jié)果又可以避免對(duì)目標(biāo)索引的索引鍵值列真正的排序。
默認(rèn)情況下，索引全掃描的掃描結(jié)果是有序性就決定了索引全掃描是不能夠并行執(zhí)行的，并且通常情況下索引全掃描使用的是單塊讀。
通常情況下，索引全掃描不需要回表的，所以索引全掃描適用于目標(biāo)sql的查詢列全部是目標(biāo)索引的索引鍵值列的情形。我們知道，對(duì)于oracle數(shù)據(jù)庫中B樹索引而言，當(dāng)所有索引鍵值列全為NULL值時(shí)不入索引（即當(dāng)所有索引鍵值列全為NULL時(shí)，這些NULL值不會(huì)在B樹索引中存在），這意味在oracle中做索引全掃描的前提條件時(shí)目標(biāo)索引只是有一個(gè)索引鍵值列的屬性值是NOT NULL。這是很顯然的事情，如果目標(biāo)索引的所有索引鍵值列屬性均為允許null值，此時(shí)如果還走索引全掃描，就會(huì)漏掉目標(biāo)表中那些索引鍵值列均為null的記錄，即此時(shí)走索引全掃描的結(jié)果就不準(zhǔn)了，oracle顯然不會(huì)允許這種事情發(fā)生。
（4）索引快速全掃描
索引快速全掃描（INDEX FAST FULL SCAN）和索引全掃描極其類似，它適用于所有類型的B樹索引（包括唯一性索引和非唯一性索引）。和索引全掃描一樣，索引快速全掃描也需要掃描目標(biāo)索引所有葉子塊的所有索引行。
索引快速全掃描和索引全掃描相比有以下三種區(qū)別：
a.索引快速全掃描只適用于CBO
b.索引快速全掃描可以使用多塊讀，也可以并行執(zhí)行。
c.索引快速全掃描的執(zhí)行結(jié)果不一定是有序的，這是因?yàn)樗饕焖偃珤呙钑r(shí)，oralce是根據(jù)索引行在磁盤上的物理存儲(chǔ)順利來掃描的，而不是根據(jù)索引行的邏輯順序來掃描的，所以掃描結(jié)果才不一定有序（對(duì)于單個(gè)索引葉子塊的索引而言，其物理存儲(chǔ)順序和邏輯存儲(chǔ)順序一致，但對(duì)于物理存儲(chǔ)位置相鄰的索引葉子塊而言，塊與塊之間索引行的物理存儲(chǔ)順序不一定在邏輯上有序的）。
實(shí)驗(yàn)驗(yàn)證：
SQL> create table emp_test(empno number,col1 char(2000),col2 char(2000),col3 char(2000));
Table created.
SQL> alter table emp_test add constraint pk_emp_test primary key(empno,col1,col2,col3);
Table altered.
SQL> insert into emp_test select empno,ename,job,'A' from emp;
14 rows created.
SQL> insert into emp_test select empno,ename,job,'B' from emp;
14 rows created.
SQL> insert into emp_test select empno,ename,job,'C' from emp;
14 rows created.
SQL> insert into emp_test select empno,ename,job,'D' from emp;
14 rows created.
SQL> insert into emp_test select empno,ename,job,'E' from emp;
14 rows created.
SQL> insert into emp_test select empno,ename,job,'F' from emp;
14 rows created.
SQL> insert into emp_test select empno,ename,job,'G' from emp;
14 rows created.
SQL> select count(*) from emp_test;
COUNT(*)
----------
98
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP_TEST',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
這里 /*+ index_ffs(pk_emp_test) */的目的是讓oracle走對(duì)主鍵索引pk_emp_test的索引快速全掃描。
SQL> select /*+ index_ffs(pk_emp_test) */ empno from emp_test;
EMPNO
----------
7521
7566
7369
7499
7782
7788
7844
7876
7900
7839
7654
98 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3550420785
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98 | 392 | 28 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_EMP_TEST | 98 | 392 | 28 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
250 consistent gets
0 physical reads
0 redo size
2224 bytes sent via SQL*Net to client
590 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
98 rows processed
上述實(shí)驗(yàn)說明：索引快速全掃描的執(zhí)行結(jié)果不一定是有序的。
（5）索引跳躍式掃描
索引跳躍式掃描（index skip scan）使用于所有類型的復(fù)合B樹索引（包括唯一性索引和非唯一性索引），它使哪些在where條件中沒有對(duì)目標(biāo)索引的前導(dǎo)列指定查詢條件但同時(shí)又對(duì)該索引的非前導(dǎo)列指定查詢條件的目標(biāo)sql依然可以用上該索引，這就像是在掃描該索引跳過了它的前導(dǎo)列，直接從該索引的非前導(dǎo)列開始掃描一樣，這也是索引跳躍式掃描中“跳躍”一詞的含義。
為什么在where條件中沒有對(duì)目標(biāo)索引的前導(dǎo)列指定查詢條件但oracle依然可以使用上該索引，這是因?yàn)閛ralce幫你對(duì)該索引的前導(dǎo)列的所有distinct值做了遍歷。
實(shí)驗(yàn)驗(yàn)證：
SQL> create table employee(gender varchar2(1),employee_id number);
Table created.
SQL> alter table employee modify(employee_id not null);
Table altered.
SQL> create index idx_employee on employee(gender,employee_id);
Index created.
SQL> begin
2 for i in 1..5000 loop
3 insert into employee values('F',i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> begin
2 for i in 5001..10000 loop
3 insert into employee values('F',i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> set autot off
SQL> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'employee',estimate_percent=>100,cascade=>true,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> set autot on
SQL> select * from employee where employee_id=100;
G EMPLOYEE_ID
- -----------
F 100
Execution Plan
----------------------------------------------------------
Plan hash value: 461756150
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | IDX_EMPLOYEE | 1 | 6 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEE_ID"=100)
filter("EMPLOYEE_ID"=100)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
599 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
從上述顯示內(nèi)容可以看出，oracle在執(zhí)行sql時(shí)已經(jīng)用了索引idx_employee，并且其執(zhí)行計(jì)劃走的是就是對(duì)該索引的索引跳躍式掃描。
這里在沒有指定前導(dǎo)列的情況下還能用上述索引，就是因?yàn)閛racle幫我們對(duì)該索引的前導(dǎo)列的索引distinct值做了遍歷。
所謂的對(duì)目標(biāo)索引的索引distinct值做了遍歷，其實(shí)實(shí)際含義相當(dāng)于對(duì)原sql做了等價(jià)改寫（既把要用的目標(biāo)索引的所有前導(dǎo)列的distinct值都加進(jìn)來）。索引inde_employee的前導(dǎo)列g(shù)ender的distinct值只有“F”和“M”兩個(gè)值，所以這里能使用ind_employee的原因可以簡單理解為以下sql：
select * from employee where gender='F' and employee_id=100
union all
select * from employee where gender='M' and employee_id=100;
從上述分析過程可以看出，Oracle中跳躍式索引掃描僅僅適用于那些目標(biāo)索引前導(dǎo)列的distinct值數(shù)量較少，后續(xù)非前導(dǎo)的可選擇性又非常好的情形，因?yàn)樗饕S式掃描的執(zhí)行效率一定會(huì)隨著目標(biāo)索引前導(dǎo)列的distinct值數(shù)量的遞增而遞增。
