Oracle数据库表数据的访问方式（oracle数据库访问控制）

Oracle数据库访问表中数据的方式主要有以下两种方式。

直接访问表中的数据，即；全表扫描。

先访问相应的索引获取相应的ROWID，再通过该ROWID访问表中对应的数据（回表），即：索引扫描+回表。

1. 全表扫描

1.全表扫描的定义

Oracle数据库的全表扫描是指访问表中数据时，从该表在表空间中的第一个Extent中的第一个BLOCK开始访问，一致到该表的HWM（高水位线，High Water Mark）为止，该范围内的所有BLOCK均需要读取到SGA的Buffer Cache中。

如果执行查询的SQL语句指定了过滤条件（where条件），在访问过程中将进行过滤，并最终返回符合条件记录。

需要注意的是，即使第一条数据就满足条件，Oracle数据库仍会继续扫描剩余的数据，直到扫描完所有BLOCK为止。

Oracle数据库的执行计划中，关于全表扫描的描述为：TABLE ACCESS FULL。

2.全表扫描的性能分析

Oracle数据库在执行全表扫描时采用多块读的方式（这种方式一般为从SGA的Buffer Cache中读取，即：逻辑读或一致性读），在待查询的表的数据量不大的情况下，这种访问方式的效率是比较高的。

但是，表中的数据可能会逐渐增加，随着数据的增加，表的HWM也是逐渐递增的，这将导致对表执行全表扫描时所需要访问的BLOCK越来越多，I/O开销不断上涨，最后，相应SQL语句的执行时间将变长。

全表扫描可能导致Oracle数据库出现db file scattered read等待事件。

1. HWM对全表扫描性能的影响

对于Oracle数据库，在对一个表经常插入数据的情况下，如果存储占用量达到分配给该表的当前空间上限，此时该表的HWM将向上移动，即使使用了DELETE删除了数据，已经向上移动的HWM也不会收缩，如图1所示。

图1 HWM示意图

由此可知，即使使用了DELETE删除了表中的大部分数据，HWM还是停留在原来的位置，这时，采用全表扫描访问表时，仍需访问HWM之下的所有BLOCK。这就是一些表在删除大量数据后，其查询性能仍未改善的一个原因。

1. ROWID扫描

Oracle数据库的ROWID扫描是指访问表中的数据时，直接通过数据对应的ROWID进行定位和获取。

ROWID代表了ORACLE数据库中数据行所对应的物理存储地址。

Oracle数据库中的堆表存在一个名为ROWID的伪列，可以通过ROWID伪列得到数据行对应的ROWID的值，图2展示了ROWID伪列的值。

图2 ROWID伪列的值

Oracle数据库的执行计划中，关于ROWID扫描的描述注意要有TABLE ACCESS BY USER ROWID和TABLE ACCESS BY INDEX ROWID两大类。

TABLE ACCESS BY USER ROWID：在SQL语句的过滤条件（where）中直接使用了ROWID。

TABLE ACCESS BY INDEX ROWID：首先通过索引定位到待访问的数据记录获取相应的ROWID，之后通过该ROWID去表中访问该数据行，这就是所谓的回表访问。

1. 索引扫描

1.B-Tree索引简介

B-Tree索引是Oracle数据库常用的索引之一，其采用了B-Tree数据结构，图3是一个B-Tree索引的示例。

图3 B Tree索引示例图

如图3所示，B-Tree索引包含两种类型的节点，即：分支节点和叶子节点。

B-Tree索引的分支节点包含指向相应索引分支或叶子节点的指针和和对应的索引键值列，通过图3可知，索引的键值列可以并非是完整的索引键值，其可能只是一个前缀，只要Oracle数据库可以根据前缀列区分出相应的索引分支或叶子节点即可。这样就可以快速定位其下层的索引分支或叶子节点了。

在B-Tree中，最顶层的节点是根节点，Oracle数据库访问B-Tree索引时均从根节点开始。

B-Tree索引的叶节点存放着该索引的键值和记录该索引对应的数据行在标记物理存储位置的ROWID。此外，为了便于访问，所有的叶节点均用双相指针进行关联，组成一个双向列表。

1. B-Tree索引对表访问性能的影响

根据上述对B Tree索引的介绍可知B-Tree索引对表访问性能的影响有着如下的影响。

1. B-Tree索引中，所有的叶节点均在同一层，因此，这些叶节点距离根节点的深度都是相同的。所以，访问索引叶节点中的任何一个索引键值所产生的时间开销基本是相同的；
2. Oracle数据库确保B-Tree索引是平衡的，不会出现不同的索引的叶节点分布在不同层的问题，且多数情况下，Oracle数据库将B-Tree索引的深度控制在2或者3，使得在索引中找到一个键只需要2或3次I/O即可，确保了访问效率；
3. 通过B-Tree索引访问数据行的效率不会随着表中数据量的增加而明显下降，这是索引扫描与全表扫描在访问性能上的最大区别。

再次强调，通过B-Tree索引扫描数据时，Oracle数据库首先访问B-Tree索引，之后根据得到的ROWID回表访问相应的数据行。这两步操作均需要一定的I/O开销，所以，索引扫描的成本主要由从B-Tree根节点至分支节点再到叶节点获取ROWID的B-Tree遍历成本和通过ROWID到表中获取对应的数据行所属的BLOCK的访问成本两部分组成。

1. 常见的基于B-Tree索引的扫描
2. 索引唯一扫描

索引唯一扫描针对于访问唯一性索引（Unique Index）。

Oracle数据库的执行计划中，关于索引唯一扫描的描述为：INDEX UNIQUE SCAN。

该扫描方式仅适用于where条件为等值查询的SQL语句，且过滤条件列创建了唯一索引或为主键，扫描结果最多返回一条数据记录。

以下展示了一个索引唯一扫描的示例。

【示例1】

查询 employees 表中员工号为 1号的雇员，语句的执行计划如下所示：

select empno,ename

from employees

where empno =1

该表作为查询条件的empno列设置了基于B-Tree的唯一索引，where条件指定了该设置唯一索引的列，所以执行时采用了索引唯一扫描（INDEX UNIQUE SCAN）的方式。此外，因获取查询结果的ename列未设置索引，所以需要通过滤出符合查询条件的empno列结果集中的ROWID再去表中读取相应的记录。

1. 索引范围扫描

索引唯一扫描即可以用于访问唯一性索引（Unique Index），也可以用于访问非唯一性索引。

Oracle数据库的执行计划中，关于索引范围扫描的描述为：INDEX RANGE SCAN。

当where条件中符合以下情形将使用索引范围扫描。

在唯一索引或主键列上使用了range操作符（>、 < 、<>、 >= 、<=、 between and）。

在组合索引上，只使用部分列进行查询，导致查询出多行。

对非唯一索引列上进行的任何查询（等值、非等值）。

一般地，索引范围扫描会返回多条数据记录。

索引范围扫描执行的是单块读，对应的等待事件为db file sequential read。

以下展示了一个索引范围扫描的示例。

【示例2】

查询员工表emp，表中的empno列为主键，语句如下：

--查询条件为主键，查询员工号在指定范围内的记录

select empno,ename

from scott.emp

where empno>=7521 and empno<=8521

执行计划如下：

该操作返回若干符合条件的ROWID，再根据这些ROWID逐个去表中获取对应的记录（执行回表，因为ename列未创建索引）。

在对索引范围扫描进行分析时需要关注实际执行时索引范围扫描返回的数据量，如果返回少量的数据，则一般不会出现性能问题。

如果返回了大量的数据，在未出现回表访问的前提下，可以将其改为INDEX FAST FULL SCAN方式，这种方式采取多块读的方式，效率较INDEX RANGE SCAN的单块读方式高。

如果返回了大量数据且出现了回表操作，此时应考虑通过创建组合索引（达到索引覆盖）或使用全表扫描方式替代索引范围扫描。

1. 索引全扫描

索引全扫描指对基于B-Tree索引的所有叶节点进行了遍历。

Oracle数据库的执行计划中，关于索引全扫描的描述为：INDEX FULL SCAN。

当查询的SQL语句出现以下情况，将使用索引全扫描。

select的列是索引列，且未指定where过滤条件。

对索引列执行了order by操作，且order by中各列的顺序与索引中各列的顺序一致（复合索引）或第一列为索引列。

对索引列进行了聚合运算。

执行了分页操作。

需要注意的是，以下情况将不采用索引全扫描。

如果索引列的值允许空值（NULL），则对该索引列执行order by操作时，将执行全表扫描。

如果order by 中的第一列存在过滤条件，则会执行索引范围扫描（INDEX RANGE SCAN）。

如果执行查询的表数据量过大，则执行TABLE ACCESS FULL+SORT ORDER BY 。

索引全扫描执行时，以顺序扫描索引相应HWM下的所有数据块。

该操作执行的是单块读，按照索引逻辑顺序依次读取索引数据块，因此返回的数据是有序的（默认升序）。

索引全扫描对应的等待事件为db file sequential read。

在对索引全扫描进行分析时需要关注以下问题。

对于索引全扫描，需要检查是否出现回表操作。

如果未出现回表操作，需要检查索引段大小，如果索引段较大（例如达到GB级别），应使用INDEX FAST FULL SCAN方式代替该INDEX FULL SCAN方式，因为INDEX FAST FULL SCAN采取多块读的方式。

在未执行分页查询的情况下，如果索引全扫描出现了回表操作，多数情况下，采用这种方式的执行计划是错误的，因为回表操作也是单块读方式。

此时，应使用全表扫描的方式，全表扫描采取多块读的方式。

在执行分页查询的情况下，如果执行了索引全扫描后又执行了回表操作，这种方式是可以接受的。

1. 索引快速全扫描

同索引全扫描，索引快速全扫描也是对基于B-Tree索引的所有叶节点进行了遍历。

Oracle数据库的执行计划中，关于索引全扫描的描述为：INDEX FAST FULL SCAN。

当查询的SQL语句出现以下情况，将使用索引快速全扫描。

在查询时可通过HINT，强制提示优化器使用此方式，即：/*+index_ffs(table col)*/。

在没有指定where过滤条件的前提下，select中选择的列为索引列。

在指定where过滤条件时，且返回较多的数据，select中选择的列与where条件中过滤的列，构成复合索引，且where条件中的过滤列复合索引前缀列原则。

索引快速全扫描执行时，以无序扫描索引相应段HWM下的所有数据块。采用多块读的方式，相应的等待事件为db file scattered read，如果采取并行扫描的方式，相应的等待事件为direct path read。

索引快速全扫描与索引全扫描（INDEX FULL SCAN）的区别在于，该操作不会按索引的逻辑顺序依次访问索引数据块，而是以并行多块方式读取索引数据块，因此产生的数据可能是无序的。

索引全扫描按照索引逻辑顺序依次读取索引数据块，不是以并行多块方式读取，产生的数据是有序的。

在对索引快速全扫描进行分析时需要关注以下问题。

当需要从表中查询出大量数据但只需要获取表中部分列的数据时，可以使用索引快速扫描替代全表扫描，达到提升性能的目的。

Oracle 12c数据库，可以启用IN MEMORY OPTION特性，这样使得表中的数据以列的形式存放在内存中，直接访问内存中的数据即可，无需再使用索引快速全扫描。

1. 索引跳跃扫描

索引跳跃扫描针对于基于B-Tree的复合索引，包括唯一索引和非唯一索引。

如果SQL查询语句的where条件中未对采用的复合索引的前导列指定查询条件但同时又对该复合索引的非前导列指定了查询条件，索引跳跃扫描使得该查询执行时仍可命中该复合索引，使用该复合索引的非前导列执行扫描。

此外，如果where条件中指定了复合索引的前导列，但前导列的选择性过低，此时，也会采用索引跳跃扫描的方式。

Oracle数据库的执行计划中，关于索引跳跃扫描的描述为：INDEX SKIP SCAN。

索引跳跃扫描以单块读的方式执行，相应的等待事件为db file sequentinal read。

以下展示了一个索引跳跃扫描的示例。

【示例3】

emp表中，对JOB和ename列创建复合索引，顺序为（JOB,ename），查询时where过滤条件使用了后面的ename列，如下所示：

--创建复合索引

create index ind_EMP_JENAME ON scott.emp(JOB,ename)

--执行查询，条件中未使用复合索引中的前导列

select empno,ename

from scott.emp

where ename='ALLEN'

执行计划如下：

索引跳跃扫描的性能不如扫描前导列高，其一般在前导列键值比较少（前导列的选择性较低，或者复合索引中CBO计算后发现前几个列的选择性均较低）的情况下，会有较好的性能。

对于索引跳跃扫描的调优，可采取以下方式将调整为索引范围扫描（INDEX RANGE INDEX）。

为where条件列单独创建索引。

调整复合索引列中的顺序，使得where条件中的列作为复合索引中的前导列。

调整复合索引，尽量将选择性较高的列作为前导列。

1. 关于表扫描方式的调优总结

1. Oracle 11g中，在对一个大表进行全表扫描时，将表直接读入PGA，绕过SGA中的buffer cache，此时的等待事件为“direct path read”。

这种方式将导致统计信息不准确，因此一般禁用此方式，即：

alter system set “_serial_direct_read”=false;

1. 尽量避免在Oracle中定义clob类型的列，对该字段执行全表扫描时将导致性能严重下降（clob类型的列单独存放在一个段中）。

可以考虑将clob类型的列拆分为多个varchar2(4000)的列，或将这类的值存放到nosql数据库中，例如mongodb。

1. 如果表中有部分块已经缓存在buffer cache中，执行全表扫描时，扫描到已经被缓存的块所在的区时，将引起I/O中断。

如果一个表不同的区有大量块缓存在buffer cache中，此时，全表扫描的性能将严重下降（例如，较多的“db file scattered read”等待）。

1. 如果表中正在执行较大的事务，此时对该表执行全表扫描，会从undo日志读取部分数据，这种读取方式只能以单块读的方式进行，使得全表扫描的效率非常低下（出现较多的db file sequential read等待）。

建议使用批量游标方式处理较大的事务。

1. 在表数据较多的极端情况下，对于没有指定过滤条件（无where条件）的全表扫描，检查查询涉及的字段数（select的字段），如果涉及的字段数不多，可以将这些字段创建成一个复合索引，将全表扫描（TABLE ACCESS FULL）调优为索引快速全扫描（INDEX FAST FULL SCAN）。

6.在表数据较多的情况下，如果指定了过滤条件（有where条件），出现了全表扫描操作，可按按以下步骤调整：

（1）使用“select count(*) from <table>”查看表中的记录总数；

（2）使用“select count(*) from <table> where <col>...”查看该过滤条件下，返回的记录数；

（3）如果返回的记录数在表总记录数的5%以内，可以考虑在相应的过滤列上建立索引，如果有多个过滤条件列，需要创建包含这些列的复合索引，且将选择性高的列作为前缀列，同时where条件中的过滤列也应与创建的复合索引一致或至少包含前缀列；

如果索引已经创建，但仍执行全表扫描，需要对相关表的统计信息进行检查，特别是直方图信息，必要时重新收集统计信息。

（4）如果返回的记录数超过表总记录数的5%，如果select获取的字段不多，可以将这些字段和where条中的字段创建复合索引，通过相关索引访问的方式调优全表扫描。

创建原则为：where条件中的过滤字段作为前缀列，表连接涉及的字段放于中部，select中的字段位于最后部分。

如果select中的字段较多，则只能执行全表扫描操作。

7.如果表中包含大量空或接近与空的块，将影响全表扫描的性能，因为要读取块才可以确定其中是否包含数据。

一个导致表产生大量分布稀疏块的常见场景是当表删除多于插入时。

虽然表中可以删除大量的数据，但执行全表扫描所读取的块并未减少，因为HWM线未降低。

此时，需要降低HWM。

1. 对于较大的表，如果只能执行全表扫描操作，此时应考虑将较大的表进行分区，查询时采取分区剪裁的方式。

9.过滤条件（where）中对于单列索引列的IS NULL操作，将无法使用索引，此时将使用全表扫描，如果要调优为索引扫描，可将该单列索引与0（或其他整数值）组合创建复合索引，即：(col,0)。

此时，将采用索引复合扫描（INDEX RANGE SCAN）的方式。

10.在条件允许的情况下，通过索引覆盖可以进一步消除索引扫描后的回表操作。