【DB筆試面試593】在Oracle中，表的訪問方式有哪幾種？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，表的訪問方式有哪幾種？

♣

答案部分

訪問表的方式也叫優化器訪問路徑，主要有3種訪問路徑：全表掃描（FULL TABLE SCAN，FTS）、索引掃描（INDEX SCAN）和ROWID訪問。

（一）全表掃描（FULL TABLE SCAN，FTS）

全表掃描將讀取高水位（High Warter Mark，HWM）之下的所有數據塊，所有行都要經WHERE子句過濾來判斷是否滿足條件。當Oracle執行全表掃描時，會按順序讀取每個塊且只讀一次，如果能夠一次讀取多個塊，那麼可以有效地提高效率，初始化參數DB_FILE_MULTIBLOCK_READ_COUNT用來設置在一次I/O中可以讀取多少個數據塊。通常應該避免全表掃描，但是在檢索大量數據時全表掃描優於索引掃描，這正是因為全表掃描可以在一次I/O中讀取多個塊，從而減少了I/O的次數。在使用全表掃描的同時也可以使用並行來提高掃描的速度。全表掃描的Hint為：FULL(T)。

CBO優化器在以下幾種情況下會選擇全表掃描：

①　無合適的索引。

②　檢索表中絕大多數的數據。

③　表非常小。例如，表中的塊小於DB_FILE_MULTIBLOCK_READ_COUNT，只需一次I/O。如果這樣的表被頻繁使用，應該執行「ALTER TABLE TABLE_NAME STORAGE(BUFFER_POOL KEEP);」將表保存在內存中。

④　高並行度。如果在表級設置了較高的並行度，例如「ALTER TABLE T_NAME PARALLEL 4;」，那麼通常會選擇全表掃描。通常建議在語句級用HINT來實現並行，例如/*+ FULL(T_NAME) PARALLEL(T_NAME 4)*/。

⑤　太舊的統計數據。如果表沒有進行過分析或很久沒有再次分析，那麼CBO可能會錯誤的認為表含有及少的數據塊。

⑥　在語句中嵌入了全表掃描的Hint。

⑦　WHERE子句的索引列上只存在極少數不同的值。

需要注意的是，由於全表掃描是掃描高水位以下的所有數據塊，所以即使使用DELETE語句清空了目標表中的所有數據，高水位線還是會在原來的位置，這意味着對該表的全表掃描操作所耗費的時間與刪除之前相比並不會有明顯的改觀。

（二）索引掃描（INDEX SCAN）

索引不僅包含被索引的字段值，還包含行的位置標識ROWID，如果SQL語句只檢索索引字段，那麼Oracle將直接從索引中讀取而不需要通過ROWID去訪問表；如果SQL語句通過索引檢索其它字段值，那麼Oracle通過索引獲得ROWID再回表讀就可以迅速找到需要的內容。

索引掃描類型有如下幾種類型：

（1）索引唯一掃描（INDEX UNIQUE SCAN）

索引唯一掃描是針對唯一性索引（UNIQUE INDEX）的掃描，它僅僅適用於WHERE條件里是等值查詢的目標SQL。因為掃描的對象是唯一性索引，所以索引唯一性掃描的結果至多只會返回一條記錄。對於組合唯一索引而言，WHERE條件需要列出所有的索引列才能使用索引唯一掃描。

LHR@orclasm > set line 9999  LHR@orclasm > select * from scott.emp t where t.empno=10;    Execution Plan  ----------------------------------------------------------  Plan hash value: 2949544139    --------------------------------------------------------------------------------------  | 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    |     1 |    38 |     1   (0)| 00:00:01 |  |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |  --------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."EMPNO"=10)    LHR@orclasm > select * from scott.emp t where t.empno>=10 and t.empno<=10;    Execution Plan  ----------------------------------------------------------  Plan hash value: 2949544139    --------------------------------------------------------------------------------------  | 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    |     1 |    38 |     1   (0)| 00:00:01 |  |*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |  --------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."EMPNO"=10)    LHR@orclasm > create table t_emp_lhr as select * from scott.emp;    Table created.  LHR@orclasm > create unique index idx_dup_lhr on t_emp_lhr(empno,ename,job);    Index created.    LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr';    Execution Plan  ----------------------------------------------------------  Plan hash value: 2495657605    -------------------------------------------------------------------------------------------  | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |             |     1 |    87 |     0   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_EMP_LHR   |     1 |    87 |     0   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IDX_DUP_LHR |     1 |       |     0   (0)| 00:00:01 |  -------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr')    Note  -----     - dynamic sampling used for this statement (level=2)    LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr' and t.job='dba';    Execution Plan  ----------------------------------------------------------  Plan hash value: 859693366    -------------------------------------------------------------------------------------------  | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |             |     1 |    87 |     1   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_EMP_LHR   |     1 |    87 |     1   (0)| 00:00:01 |  |*  2 |   INDEX UNIQUE SCAN         | IDX_DUP_LHR |     1 |       |     0   (0)| 00:00:01 |  -------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr' AND "T"."JOB"='dba')  

（2）索引範圍掃描（INDEX RANGE SCAN）

使用索引範圍掃描的3種情況：①在唯一索引列上使用了範圍操作符（>、<、<>、>=、<=、BETWEEN）。②在組合索引上，只使用部分列進行查詢，導致查詢出多行。③在非唯一索引列上進行的任何查詢。

LHR@orclasm > select * from scott.emp t where t.empno>=10 and t.empno<=20;    Execution Plan  ----------------------------------------------------------  Plan hash value: 169057108    --------------------------------------------------------------------------------------  | 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    |     1 |    38 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | PK_EMP |     1 |       |     1   (0)| 00:00:01 |  --------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."EMPNO">=10 AND "T"."EMPNO"<=20)    LHR@orclasm > select * from t_emp_lhr t where t.empno=7369 and t.ename='lhr';    Execution Plan  ----------------------------------------------------------  Plan hash value: 2495657605    -------------------------------------------------------------------------------------------  | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |             |     1 |    87 |     0   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_EMP_LHR   |     1 |    87 |     0   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IDX_DUP_LHR |     1 |       |     0   (0)| 00:00:01 |  -------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."EMPNO"=7369 AND "T"."ENAME"='lhr')    Note  -----     - dynamic sampling used for this statement (level=2)      LHR@orclasm > create index idx_nounique_lhr on t_emp_lhr(DEPTNO);    Index created.    LHR@orclasm > select * from t_emp_lhr t where t.deptno=7369;    Execution Plan  ----------------------------------------------------------  Plan hash value: 4262540901    ------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  ------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                  |     1 |    87 |     1   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_EMP_LHR        |     1 |    87 |     1   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IDX_NOUNIQUE_LHR |     1 |       |     1   (0)| 00:00:01 |  ------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."DEPTNO"=7369)    Note  -----     - dynamic sampling used for this statement (level=2)    --索引降序範圍掃描（INDEX RANGE SCAN DESCENDING）  LHR@orclasm > select * from t_emp_lhr t where t.deptno between 7369 and 8000 order by deptno desc;    Execution Plan  ----------------------------------------------------------  Plan hash value: 3039488792    -------------------------------------------------------------------------------------------------  | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT             |                  |     1 |    87 |     0   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID | T_EMP_LHR        |     1 |    87 |     0   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN DESCENDING| IDX_NOUNIQUE_LHR |     1 |       |     0   (0)| 00:00:01 |  -------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."DEPTNO">=7369 AND "T"."DEPTNO"<=8000)    Note  -----     - dynamic sampling used for this statement (level=2)  

（3）索引全掃描（INDEX FULL SCAN）

索引全掃描需要掃描目標索引所有葉子塊的所有索引行。這裡需要注意的是，索引全掃描需要掃描目標索引的所有葉子塊，但這並不意味着需要掃描該索引的所有分支塊。在默認情況下，Oracle在做索引全掃描時只需要通過訪問必要的分支塊定位到位於該索引最左邊的葉子塊的第一行索引行，就可以利用該索引葉子塊之間的雙向指針鏈表，從左至右依次順序掃描該索引所有葉子塊的所有索引行了。索引全掃描的執行結果是有序的，並且是按照該索引的索引鍵值列來排序，這也意味着走索引全掃描能夠既達到排序的效果，又同時避免了對該索引的索引鍵值列的真正排序操作默認情況下，索引全掃描的掃描結果的有序性就決定了索引全掃描是不能夠並行執行的，並且通常情況下索引全掃描使用的是單塊讀。通常情況下，索引全掃描是不需要回表的，所以索引全掃描適用於目標SQL的查詢列全部是目標索引的索引鍵值列的情形。默認情況下，索引全掃描的掃描結果的有序性就決定了索引全掃描是不能夠並行執行的，並且通常情況下索引全掃描使用的是單塊讀。通常情況下，索引全掃描是不需要回表的，所以索引全掃描適用於目標SQL的查詢列全部是目標索引的索引鍵值列的情形。Oracle中能做索引全掃描的前提條件是目標索引至少有一個索引鍵值列的屬性是NOT NULL。

LHR@orclasm > create index idx_full_emp_lhr on scott.emp(empno,ename);    Index created.    LHR@orclasm > select empno, ename from scott.emp order by empno,ename;    Execution Plan  ----------------------------------------------------------  Plan hash value: 3792893151    -------------------------------------------------------------------------------------  | Id  | Operation        | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT |                  |    14 |   140 |     1   (0)| 00:00:01 |  |   1 |  INDEX FULL SCAN | IDX_FULL_EMP_LHR |    14 |   140 |     1   (0)| 00:00:01 |  -------------------------------------------------------------------------------------  

（4）索引快速全掃描（INDEX FAST FULL SCAN）

和索引全掃描一樣，索引快速全掃描也需要掃描目標索引所有葉子塊的所有索引行。索引快速全掃描的Hint為INDEX_FFS。索引快速全掃描與索引全掃描相比有如下三點區別：

①　索引快速全掃描只適用於CBO，而索引全掃描既可以用於CBO也可以用於RBO。

②　索引快速全掃描可以使用多塊讀，也可以並行執行。

③　索引快速全掃描的執行結果不一定是有序的。這是因為索引快速全掃描時Oracle是根據索引行在磁盤上的物理存儲順序來掃描，而不是根據索引行的邏輯順序來掃描的，所以掃描結果才不一定有序（對於單個索引葉子塊中的索引行而言，其物理存儲順序和邏輯存儲順序一致；但對於物理存儲位置相鄰的索引葉子塊而言，塊與塊之間索引行的物理存儲順序則不一定在邏輯上有序）。

LHR@orclasm > select /*+ index_ffs(t) */ empno from scott.emp t where empno>0;    Execution Plan  ----------------------------------------------------------  Plan hash value: 36645660    -----------------------------------------------------------------------------------------  | Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT     |                  |    14 |    56 |     2   (0)| 00:00:01 |  |*  1 |  INDEX FAST FULL SCAN| IDX_FULL_EMP_LHR |    14 |    56 |     2   (0)| 00:00:01 |  -----------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - filter("EMPNO">0)  

（5）索引跳躍掃描（INDEX SKIP SCAN）

索引跳躍掃描發生在多個列建立的複合索引上，如果SQL中謂詞條件只包含索引中的部分列，並且這些列不包含建立索引時的第一列（前導列），就可能發生索引跳躍掃描。索引跳躍掃描僅僅適用於那些目標索引前導列的DISTINCT值數量較少、後續非前導列的可選擇性又非常好的情形，因為索引跳躍掃描的執行效率一定會隨着目標索引前導列的DISTINCT值數量的遞增而遞減。可以通過「ALTER SYSTEM SET "_OPTIMIZER_SKIP_SCAN_ENABLED" = FALSE SCOPE=SPFILE;」來禁用索引跳躍掃描。索引跳躍掃描的Hint為INDEX_SS。

LHR@orclasm > select /*+index_ss(t)*/ * from t_emp_lhr t where t.ename='lhr';    Execution Plan  ----------------------------------------------------------  Plan hash value: 3374324980    -------------------------------------------------------------------------------------------  | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |  -------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |             |     1 |    87 |     2   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_EMP_LHR   |     1 |    87 |     2   (0)| 00:00:01 |  |*  2 |   INDEX SKIP SCAN           | IDX_DUP_LHR |     1 |       |     1   (0)| 00:00:01 |  -------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("T"."ENAME"='lhr')         filter("T"."ENAME"='lhr')    Note  -----     - dynamic sampling used for this statement (level=2)    LHR@orclasm > create table t_idxss_20170607_lhr as select owner,object_id,object_type,created from dba_objects;    Table created.    LHR@orclasm > create index idx_idxss_com on t_idxss_20170607_lhr(owner,object_id,object_type);    Index created.  LHR@orclasm >  exec dbms_stats.gather_table_stats(user,'t_idxss_20170607_lhr');    PL/SQL procedure successfully completed.    LHR@orclasm > select * from t_idxss_20170607_lhr where object_id=20 and object_type='TABLE';    Execution Plan  ----------------------------------------------------------  Plan hash value: 1285454804    ----------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |  ----------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                      |     1 |    28 |    41   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_IDXSS_20170607_LHR |     1 |    28 |    41   (0)| 00:00:01 |  |*  2 |   INDEX SKIP SCAN           | IDX_IDXSS_COM        |     1 |       |    40   (0)| 00:00:01 |  ----------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')         filter("OBJECT_ID"=20 AND "OBJECT_TYPE"='TABLE')  

（三）ROWID掃描

ROWID表示行在數據塊中的具體位置，ROWID是查找具體行的最快方式。可以在WHERE子句中寫入ROWID，但是不推薦這麼做。通常都是通過索引來獲得ROWID，但如果被檢索的行都包含在索引中時，那麼直接訪問索引就能得到所需的數據則不會使用ROWID。對Oracle中的堆表而言，可以通過Oracle內置的ROWID偽列得到對應行記錄所在的ROWID的值，然後通過DBMS_ROWID包中的相關方法（DBMS_ROWID.ROWID_RELATIVE_FNO獲取文件號、DBMS_ROWID.ROWID_BLOCK_NUMBER獲取塊號和DBMS_ROWID.ROWID_ROW_NUMBER獲取行號）將上述ROWID偽列的值翻譯成對應數據行的實際物理存儲地址。

需要注意的是，在通過ROWID直接訪問數據的情況下，執行計劃中常常是「TABLE ACCESS BY USER ROWID」，而非索引回表讀的操作（TABLE ACCESS BY INDEX ROWID）。如下所示：

SYS@orclasm > select rowid from scott.emp where rownum<=1;    ROWID  ------------------  AAAnvoAAhAAACnzAAA    SYS@orclasm > select * from scott.emp where rowid='AAAnvoAAhAAACnzAAA';         EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO  ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------        7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20      Execution Plan  ----------------------------------------------------------  Plan hash value: 1116584662    -----------------------------------------------------------------------------------  | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |  -----------------------------------------------------------------------------------  |   0 | SELECT STATEMENT           |      |     1 |    38 |     1   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY USER ROWID| EMP  |     1 |    38 |     1   (0)| 00:00:01 |  -----------------------------------------------------------------------------------      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets            1  consistent gets            0  physical reads            0  redo size         1021  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  

& 說明：

有關索引掃描類型的實驗操作過程可以參考作者BLOG：http://blog.itpub.net/26736162/viewspace-2139246/

本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。