oracle 索引訪問的幾種方式
索引的全掃描跟索引的快速全掃描
索引快速全掃描：從索引段頭開始多塊讀HWM以下的所有根塊、枝塊、葉塊，所以透過FFS讀出來的資料讀取順序是由物理儲存位置決定的、可能是無序的。因此在這類操作中，我們可以看到會話會大量的出現db file scattered read等待。
索引全掃描：索引全掃描是根據葉節點鏈來進行的。進行索引全掃描首先要從根開始，找到葉節點鏈上的第一個資料塊，然後沿著葉節點鏈進行掃描，由於葉節點鏈是根據索引鍵值排序的，因此這樣掃描出來的資料本身就是排序的，資料讀出後不需要再次排序。這種掃描方式和索引快速全掃描相比，首先要找到索引的根，然後透過枝節點找到第一個葉節點，然後再順著葉節點鏈掃描整個索引。索引全掃描的IO成本比索引快速全掃描要大很多，讀取根節點和葉節點的成本相對不大，不過由於順著葉節點鏈掃描整個索引的時候無法使用多塊讀，而只能使用單塊讀，因此這種掃描方式的IO開銷要遠大於索引快速全掃描。這種索引掃描，我們如果對會話進行跟蹤，會發現大量的db file sequential read等待
20:44:29 scott@orcl> create table test as select * from emp;
Table created.
Elapsed: 00:00:00.30
20:44:39 scott@orcl> alter table test add constraint pk_test primary key(empno);
Table altered.
Elapsed: 00:00:00.36
20:45:12 scott@orcl> select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.01
20:45:22 scott@orcl> create table t as select * from emp where empno=7900;
Table created.
Elapsed: 00:00:00.03
20:46:54 scott@orcl> begin
20:47:00 2 for i in 1..7200 loop
20:47:12 3 update t set empno=i;
20:47:24 4 insert into test select * from t;
20:47:40 5 commit;
20:47:42 6 end loop;
20:47:47 7 end;
20:47:48 8 /
PL/SQL procedure successfully completed.
scott@orcl> select empno from test;
7214 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 850129961
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7214 | 21642 | 8 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_TEST | 7214 | 21642 | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
509 consistent gets
0 physical reads
0 redo size
125803 bytes sent via SQL*Net to client
5800 bytes received via SQL*Net from client
482 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7214 rows processed
scott@orcl> select empno from emp;
14 rows selected.
Elapsed: 00:00:00.01
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
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
686 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
索引的跳躍式掃描
21:22:06 scott@orcl> select distinct id from test2;
ID
-------------
1
2
21:16:40 scott@orcl> create index idx_test2 on test2(id,empno);
Index created.
21:19:16 scott@orcl> analyze table test2 compute statistics;
Table analyzed.
Elapsed: 00:00:00.10
21:19:24 scott@orcl> select * from test2 where empno=7788;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3100316192
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 33 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TEST2 | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
filter("EMPNO"=7788)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1094 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
從上面可以看到，在沒有指定前導列的情況下還能夠用上該索引，就是因為orcle幫助我們對該索引的前導列的所有的distinct的值做了個遍歷。
所謂的對目標的distinct的值做了遍歷。其含義就是對目標的sql做了等價的改寫（即把要用的目標索引所有前導列的distinct的值都加進來。我們看到
索引的前導列id的值只有1，2兩個。所以我們就能簡單的理解oracle把
select * from test2 where empno=7788;
改寫成了
select * from test2 where id=1 and empno=7788 union all select * from test2 where id=2 and empno=7788;
所以我們看到 oracle的索引的跳躍式掃描僅僅適用於那些目標的前導列的distinct只數量比較少的情況，後續非前導列的值的選擇性有非常好的情況下。
索引範圍掃描
21:35:15 scott@orcl> select * from test where empno<300;
299 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 115135762
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 272 | 8976 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 272 | 8976 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_TEST | 272 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"<300)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
17688 bytes sent via SQL*Net to client
728 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
299 rows processed
適用於謂詞條件有選擇範圍的
索引快速全掃描：從索引段頭開始多塊讀HWM以下的所有根塊、枝塊、葉塊，所以透過FFS讀出來的資料讀取順序是由物理儲存位置決定的、可能是無序的。因此在這類操作中，我們可以看到會話會大量的出現db file scattered read等待。
索引全掃描：索引全掃描是根據葉節點鏈來進行的。進行索引全掃描首先要從根開始，找到葉節點鏈上的第一個資料塊，然後沿著葉節點鏈進行掃描，由於葉節點鏈是根據索引鍵值排序的，因此這樣掃描出來的資料本身就是排序的，資料讀出後不需要再次排序。這種掃描方式和索引快速全掃描相比，首先要找到索引的根，然後透過枝節點找到第一個葉節點，然後再順著葉節點鏈掃描整個索引。索引全掃描的IO成本比索引快速全掃描要大很多，讀取根節點和葉節點的成本相對不大，不過由於順著葉節點鏈掃描整個索引的時候無法使用多塊讀，而只能使用單塊讀，因此這種掃描方式的IO開銷要遠大於索引快速全掃描。這種索引掃描，我們如果對會話進行跟蹤，會發現大量的db file sequential read等待
20:44:29 scott@orcl> create table test as select * from emp;
Table created.
Elapsed: 00:00:00.30
20:44:39 scott@orcl> alter table test add constraint pk_test primary key(empno);
Table altered.
Elapsed: 00:00:00.36
20:45:12 scott@orcl> select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
Elapsed: 00:00:00.01
20:45:22 scott@orcl> create table t as select * from emp where empno=7900;
Table created.
Elapsed: 00:00:00.03
20:46:54 scott@orcl> begin
20:47:00 2 for i in 1..7200 loop
20:47:12 3 update t set empno=i;
20:47:24 4 insert into test select * from t;
20:47:40 5 commit;
20:47:42 6 end loop;
20:47:47 7 end;
20:47:48 8 /
PL/SQL procedure successfully completed.
scott@orcl> select empno from test;
7214 rows selected.
Elapsed: 00:00:00.09
Execution Plan
----------------------------------------------------------
Plan hash value: 850129961
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7214 | 21642 | 8 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| PK_TEST | 7214 | 21642 | 8 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
509 consistent gets
0 physical reads
0 redo size
125803 bytes sent via SQL*Net to client
5800 bytes received via SQL*Net from client
482 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
7214 rows processed
scott@orcl> select empno from emp;
14 rows selected.
Elapsed: 00:00:00.01
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
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
686 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
索引的跳躍式掃描
21:22:06 scott@orcl> select distinct id from test2;
ID
-------------
1
2
21:16:40 scott@orcl> create index idx_test2 on test2(id,empno);
Index created.
21:19:16 scott@orcl> analyze table test2 compute statistics;
Table analyzed.
Elapsed: 00:00:00.10
21:19:24 scott@orcl> select * from test2 where empno=7788;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3100316192
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 33 | 4 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_TEST2 | 1 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)
filter("EMPNO"=7788)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1094 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
從上面可以看到，在沒有指定前導列的情況下還能夠用上該索引，就是因為orcle幫助我們對該索引的前導列的所有的distinct的值做了個遍歷。
所謂的對目標的distinct的值做了遍歷。其含義就是對目標的sql做了等價的改寫（即把要用的目標索引所有前導列的distinct的值都加進來。我們看到
索引的前導列id的值只有1，2兩個。所以我們就能簡單的理解oracle把
select * from test2 where empno=7788;
改寫成了
select * from test2 where id=1 and empno=7788 union all select * from test2 where id=2 and empno=7788;
所以我們看到 oracle的索引的跳躍式掃描僅僅適用於那些目標的前導列的distinct只數量比較少的情況，後續非前導列的值的選擇性有非常好的情況下。
索引範圍掃描
21:35:15 scott@orcl> select * from test where empno<300;
299 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 115135762
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 272 | 8976 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 272 | 8976 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_TEST | 272 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"<300)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
17688 bytes sent via SQL*Net to client
728 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
299 rows processed
適用於謂詞條件有選擇範圍的
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2124998/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 索引和不走索引的幾種形式Oracle索引
- 資料庫訪問幾種方式對比資料庫
- Springboot呼叫Oracle儲存過程的幾種方式Spring BootOracle儲存過程
- 【Oracle】 索引的掃描方式Oracle索引
- oracle產生事務transaction幾種方式或方法Oracle
- 前端基礎問題：CSS居中的幾種方式前端CSS
- Oracle索引梳理系列（六）- Oracle索引種類之函式索引Oracle索引函式
- Linux禁止某個IP地址訪問的幾種方法Linux
- css引入的幾種方式CSS
- Unity反射的幾種方式Unity反射
- Spring框架訪問資料庫的兩種方式的小案例Spring框架資料庫
- oracle資料庫透過sqlplus連線的幾種方式介紹Oracle資料庫SQL
- offline tablespace 的幾種方式 (轉)
- 程式碼迭代的幾種方式
- Spring注入Bean的幾種方式SpringBean
- react 路由的幾種使用方式React路由
- python 非同步的幾種方式Python非同步
- python的幾種輸出方式Python
- python的幾種輸入方式Python
- JavaScript的幾種繼承方式JavaScript繼承
- OAuth的幾種授權方式OAuth
- 詳解瀏覽器跨域訪問的幾種辦法瀏覽器跨域
- 【彙總】python檔案開啟的訪問模式有幾種?Python模式
- App相互喚醒的幾種方式APP
- 陣列去重的幾種方式陣列
- Express 提交資料的幾種方式Express
- python接收郵件的幾種方式Python
- Spring - 獲取ApplicationContext的幾種方式SpringAPPContext
- sqlplus常用的幾種登入方式SQL
- spring註冊bean的幾種方式SpringBean
- Golang語言排序的幾種方式Golang排序
- 檔案上傳的幾種方式
- sql最佳化的幾種方式SQL
- C#解析json的幾種方式C#JSON
- 實現 JavaScript 沙箱的幾種方式JavaScript
- 程式間的幾種通訊方式
- web方式訪問sshWeb
- 幾種結匯方式分享
- Oracle外網訪問Oracle