本文共 2853 字，大约阅读时间需要 9 分钟。

Oracle 访问表的方式及优化技巧

在 Oracle 数据库中，用户可以通过三种方式访问表中的记录：全表扫描、通过 ROWID 访问表以及索引扫描。每种方式都有其特定的适用场景和性能表现。本文将详细介绍这三种访问方式，并提供实际案例分析。

1. 全表扫描（Full Table Scans, FTS）

全表扫描是 Oracle 最基础的表访问方式。通过这种方式，Oracle 会逐行访问表中的所有记录，并检查每条记录是否满足 WHERE 子句的限制条件。这种方法的核心原理是 Oracle 通过多次读取数据块（database block），以减少 I/O 操作次数，从而提高系统吞吐量。

全表扫描的优化建议

• 避免在大表上使用全表扫描：只有在需要读取大量数据（超过表总数据量的 5%~10%）或需要使用并行查询时，才建议使用全表扫描。
• 利用多块读优化：全表扫描时，Oracle 会采用多块读的方式读取数据块，确保每个数据块只被读取一次，从而显著降低 I/O 操作次数。

全表扫描的实例

以下是通过全表扫描查询的示例：

SELECT * FROM USERINFO WHERE NO = 5000;

此时，执行计划显示查询采用了 TABLE ACCESS FULL 模式，表访问方式为全表扫描。由于该查询需要读取所有数据块，consistent gets 的数量会显著增加。

2. 通过 ROWID 访问表（Table Access by ROWID）

ROWID 是 Oracle 记录行的唯一标识符，包含了该行所在的数据文件、数据块以及行在数据块中的位置信息。通过 ROWID 访问表可以快速定位目标数据，是 Oracle 存取单行数据的最快方法。具体操作方式如下：

2.1 单个 ROWID 的情形

以下是通过单个 ROWID 查询单行数据的示例：

SELECT empno, ename FROM emp WHERE rowid = 'AAAX7bAAEAAAo1VAAM';

执行计划显示查询采用了 TABLE ACCESS BY USER ROWID 模式，表访问方式为通过 ROWID 定位行数据。由于 ROWID 已知且唯一，consistent gets 的数量仅为 1。

2.2 多个 ROWID 的查询

以下是通过多个 ROWID 查找多行数据的示例：

SELECT empno, ename FROM emp WHERE rowid IN ('AAAX7bAAEAAAo1VAAA', 'AAAX7bAAEAAAo1VAAD');

此时，执行计划显示查询采用了 INLIST ITERATOR 操作，结合 TABLE ACCESS BY USER ROWID 模式完成查找。每个 ROWID 需要进行一次 consistent get 操作。

3. 索引扫描（Index Scan）

索引扫描是一种通过索引快速定位目标数据的优化方式。索引不仅存储了查询字段的值，还记录了对应的 ROWID。通过索引可以快速定位数据块，进而从表中读取具体的行数据。索引扫描分为以下几种类型：

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

索引范围扫描适用于 WHERE 子句使用范围操作符（如 >, <, >=, <=, BETWEEN 等）的情况。以下是索引范围扫描的示例：

CREATE INDEX IX_USERINFO_NO ON USERINFO (NO);
SELECT * FROM USERINFO WHERE NO BETWEEN 1000 AND 1005;

执行计划显示查询采用了 INDEX RANGE SCAN 模式，通过索引快速定位目标数据范围。

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

索引唯一扫描适用于 WHERE 子句使用精确匹配操作符（如 =）的情况，且索引列具有唯一性约束。以下是索引唯一扫描的示例：

ALTER TABLE USERINFO ADD CONSTRAINT PK_USERINFO_NO PRIMARY KEY (NO);
SELECT * FROM USERINFO WHERE NO = 5000;

此时，执行计划显示查询采用了 INDEX UNIQUE SCAN 模式，通过唯一索引快速定位目标行。

3.3 索引全扫描（Index Full Scan）

索引全扫描是一种特殊的索引扫描方式，适用于以下情况：

• 索引包含查询所需的所有列（组合索引）。
• 索引的主列（leading column）有非空约束。

这种方式通过索引本身读取数据块，而不是直接从表中读取数据。以下是索引全扫描的示例：

CREATE INDEX IX_OBJECT_ID ON test (object_id);
SELECT owner, object_name, object_type FROM test WHERE object_name = 'EMP';

执行计划显示查询采用了 INDEX FULL SCAN 模式，通过索引读取所有相关数据。

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

快全索引扫描是优化版的索引全扫描，适用于以下情况：

• 索引包含查询所需的所有列。
• 索引的主列有非空约束。

这种方式可以通过多块读优化整体索引数据，性能远高于普通索引全扫描。以下是快全索引扫描的示例：

CREATE INDEX IX2 ON test (owner, object_name, object_type);
SELECT owner, object_name, object_type FROM test WHERE object_name = 'EMP';

执行计划显示查询采用了 INDEX FAST FULL SCAN 模式，通过索引快速读取所有相关数据。

4. 索引跳跃式扫描（Index Skip Scan）

索引跳跃式扫描是一种优化组合索引的方式，适用于以下情况：

• 查询不涉及索引的主列（leading column）。
• 索引的主列具有少量唯一值。

这种方式通过跳过索引的主列，直接读取索引的非主列部分，从而降低查询时间。以下是索引跳跃式扫描的示例：

CREATE INDEX IX3 ON test (owner, object_name, object_type);
SELECT object_name, object_type FROM test WHERE owner = 'SCOTT';

总结

通过以上三种方式，Oracle 提供了灵活的表访问策略。选择合适的访问方式对数据库性能有重要影响。全表扫描适合大数据量的单行读取，ROWID 访问适合已知行标识的快速定位，索引扫描则通过快速定位行数据，显著提升查询效率。在实际应用中，应根据具体需求和数据分布，合理选择最优的表访问方式。

转载地址：http://erpfk.baihongyu.com/