合 Oracle中的表的访问方式有哪几种
Tags： OracleROWID全表扫描索引全扫描索引唯一扫描索引快速全扫描索引扫描索引范围扫描索引跳跃扫描
👉 本文共约4485个字，系统预计阅读时间或需17分钟。
目录
访问表的方式也叫优化器访问路径，主要有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再回表读就可以迅速找到需要的内容。
索引扫描类型有如下几种类型：
| 类型 | 简介 | 索引类型 |
|---|---|---|
| 索引唯一扫描（INDEX UNIQUE SCAN） | 索引唯一扫描是针对唯一性索引（UNIQUE INDEX）的扫描，它仅仅适用于WHERE条件里是等值查询的目标SQL。因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。对于组合唯一索引而言，WHERE条件需要列出所有的索引列才能使用索引唯一扫描。 | 唯一索引 |
| 索引范围扫描（INDEX RANGE SCAN） | 在同等条件下，当目标索引的索引行的数量大于1时，索引范围扫描所耗费的逻辑读至少会比相应的索引唯一性扫描的逻辑读多1。使用索引范围扫描的3种情况：①在唯一索引列上使用了范围操作符（>、<、<>、>=、<=、BETWEEN）。②在组合索引上，只使用部分列进行查询，导致查询出多行。③在非唯一索引列上进行的任何查询。 | 所有类型的B树索引 |
| 索引全扫描（INDEX FULL SCAN） | 索引全扫描需要扫描目标索引所有叶子块的所有索引行。这里需要注意的是，索引全扫描需要扫描目标索引的所有叶子块，但这并不意味着需要扫描该索引的所有分支块。在默认情况下，Oracle在做索引全扫描时只需要通过访问必要的分支块定位到位于该索引最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索引所有叶子块的所有索引行了。索引全扫描的执行结果是有序的，并且是按照该索引的索引键值列来排序，这也意味着走索引全扫描能够既达到排序的效果，又同时避免了对该索引的索引键值列的真正排序操作默认情况下，索引全扫描的扫描结果的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。通常情况下，索引全扫描是不需要回表的，所以索引全扫描适用于目标SQL的查询列全部是目标索引的索引键值列的情形。默认情况下，索引全扫描的扫描结果的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。通常情况下，索引全扫描是不需要回表的，所以索引全扫描适用于目标SQL的查询列全部是目标索引的索引键值列的情形。Oracle中能做索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是NOT NULL。 | 所有类型的B树索引 |
| 索引快速全扫描（INDEX FAST FULL SCAN） | 和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。索引快速全扫描的Hint为INDEX_FFS。索引快速全扫描与索引全扫描相比有如下三点区别：①索引快速全扫描只适用于CBO，而索引全扫描既可以用于CBO也可以用于RBO。②索引快速全扫描可以使用多块读，也可以并行执行。③索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序（对于单个索引叶子块中的索引行而言，其物理存储顺序和逻辑存储顺序一致；但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序）。 | 所有类型的B树索引 |
| 索引跳跃扫描（INDEX SKIP SCAN） | 索引跳跃扫描发生在多个列建立的复合索引上，如果SQL中谓词条件只包含索引中的部分列，并且这些列不包含建立索引时的第一列（前导列），就可能发生索引跳跃扫描。索引跳跃扫描仅仅适用于那些目标索引前导列的DISTINCT值数量较少、后续非前导列的可选择性又非常好的情形，因为索引跳跃扫描的执行效率一定会随着目标索引前导列的DISTINCT值数量的递增而递减。可以通过“ALTER SYSTEM SET "_OPTIMIZER_SKIP_SCAN_ENABLED" = FALSE SCOPE=SPFILE;”来禁用索引跳跃扫描。索引跳跃扫描的Hint为INDEX_SS。 | 所有类型的复合B树索引 |
（1）索引唯一扫描（INDEX UNIQUE SCAN）
索引唯一扫描是针对唯一性索引（UNIQUE INDEX）的扫描，它仅仅适用于WHERE条件里是等值查询的目标SQL。因为扫描的对象是唯一性索引，所以索引唯一性扫描的结果至多只会返回一条记录。对于组合唯一索引而言，WHERE条件需要列出所有的索引列才能使用索引唯一扫描。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | 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）。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
