【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程序员面试笔试宝典》，作者：李华荣。