本文主要討論以下幾種索引訪問(wèn)方法：
1.索引唯一掃描(INDEX UNIQUE SCAN)
2.索引范圍掃描(INDEX RANGE SCAN)
3.索引全掃描(INDEX FULL SCAN)
4.索引跳躍掃描(INDEX SKIP SCAN)
5.索引快速全掃描(INDEX FAST FULL SCAN)
索引唯一掃描(INDEX UNIQUE SCAN)
通過(guò)這種索引訪問(wèn)數(shù)據(jù)的特點(diǎn)是對(duì)于某個(gè)特定的值只返回一行數(shù)據(jù)，通常如果在查詢謂語(yǔ)中使用UNIQE和PRIMARY KEY索引的列作為條件的時(shí)候會(huì)選用這種掃描；訪問(wèn)的高度總是索引的高度加一，除了某些特殊的情況，如另外存儲(chǔ)的LOB對(duì)象。
SQL> set autotrace traceonly explain
SQL> select * from hr.employees where employee_id = 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1833546154
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"=100)
索引范圍掃描(INDEX RANGE SCAN)
謂語(yǔ)中包含將會(huì)返回一定范圍數(shù)據(jù)的條件時(shí)就會(huì)選用索引范圍掃描，索引可以是唯一的亦可以是不唯一的；所指定的條件可以是(<,>,LIKE,BETWEEN,=)等運(yùn)算符，不過(guò)使用LIKE的時(shí)候，如果使用了通配符%，極有可能就不會(huì)使用范圍掃描，因?yàn)闂l件過(guò)于的寬泛了，下面是一個(gè)示例：
SQL> select * from hr.employees where DEPARTMENT_ID = 30;
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2056577954
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 414 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 414 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 6 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=30)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
7 consistent gets
1 physical reads
0 redo size
1716 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
范圍掃描的條件需要準(zhǔn)確的分析返回?cái)?shù)據(jù)的數(shù)目，范圍越大就越可能執(zhí)行全表掃描；
SQL> select department_id,count(*) from hr.employees group by department_id order by count(*);
DEPARTMENT_ID COUNT(*)
------------- ----------
10 1
40 1
1
70 1
20 2
110 2
90 3
60 5
30 6
100 6
80 34
50 45
12 rows selected.
-- 這里使用數(shù)值最多的50來(lái)執(zhí)行范圍掃描
SQL> set autotrace traceonly explain
SQL> select * from hr.employees where DEPARTMENT_ID = 50;
45 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 3105 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"=50)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
4733 bytes sent via SQL*Net to client
545 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
45 rows processed
可以看到在獲取范圍數(shù)據(jù)較大的時(shí)候，優(yōu)化器還是執(zhí)行了全表掃描方法。
一種對(duì)于索引范圍掃描的優(yōu)化方法是使用升序排列的索引來(lái)獲得降序排列的數(shù)據(jù)行，這種情況多發(fā)生在查詢中包含有索引列上的ORDER BY子句的時(shí)候，這樣就可避免一次排序操作了，如下：
SQL> set autotrace traceonly explain
SQL> select * from hr.employees
2 where department_id in (90, 100)
3 order by department_id desc;
Execution Plan
----------------------------------------------------------
Plan hash value: 3707994525
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 621 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 9 | 621 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN DESCENDING| EMP_DEPARTMENT_IX | 9 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=90 OR "DEPARTMENT_ID"=100)
上例中，索引條目被相反的順序讀取，避免了排序操作。
索引全掃描(INDEX FULL SCAN)
索引全掃描的操作將會(huì)掃描索引結(jié)構(gòu)的每一個(gè)葉子塊，讀取每個(gè)條目的的行編號(hào)，并取出數(shù)據(jù)行，既然是訪問(wèn)每一個(gè)索引葉子塊，那么它相對(duì)的全表掃描的優(yōu)勢(shì)在哪里呢？實(shí)際上在索引塊中因?yàn)榘男畔⒘袛?shù)較少，通常都是索引鍵和ROWID，所以對(duì)于同一個(gè)數(shù)據(jù)塊和索引塊，包含的索引鍵的條目數(shù)通常都是索引塊中居多，因此如果查詢字段列表中所有字段都是索引的一部分的時(shí)候，就可以完全跳過(guò)對(duì)表數(shù)據(jù)的訪問(wèn)了，這種情況索引全掃描的方法會(huì)獲得更高的效率。
發(fā)生索引全掃描的情況有很多，幾種典型的場(chǎng)景：
1，查詢總?cè)鄙僦^語(yǔ)，但獲取的列可以通過(guò)索引直接獲得
SQL> select email from hr.employees;
Execution Plan
----------------------------------------------------------
Plan hash value: 2196514524
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 856 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN | EMP_EMAIL_UK | 107 | 856 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------
2，查詢謂語(yǔ)中包含一個(gè)位于索引中非引導(dǎo)列上的條件(其實(shí)也取決于引導(dǎo)列值的基數(shù)大小，如果引導(dǎo)列的唯一值較少，也可能出現(xiàn)跳躍掃描的情況)
SQL> select first_name, last_name from hr.employees
2 where first_name like 'A%' ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2228653197
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 45 | 1 (0)| 00:00:01 |
|* 1 | INDEX FULL SCAN | EMP_NAME_IX | 3 | 45 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("FIRST_NAME" LIKE 'A%')
filter("FIRST_NAME" LIKE 'A%')
SQL> SET LONG 2000000
SQL> select dbms_metadata.get_ddl('INDEX','EMP_NAME_IX','HR') from dual;
DBMS_METADATA.GET_DDL('INDEX','EMP_NAME_IX','HR')
--------------------------------------------------------------------------------
CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME"
)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EXAMPLE"
-- 可以看到EMP_NAME_IX索引是建立在列(("LAST_NAME", "FIRST_NAME")上的，使用了帶非引導(dǎo)列FIRST_NAME的謂語(yǔ)
3，數(shù)據(jù)通過(guò)一個(gè)已經(jīng)排序的索引獲得從而省去單獨(dú)的排序操作
SQL> select * from hr.employees order by employee_id ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2186312383
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 7383 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 107 | 7383 | 3 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN | EMP_EMP_ID_PK | 107 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
-- 同樣可以使用升序索引返回降序數(shù)據(jù)
SQL> select employee_id from hr.employees order by employee_id desc ;
Execution Plan
----------------------------------------------------------
Plan hash value: 753568220
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 428 | 1 (0)| 00:00:01 |
| 1 | INDEX FULL SCAN DESCENDING| EMP_EMP_ID_PK | 107 | 428 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
在上面的例子中可以看出，索引全掃描也可以想范圍掃描一樣，通過(guò)升序索引返回降序數(shù)據(jù)，而它的優(yōu)化不止這一種，當(dāng)我們查詢某一列的最大值或最小值而這一列又是索引列的時(shí)候，索引全掃描就會(huì)獲得非常顯著的優(yōu)勢(shì)，因?yàn)檫@時(shí)的優(yōu)化器并沒(méi)有對(duì)索引的數(shù)據(jù)進(jìn)行全部葉子節(jié)點(diǎn)的檢索，而只是對(duì)一個(gè)根塊，第一個(gè)或最后一個(gè)葉子塊的掃描，這無(wú)疑會(huì)顯著的提高性能！！
-- 索引全掃描獲得最小值
SQL> select min(department_id) from hr.employees ;
Execution Plan
----------------------------------------------------------
Plan hash value: 613773769
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
-- 如果同時(shí)包含MAX和MIN的求值，優(yōu)化器并不會(huì)主動(dòng)選擇效率較高的索引全掃描方法
SQL> select min(department_id), max(department_id) from hr.employees ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
-- 一種替代的優(yōu)化方案
SQL> select
2 (select min(department_id) from hr.employees) min_id,
3 (select max(department_id) from hr.employees) max_id
4 from dual;
Execution Plan
----------------------------------------------------------
Plan hash value: 2189307159
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 3 | | |
| 4 | INDEX FULL SCAN (MIN/MAX)| EMP_DEPARTMENT_IX | 1 | 3 | 1 (0)| 00:00:01 |
| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
索引跳躍掃描(INDEX SKIP SCAN)
這種掃描方式也是一種特例，因?yàn)樵谠缙诘陌姹局校瑑?yōu)化器會(huì)因?yàn)槭褂昧朔且龑?dǎo)列而拒絕使用索引。跳躍掃描的前提有著對(duì)應(yīng)的情景，當(dāng)謂語(yǔ)中包含索引中非引導(dǎo)列上的條件，并且引導(dǎo)列的唯一值較小的時(shí)候，就有極有可能使用索引跳躍掃描方法；同索引全掃描，范圍掃描一樣，它也可以升序或降序的訪問(wèn)索引；不同的是跳躍掃描會(huì)根據(jù)引導(dǎo)列的唯一值數(shù)目將復(fù)合索引分成多個(gè)較小的邏輯子索引，引導(dǎo)列的唯一值數(shù)目越小，分割的子索引數(shù)目也就越少，就越可能達(dá)到相對(duì)全表掃描較高的運(yùn)算效率。
-- 創(chuàng)建測(cè)試表，以dba_objects表為例
SQL> create table test as select * from dba_objects;
Table created.
-- 創(chuàng)建一個(gè)復(fù)合索引，這里選取了一個(gè)唯一值較少的owner列作為引導(dǎo)列
SQL> create index i_test on test(owner,object_id,object_type) ;
Index created.
-- 分析表收集統(tǒng)計(jì)信息
SQL> exec dbms_stats.gather_table_stats('SYS','TEST');
PL/SQL procedure successfully completed.
-- 先看一下引導(dǎo)列的唯一值的比較
SQL> select count(*),count(distinct owner) from test;
COUNT(*) COUNT(DISTINCTOWNER)
---------- --------------------
72482 29
-- 使用非引導(dǎo)列的條件查詢來(lái)訪問(wèn)觸發(fā)SKIP SCAN
SQL> select * from test where object_id = 46;
Execution Plan
----------------------------------------------------------
Plan hash value: 1001786056
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 31 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 97 | 31 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | I_TEST | 1 | | 30 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=46)
filter("OBJECT_ID"=46)
Statistics
----------------------------------------------------------
101 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
1610 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
-- 來(lái)看看這條語(yǔ)句全掃描的效率
SQL> select /*+ full(test) */ * from test where object_id = 46;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 97 | 282 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=46)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1037 consistent gets
0 physical reads
0 redo size
1607 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
分析上面的查詢可以看出，我們使用的索引中引導(dǎo)列有29個(gè)唯一值，也就是說(shuō)在執(zhí)行索引跳躍掃描的時(shí)候，分割成了29個(gè)邏輯子索引來(lái)查詢，只產(chǎn)生了38次邏輯讀；而相對(duì)全表掃描的1037次邏輯讀，性能提升非常明顯！
索引快速全掃描(INDEX FAST FULL SCAN)
這種訪問(wèn)方法在獲取數(shù)據(jù)上和全表掃描相同，都是通過(guò)無(wú)序的多塊讀取來(lái)進(jìn)行的，因此也就無(wú)法使用它來(lái)避免排序代價(jià)了；索引快速全掃描通常發(fā)生在查詢列都在索引中并且索引中一列有非空約束時(shí)，當(dāng)然這個(gè)條件也容易發(fā)生索引全掃描，它的存在多可用來(lái)代替全表掃描，比較數(shù)據(jù)獲取不需要訪問(wèn)表上的數(shù)據(jù)塊。
-- 依舊使用上面創(chuàng)建的test表
SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
-- 在object_id列上創(chuàng)建索引
SQL> create index pri_inx on test (object_id);
Index created.
-- 直接執(zhí)行全表掃描
SQL> select object_id from test;
72482 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72482 | 353K| 282 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| TEST | 72482 | 353K| 282 (1)| 00:00:04 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5799 consistent gets
0 physical reads
0 redo size
1323739 bytes sent via SQL*Net to client
53675 bytes received via SQL*Net from client
4834 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
72482 rows processed
-- 修改object_id為not null
SQL> alter table test modify (object_id not null);
Table altered.
-- 再次使用object_id列查詢就可以看到使用了快速全掃描了
SQL> select object_id from test;
72482 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3806735285
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72482 | 353K| 45 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PRI_INX | 72482 | 353K| 45 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
167 recursive calls
0 db block gets
5020 consistent gets
161 physical reads
0 redo size
1323739 bytes sent via SQL*Net to client
53675 bytes received via SQL*Net from client
4834 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
72482 rows processed
PS，這個(gè)INDEX FAST FULL SCAN的例子真是不好模擬，上面的例子弄了好久。。。。。
