深入理解ORACLE数据库的访问路劲

1 访问路径ACCESS PATH定义

在 Oracle 数据库中，数据访问路径是指数据库引擎用于检索和操作数据的方法和路径。列入全表扫描、索引扫描或者直接通过ROWID获取数据，要想成为SQL高手，就必须深入理解各种访问路径。

1.1 TABLE ACCESS FULL

TABLE ACCESS FULL表示全表扫描，一般情况下是多块读，HINT: FULL(表名/ 别名)。等待事件为db file scattered read。如果是并行全表扫描，等待事件为directpath read。在没有合适的索引可用或索引无法有效支持查询时，Oracle 可能会选择进行全表扫描。全表扫描会遍历整个表的数据块，以查找符合查询条件的行。全表扫描在某些情况下可能是必要的，但对于大表可能会导致性能下降。

ORACLE是如何进行全表扫描的呢？

首先我们先了解一下Oracle的逻辑存储结构，Oracle最小的存储单位是块（block），物理上连续的块组成了区（extent），区又组成了段（segment）。对于非分区表，如果表中没有clob/blob字段，那么一个表就是一个段。全表扫描，其实就是扫描表中所有格式化过的区。因为区里面的数据块在物理上是连续的，所以全表扫描可以多块读。全表扫描不能跨区扫描，因为区与区之间的块物理上不一定是连续的。对于分区表，如果表中没有clob/blob字段，一个分区就是一个段，分区表扫描方式与非分区表扫描方式是一样的。

对一个非分区表进行并行扫描，其实就是同时扫描表中多个不同区，因为区与区之间的块物理上不连续，所以我们不需要担心扫描到相同数据块。对一个分区表进行并行扫描，有两种方式。如果需要扫描多个分区，那么是以分区为粒度进行并行扫描的，这时如果分区数据不均衡，会严重影响并行扫描速度；如果只需要扫描单个分区，这时是以区为粒度进行并行扫描的。如果表中有clob字段，clob会单独存放在一个段中，当全表扫描需要访问clob字段时，这时性能会严重下降，因此尽量避免在Oracle中使用clob。我们可以考虑将clob字段拆分为多个varchar2（4000）字段，或者将clob存放在nosql数据库中，例如mongodb。一般的操作系统，一次I/O最多只支持读取或者写入1MB数据。数据块为8KB的时候，一次I/O最多能读取128个块。数据块为16KB的时候，一次I/O最多能读取64个块，数据块为32KB的时候，一次I/O最多能读取32个块。如果表中有部分块已经缓存在buffer cache中，在进行全表扫描的时候，扫描到已经被缓存的块所在区时，就会引起I/O中断。如果一个表不同的区有大量块缓存在buffer cache中，这个时候，全表扫描性能会严重下降，因为有大量的I/O中断，导致每次I/O不能扫描1MB数据。

举例：

select extent_id,blocks, block_id
from dba_extents
where segment_name = 'LIUXIAOBIN'
and owner = 'SYS';

测试表LIUXIAOBIN一共有28个区，而且每个区都没有超过128个块。正常情况下，对测试表LIUXIAOBIN进行全表扫描需要进行28次多块读。现在我们清空buffer cache缓存，对LIUXIAOBIN表进行全表扫描，同时使用10046事件监控等待事件。

 show parameter db_file_multiblock

alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
select count(*) from liuxiaobin;
alter session set events '10046 trace name context off';

查询Trace:

SELECT * FROM V$DIAG_INFO

正如我们猜想的那样，全表扫描多块读（db file scattered read）耗费了28次。

我们可以根据block_id为边界来判断rowid在哪个区。

select rowid,
dbms_rowid.rowid_relative_fno(rowid) file#,
dbms_rowid.rowid_block_number(rowid) block#
from LIUXIAOBIN;
alter system flush buffer_cache;
select count(*)from LIUXIAOBIN
 where rowid in ('AAAaj+AABAAAZC5AAA', 'AAAaj+AABAAAZDRAA1',
 'AAAaj+AABAAAZD/AA4', 'AAAaj+AABAAAZNcAAU');

 alter session set events '10046 trace name context forever, level 8';
 select count(*) from LIUXIAOBIN;

因为缓存了4个不同区的块在buffer cache中，全表扫描的时候需要中断4次I/O，所以全表扫描多块读一共耗费了32次。如果表正在发生大事务，在进行全表扫描的时候，还会从undo读取部分数据。从 undo读取数据是单块读，这种情况下全表扫描效率非常低下。因此，我们建议使用批量游标的方式处理大事务。使用批量游标处理大事务还可以减少对undo的使用，防止事务失败回滚太慢。

举例我们模拟一个大事务

update liuxiaobin set owner='SCOTT';

我们开启另一个会话，清空buffer cache缓存并且设置10046事件，然后运行查询。

alter system flush buffer_cache;
alter session set events '10046 trace name context forever, level 8';
select count(*) from liuxiaobin;
alter session set events '10046 trace name context off';

db file sequential read表示单块读，一共读取了1775次，这里的单块读就是大事务产生的undo所引起的。

Oracle行存储数据库在进行全表扫描时会扫描表中所有的列。

1.2 TABLE ACCESS BY USER ROWID

TABLE ACCESS BY USER ROWID表示直接用ROWID获取数据，是单块读。该访问路径在Oracle所有的访问路径中性能是最优的。

举例：

select * from liuxiaobin where rowid ='AAAaj+AABAAAZC5AAA'

1.3 TABLE ACCESS BY ROWID RANGE

TABLE ACCESS BY ROWID RANGE表示ROWID范围扫描，多块读。因为同一个块里面的ROWID是连续的，同一个EXTENT里面的ROWID也是连续的，所以可以多块读。

举例：

select * from liuxiaobin where rowid >='AAAaj+AABAAAZC5AAA'

1.4 TABLE ACCESS BY INDEX ROWIDTABLE ACCESS BY INDEX ROWID就是回表。索引返回多少行数据，回表就要回多少次，每次回表都是单块读（因为一个rowid对应一个数据块）。当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。回表已经在公众号文章（深入探索Oracle 回表原理、影响与优化技巧）中已经讲过这里不在深入谈论。

1.5 INDEX UNIQUE SCAN

INDEX UNIQUE SCAN表示索引唯一扫描，单块读。对唯一索引或者对主键列进行等值查找，就会走INDEX UNIQUE SCAN。因为对唯一索引或者对主键列进行等值查找，CBO能确保最多只返回1行数据，所以这时可以走索引唯一扫描。

select * from emp where empno=7369;

INDEX UNIQUE SCAN最多只返回一行数据，只会扫描“索引高度”个索引块，在所有的Oracle访问路径中，其性能仅次于TABLE ACCESS BY USER ROWID。

1.6 INDEX RANGE SCAN

INDEX RANGE SCAN表示索引范围扫描，单块读，返回的数据是有序的（默认升序）。HINT: INDEX（表名/别名索引名）。对唯一索引或者主键进行范围查找，对非唯一索引进行等值查找，范围查找，就会发生INDEX RANGE SCAN。等待事件为db file sequential read。

举例：

select * from liuxiaobin where object_id=200;

因为索引IDX_ID是非唯一索引，对非唯一索引进行等值查找并不能确保只返回一行数据，有可能返回多行数据，所以执行计划会进行索引范围扫描。索引范围扫描默认是从索引中最左边的叶子块开始，然后往右边的叶子块扫描（从小到大），当检查到不匹配数据的时候，就停止扫描。现在我们将过滤条件改为小于，并且对过滤列进行降序排序，查看执行计划。

举例：

select * from liuxiaobin where object_id<200  order by object_id desc;

INDEX RANGE SCAN DECENDING表示索引降序范围扫描，从右往左扫描，返回的数据是降序显示的。

注意：假设一个索引叶子块能存储100行数据，通过索引返回100行以内的数据，只扫描“索引高度”个索引块，如果通过索引返回200行数据，就需要扫描两个叶子块。通过索引返回的行数越多，扫描的索引叶子块也就越多，随着扫描的叶子块个数的增加，索引范围扫描的性能开销也就越大。如果索引范围扫描需要回表，同样假设一个索引叶子块能存储100行数据，通过索引返回1000行数据，只需要扫描10个索引叶子块（单块读），但是回表可能会需要访问几十个到几百个表块（单块读）。在检查执行计划的时候我们要注意索引范围扫描返回多少行数据，如果返回少量数据，不会出现性能问题；如果返回大量数据，在没有回表的情况下也还好；如果返回大量数据同时还有回表，这时我们应该考虑通过创建组合索引消除回表或者使用全表扫描来代替它。

1.7 INDEX SKIP SCAN

INDEX SKIP SCAN表示索引跳跃扫描，单块读。返回的数据是有序的（默认升序）。HINT: INDEX_SS（表名/别名索引名）。当组合索引的引导列（第一个列）没有在where条件中，并且组合索引的引导列/前几个列的基数很低，where过滤条件对组合索引中非引导列进行过滤的时候就会发生索引跳跃扫描，等待事件为db file sequential read。

创建组合索引：

create index idx_ownerid on liuxiaobin(owner,object_id);

查询一下表上已经建立了的索引情况：

SELECT
    i.table_name,
    i.index_name,
    ic.column_name
FROM
    all_indexes i
JOIN
    all_ind_columns ic ON i.index_name = ic.index_name
WHERE
    i.table_name = 'LIUXIAOBIN' -- 替换为您的表名
    AND i.owner = 'SYS' -- 替换为您的模式名/所有者
ORDER BY
    i.table_name,
    i.index_name,
    ic.column_position;

删除之前建立的非组合索引

drop index INDEX_OBJECT_ID;

select * from liuxiaobin where object_id<200

从执行计划中我们可以看到上面SQL走了索引跳跃扫描。最理想的情况应该是直接走where条件列object_id上的索引，并且走INDEX RANGE SCAN。但是因为where条件列上面没有直接创建索引，而是间接地被包含在组合索引中，为了避免全表扫描，CBO就选择了索引跳跃扫描。INDEX SKIP SCAN中有个SKIP关键字，也就是说它是跳着扫描的。那么想要跳跃扫描，必须是组合索引，如果是单列索引怎么跳？另外，组合索引的引导列不能出现在where条件中，如果引导列出现在where条件中，它为什么还跳跃扫描呢，直接INDEX RANGE SCAN不就可以了？再有，要引导列基数很低，如果引导列基数很高，那么它“跳”的次数就多了，性能就差了。当执行计划中出现了INDEX SKIP SCAN，我们可以直接在过滤列上面建立索引，使用INDEX RANGE SCAN代替INDEX SKIP SCAN。

1.8 INDEX FULL SCAN

INDEX FULL SCAN表示索引全扫描，单块读，返回的数据是有序的（默认升序）。HINT: INDEX（表名/别名索引名）。索引全扫描会扫描索引中所有的叶子块（从左往右扫描），如果索引很大，会产生严重性能问题（因为是单块读）。等待事件为db file sequential read。

索引全扫描以下三种情况会发生：

1.一般在分页语句中（后期单独讲ORACLE的分页语句）

2.SQL语句有order by选项，order by的列都包含在索引中，并且order by后列顺序必须和索引列顺序一致。order by的第一个列不能有过滤条件，如果有过滤条件就会走索引范围扫描（INDEX RANGE SCAN）。同时表的数据量不能太大（数据量太大会走TABLE ACCESS FULL + SORT ORDER BY）

举例：

create index idx_idowner on liuxiaobin(object_id,owner,0);
select * from liuxiaobin order by object_id,owner;

3.在进行SORT MERGE JOIN的时候，如果表数据量比较小，让连接列走INDEXFULL SCAN可以避免排序

select /*+ use_merge(e,d) */*
from emp e, dept d
where e.deptno = d.deptno;

当看到执行计划中有INDEX FULL SCAN，我们首先要检查INDEX FULL SCAN是否有回表。如果INDEX FULL SCAN没有回表，我们要检查索引段大小，如果索引段太大（GB级别），应该使用INDEX FAST FULL SCAN代替INDEX FULL SCAN，因为INDEX FAST FULL SCAN是多块读，INDEX FULL SCAN是单块读，即使使用了INDEX FAST FULL SCAN会产生额外的排序操作，也要用INDEX FAST FULL SCAN 代替INDEX FULL SCAN。如果INDEX FULL SCAN有回表，大多数情况下，这种执行计划是错误的，因为INDEX FULL SCAN是单块读，回表也是单块读。这时应该走全表扫描，因为全表扫描是多块读。如果分页语句走了INDEX FULL SCAN然后回表，这时应该性能还行。

1.9 INDEX FAST FULL SCAN

INDEX FAST FULL SCAN表示索引快速全扫描，多块读。HINT： INDEX_FFS（表名/别名索引名）。当需要从表中查询出大量数据但是只需要获取表中部分列的数据的，我们可以利用索引快速全扫描代替全表扫描来提升性能。索引快速全扫描的扫描方式与全表扫描的扫描方式是一样，都是按区扫描，所以它可以多块读，而且可以并行扫描。等待事件为db file scattered read，如果是并行扫描，等待事件为direct path read。

create index idx_ownername on liuxiaobin(owner,object_name,0);
select owner,object_name from liuxiaobin;

在做SQL优化的时候，我们不要只看逻辑读来判断一个SQL性能的好坏，物理I/O次数比逻辑读更为重要。有时候逻辑读高的执行计划性能反而比逻辑读低的执行计划性能更好，因为逻辑读高的执行计划物理I/O次数比逻辑读低的执行计划物理I/O次数低。

在Oracle数据库中，INDEX FAST FULL SCAN是用来代替TABLE ACCESSFULL的。因为Oracle是行存储数据库，TABLE ACCESS FULL会扫描表中所有的列，而INDEX FAST FULL SCAN只需要扫描表中部分列，INDEX FAST FULL SCAN就是由Oracle是行存储这个“缺陷”而产生的。在ORACLE 12c及之后版本的新特性IN MEMORY OPTION，直接访问内存中的数据，INDEX FAST FULLSCAN几乎也没有用武之地了。

1.10 INDEX FULL SCAN（MIN/MAX）

INDEX FULL SCAN（MIN/MAX）表示索引最小/最大值扫描、单块读，该访问路径发生在 SELECT MAX（COLUMN）FROM TABLE 或者SELECT MIN（COLUMN）FROM TABLE等SQL语句中。INDEX FULL SCAN（MIN/MAX）只会访问“索引高度”个索引块，其性能与INDEX UNIQUE SCAN一样，仅次于TABLE ACCESS BY USER ROWID。

举例：

select max(object_id) from liuxiaobin;

取最大和最小：

select max(object_id),min(object_id) from  liuxiaobin;

该SQL要同时查看object_id的最大值和最小值，如果想直接从object_id列的索引获取数据，我们只需要扫描索引中“最左边”和“最右边”的叶子块就可以。在Btree索引中，索引叶子块是双向指向的，如果要一次性获取索引中“最左边”和“最右边”的叶子块，我们就需要连带的扫描“最大值”与“最小值”中间的叶子块，而本案例中，中间叶子块的数据并不是我们需要的。如果该SQL走索引，会走INDEX FAST FULL SCAN，而不会走INDEX FULL SCAN，因为INDEX FAST FULL SCAN可以多块读，而INDEXFULL SCAN是单块读，两者性能差距巨大（如果索引已经缓存在buffer cache中，走INDEX FULL SCAN与INDEX FAST FULL SCAN效率几乎一样，因为不需要物理I/O）。

从上面的执行计划中我们可以看到SQL走了INDEX FAST FULL SCAN，INDEXFAST FULL SCAN会扫描索引段中所有的块，理想的情况是只扫描索引中“最左边”和“最右边”的叶子块。现在我们将该SQL改写为如下SQL：

select (select max(object_id) from liuxiaobin),
       (select min(object_id) from liuxiaobin) from dual;

原始SQL因为需要1次性从索引中取得最大值和最小值，所以导致走了INDEXFAST FULL SCAN。我们将该SQL进行等价改写之后，访问了索引两次，一次取最大值，一次取最小值，从而避免扫描不需要的索引叶子块，大大提升了查询性能。

1.11 MAT_VIEW REWRITE ACCESS FULL

MAT_VIEW REWRITE ACCESS FULL表示物化视图全表扫描、多块读。因为物化视图本质上也是一个表，所以其扫描方式与全表扫描方式一样。如果我们开启了查询重写功能，而且SQL查询能够直接从物化视图中获得结果，就会走该访问路径。

举例：

create materialized view liuxiaobin_mv
 build immediate enable query rewrite
 as select object_id,object_name from liuxiaobin;
 
 select object_id,object_name from liuxiaobin_mv;

数据访问路径的选择和优化是一个复杂的过程，需要综合考虑多个因素。每个数据库环境都是独特的，因此最佳的优化策略可能会有所不同。深入了解数据库架构、数据分布、业务需求和查询模式是成功优化数据访问路径的关键。通过合理的设计、索引管理和查询优化，可以提高 Oracle 数据库的性能和响应时间，确保高效的数据访问。