一文带你搞懂 Oracle SQL访问路径方法理论+案例

原创 codePanda 云和恩墨 2024-08-28

1029

Oracle SQL 语句访问路径详解

在Oracle数据库中，SQL语句的执行涉及多种访问路径，这些路径决定了Oracle如何找到并返回用户需要的数据。理解和优化这些访问路径对数据库性能优化至关重要，知己知彼才能百战百胜嘛。本文将详细介绍Oracle的各种SQL访问路径，包括全表扫描、ROWID访问和索引扫描等方法，并结合实际案例，帮助您深入理解这些机制。

1. 什么是SQL访问路径？

SQL访问路径是指Oracle找到用户需要的数据的方法。在执行SQL查询时，Oracle优化器会选择一种最佳的访问路径，以最小的代价返回结果。访问数据的方法分为访问表和访问索引，访问路径是Oracle数据库访问表数据的不同方式。主要的访问路径包括：

全表扫描：直接读取整个表的数据块。
ROWID访问：直接通过数据的物理地址访问特定行。
索引访问：利用索引定位数据，包括多种扫描方式。

每种访问路径都有其适用场景和性能特性，不同的查询和数据条件下，访问路径的选择会有所不同，了解这些路径有助于优化SQL查询。

2. 常见的访问路径类型

文章中用到的环境见：每天查询三百万次的TOP SQL，数据竟是空的？ - 墨天轮

2.1 全表扫描（Full Table Scan）

全表扫描又可简称为全扫，或全扫描，是指Oracle读取表中的所有数据块来执行查询。Oracle 将读取指定段中用于某一点或另一点上的每个块。全扫描就是批读取所有的块，准确说是读取段高水标记之下所有的块。在这里，Oracle 将从段的开始读到结尾。处理每一个块。全扫描是读取 Oracle 的大量数据的行之有效的方法。因为数据库将使用多块读取。由于 Oracle 知道它打算读取读段中的每一块，因此它将一次性读取多个块，而非一次一个块。多块读的数量由初始化参数 DB_FILE_MULTIBLOCK_READ_COUNT参数决定。
在扫描过程中，数据库会从表的第一个块开始，顺序读取直到高水位线（HWM）。尽管全表扫描经常被视为低效的操作，但在处理大量数据时，它可能是最优选择，因为它可以利用多块读取来提高吞吐量。平常我们经常要避免全表扫描操作，不是说全表扫描不好，事实上Oracle 在做全表扫描操作时会使用多块读，这在目标表的数据量不大时执行效率是非常高的，但全表扫描最大的问题就在于走全表扫描的目标SQL 的执行时间会不稳定、不可控，这个执行时间一定会随着目标表数据量的递增而递增。因为随着目标表数据量的递增，它的高水位线会一直不断往上涨，所以全表扫描该表时所需要读取的数据块的数量也会不断增加，（比如上一篇文章的案例，都没有数据了，走全表扫描还是78M的逻辑读，这个时候如果不走全表那是不是？）这意味着全表扫描该表时所需要耗费的I/O资源会随之不断增加，当然完成对该表的全表扫描操作所需要耗费的时间也会随之增加。另外，对于CBO而言，所要耗费的 I/O 资源不断增加则意味着全表扫描的成本值也会随着目标表数据量的递增而递增。
这种扫描适用于表较小或查询需要返回大部分行的情况。当没有合适的索引可用时，Oracle会选择全表扫描。

SELECT * FROM TAB2 WHERE NAME = 'TEST10';

如果NAME列没有索引，Oracle可能会选择全表扫描。这种扫描在大表上效率较低，逻辑读有4697，我们在name字段建上索引后再次查询，逻辑读降到了3，经过三次对比到叶子节点，发现没有想要的值直接返回了（块上没有行迁移和行链接，正常有一个值的话逻辑读应该是4，根据索引叶子节点的rowid找到对应的物理存储，回表读一个块），而全表扫描需要把所有快读一遍，性能直接提升千倍，等着老板给你涨工资吧，哈哈哈，开个玩笑，一般情况下是遇不到这么简单的情况的。

2.2 ROWID访问（Table Access by ROWID）

ROWID是Oracle为每一行数据分配的唯一标识符，是Oracle 中的数据行记录所在的物理存储地址，也就是说ROWID 实际上是和 Oracle 中数据块里的行记录一一对应的。通过ROWID访问数据是非常高效的，因为它直接定位到数据行。然而，当需要检索大量数据时，逐个使用ROWID访问每行可能导致较高的I/O成本，因此在这种情况下，ROWID访问并非最佳选择。
常见于已经通过索引找到行的ROWID后进行的数据访问。先随便拿几个rowid

SELECT * FROM TAB2 WHERE ROWID = 'AAAV3jAAMAAAACSAAI';

在这个示例中，我们直接通过ROWID定位并返回数据。ROWID 对于获取一个特定的行来说可能是最快的方法。但是，使用ROWID 获取成千上万的行，并不是最好的方法。因为每使用 ROWID 访问一行，都是一次逻辑读。在不使用 ROWID 访问行时，Oracle 将在一次逻辑读中访问尽量多的行。

想要走这个方式，不只是直接指定ROWID，其实每一个表都有一个 ROWID 伪列，此伪列中记录了每一行的物理地址。它包含文件号、块号和行在该块中的行号，最常见的索引的回表其实就是ROWID访问。
总结，Oracle中的ROWID扫描大致有两层含义：一种是根据用户在SOL语中输入的ROWID的值直接去访问对应的数据行记录；另外一种是先去访问相关的索引，然后根据访问索引后得到的ROWID 再回表去访问对应的数据行记录。

2.3 索引扫描（Index Scan）

索引扫描是通过索引结构来访问数据的方式，也是Oracle中提高查询性能的重要工具。根据查询条件，Oracle可以选择不同类型的索引扫描：

1）索引唯一扫描（Index Unique Scan）

索引唯一扫描 用于唯一性索引，每次至多只返回一条记录，当查询条件能唯一定位到一行时，Oracle通过索引找到对应的ROWID，然后访问数据行。表中某字段存在 UNIQUE、PRIMARY KEY 约束时，Oracle常实现唯一性扫描；这里的此时数据没有唯一约束的，这是另建的环境TAB22，或者在建一张表并修改ACCOUNTID为唯一约束，如果用的我的测试数据TAB2表ACCOUNTID不唯一，修改不了的哦。

ALTER TABLE tab22 ADD CONSTRAINT uq_tab1_acctid UNIQUE (ACCOUNTID);
--
SYS@orcl> CREATE TABLE tab11 (
  2      ACCOUNTID NUMBER(20) CONSTRAINT uq_tab1_accid UNIQUE,
  3      region NUMBER(20),
  4      name VARCHAR2(100),
  5      sex VARCHAR2(100),
  6      created_time DATE
  7  );
  Table created.
INSERT INTO tab11 (ACCOUNTID, name, region, sex, created_time)
SELECT rownum, 'test' || rownum, 310, DECODE(MOD(rownum, 2), 0, '男', '女'), 
       TO_DATE('2023-04-15 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
FROM dual
CONNECT BY rownum <= 100000;
commit;

SELECT ACCOUNTID, NAME FROM TAB2 WHERE ACCOUNTID = 12345;

如果ACCOUNTID列有唯一索引，Oracle会使用索引唯一扫描来高效获取数据。

索引唯一扫描和使用 ROWID 访问行是差不多的。只不过是把每一行的 ROWID 记录到另一处地方，它被叫做索引，每次访问行时，先访问索引，从索引中取出行的 ROWID，根据 ROWID 再真正的访问行，看执行计划的父节点也标注出来了，和上面提到的rowid访问正好对应起来了。

2）索引范围扫描（Index Range Scan）

索引范围扫描适用于返回多行且这些行在索引中是连续存储的查询。通常用于条件范围查询。使用一个索引存取多行数据；发生索引范围扫描的三种情况：

在唯一索引列上使用了范围操作符（如：> < <> >= <= between）即使是在唯一索引上，Oracle会执行索引范围扫描。因为这些操作符不会匹配单一值，而是匹配一个值范围，可能返回多行结果。
在组合索引（复合索引）上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描）
对非唯一索引列上进行的任何查询，因为一个索引键值可能对应多行数据。

注：在同等条件下，当目标索引的索引行的数量大于1时，索引范围扫描所耗费的逻辑读至少会比相应的索引唯一性扫描的逻辑读多1。

SELECT ACCOUNTID, NAME FROM TAB2 WHERE ACCOUNTID < 50;

如果ACCOUNTID列有索引，Oracle会使用索引范围扫描。

**思考： “ACCOUNTID < 50” 使用了索引扫描，而 “ACCOUNTID < 5000” 则进行了全表扫描， **

3）索引全扫描（Index Full Scan）

索引全扫描不读取索引结构上的每个块，这与其名字及我们关于全扫描的知识相背。可以这样说，它根据部分枝块，找到第一个叶块，然后按叶块双向链表的顺序，处理所有的叶块。不需要扫描该索引的所有分支块。在默认情况下，Oracle 在做索引全扫描时只需要通过访问必要的分支块定位到位于该索引最左边的叶子块的第一行索引行，就可以利用该索引叶子块之间的双向指针链表，从左至右依次顺序扫描该索引所有叶子块的所有索引行了。
默认情况下，索引全扫描的扫描结果的有序性就决定了索引全扫描是不能够并行执行的，并且通常情况下索引全扫描使用的是单块读。
通常情况下，索引全扫描是不需要回表的，所以索引全扫描适用于目标SOL 的查询列全部是目标索引的索引键值列的情形。我们知道，对于 Oracle 数据库中的 B 树索引而言，当所有索引键值列全为 NULL 值时不入索引(即当所有索引键值列全为 NULL 值时，这些 NULL值不会在 B 索引中存在)，这意味着Oracle 中能做索引全扫描的前提条件是目标索引至少有一个索引键值列的属性是 NOT NULL。这是很显然的事情，如果目标索引的所有索引键值列的属性均为允许 NULL 值，此时如果还走索引全扫描，就会漏掉目标表中那些索引键值列均为 NULL 的记录，即此时走索引全扫描的结果就不准了!Oracle 显然不会允许这种事情发生。
进行全索引扫描时，查询出的数据都必须从索引中可以直接得到（注意全索引扫描只有在CBO模式下才有效）
通常用于最大值或最小值查询。

SELECT MAX(ACCOUNTID) FROM TAB2;

Oracle会对ACCOUNTID的索引进行全扫描，以找到最大值。

4）索引快速全扫描（Index Fast Full Scan）

索引快速全扫描扫描索引中的所有的数据块，类似于全表扫描，把索引当作表一样进行全扫描操作。与 INDEX FULL SCAN显著的区别是它不对查询出的数据进行排序（即数据不是以排序顺序被返回）。它也适用于所有类型的B 树索引(包括唯一性索引和非唯一性索引)。和索引全扫描一样，索引快速全扫描也需要扫描目标索引所有叶子块的所有索引行。用于覆盖索引查询。

SELECT /*+ INDEX_FFS(TAB2 IDX_TAB2_REGION_SEX_NAME)*/ SEX, NAME FROM TAB2 WHERE REGION = 312 ;

如果有覆盖索引，Oracle可能选择使用快速全扫描来提升查询效率。没有模拟出来，hint指定了一下

查询中所需的所有列都可以从索引中获取，无需访问表的数据块。使用覆盖索引查询可以显著减少I/O操作，因为只需从索引中读取数据，而且不需要进行额外的回表访问。
扩展一：索引快速全扫描与索引全扫描的区别？

5）索引跳跃扫描（Index Skip Scan）

索引跳跃扫描用于复合索引部分列的查询，当查询不完全使用索引的前缀索引列时，Oracle可能会选择此路径。这与Mysql的索引失效之最左前缀法则不一样，Mysql如果索引了多列（联合索引）如果最前面的列不存在或者跳跃某一列，索引将会部分失效(后面的字段索引失效)。
有复合索引 (REGION, SEX, NAME)

SELECT /*+ INDEX_ss(TAB2 IDX_TAB2_REGION_SEX_NAME)*/ SEX, NAME FROM TAB2 WHERE REGION = 312 ;

SELECT * FROM TAB2 WHERE NAME = 'TEST10';

2.4 分区扫描（Partition Range Scan） todo

当表是分区表时，Oracle会根据查询条件选择性扫描符合条件的分区，而不是整个表的数据，这种方式称为分区扫描。分区扫描极大地提高了大表查询的效率。

SELECT * FROM TAB2 
WHERE REGION = 312;

在这个查询中，Oracle只会扫描符合条件的分区，而不是整个表，显著提升了查询性能。

访问方式还有很多，比如LOAD AS SELECT，以追加(APPEND)模式向表中插人数据、TABLE ACCESS(SAMPLE)、INDEX(SKIP SCAN DESCENDING)以及位图索引BITMAP INDEX(RANGE SCAN)等，可以通过阅读官方文档或者《Oracle 高性能SQL引擎剖析：SQL优化与调优机制详解》一书了解。

3. 选择访问路径的影响因素

Oracle优化器在选择访问路径时，会考虑以下因素：

表和索引的统计信息：包括数据量、分布情况等。
查询条件：WHERE子句中的条件会直接影响路径选择。
表的大小：小表通常适合全表扫描，而大表则倾向于索引扫描。
查询模式：例如，是否涉及聚合函数、排序操作等。
系统资源：包括CPU、内存和I/O的当前利用率。

4. 如何优化访问路径

创建适当的索引：确保为常用的查询条件列创建索引，特别是用于连接、过滤和排序的列。

CREATE INDEX IDX_ACCOUNTID ON TAB2(ACCOUNTID);

定期更新统计信息：确保表和索引的统计信息是最新的，以便优化器做出最优决策。

EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'TAB2');

使用分区：对于大表，合理的分区策略可以显著提高查询性能，尤其是对于时间序列数据。
避免不必要的全表扫描：对于大表，避免无索引的全表扫描，除非确实需要读取大部分数据。

5. 总结

理解和优化SQL访问路径是提高Oracle数据库性能的关键。通过选择合适的索引类型、创建合理的分区结构，并定期维护统计信息，可以有效减少SQL查询的执行时间，提高数据库系统的整体效率。不同的访问路径各有优劣，最佳路径的选择依赖于具体的查询需求和数据特性。
SQL访问路径的选择和优化并不是一劳永逸的过程，持续的监控和调整是保持数据库性能的最佳实践。
希望本文对您理解Oracle SQL访问路径有所帮助。如有疑问或进一步探讨，欢迎在评论区交流。

扩展一：索引快速全扫描与索引全扫描的区别？

索引快速全扫描只适用于CBO
索引快速全扫描可以使用多块读，也可以并行执行。
索引快速全扫描的执行结果不一定是有序的。这是因为索引快速全扫描时Oracle 是根据索引行在磁盘上的物理存储顺序来扫描，而不是根据索引行的逻辑顺序来扫描的，所以扫描结果才不一定有序(对于单个索引叶子块中的索引行而言，其物理存储顺序和逻辑存储顺序一致:但对于物理存储位置相邻的索引叶子块而言，块与块之间索引行的物理存储顺序则不一定在逻辑上有序)。

oracle oracle执行计划墨力计划 sql语句访问路径

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

DataBase（共14篇）

DataBase合集

一文带你搞懂 Oracle SQL访问路径方法 理论+案例