数据库基础体系 · 第 29/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

Oracle 索引与优化器：统计信息、执行计划、Hint 和 SQL 调优

索引、统计信息、执行计划和 Hint 解决的是同一个问题的不同层面：

对一条 SQL，Oracle 如何估算不同执行方式的代价，选择访问路径和连接顺序，并在数据分布、参数值和系统负载变化后维持可接受的性能。

索引不是“加上就一定更快”，Hint 也不是“写上就一定生效”。SQL 调优的核心，是让优化器获得足够准确的信息，并验证它实际执行的计划是否适合当前数据和业务边界。

本文以 Oracle Database 19c/21c/23c 公开语义为基础。具体的成本模型、默认参数和优化器行为可能随版本、初始化参数、补丁集及统计信息变化，示例中的执行计划形状用于说明机制，不应被当作所有环境中的固定输出。

---

一、优化器到底在选择什么

1. 解析、优化和执行

Oracle 执行一条 SQL，大致经历以下阶段：

1. 语法解析：检查 SQL 是否符合语法。
2. 语义解析：解析对象、列、权限、数据类型。
3. 共享游标查找：尝试复用已经存在的 SQL 游标。
4. 优化：生成候选执行计划，估算各计划的代价。
5. 执行：按照选定的行源树访问数据、连接表并返回结果。

优化器主要负责第 4 步。它通常称为 CBO（Cost-Based Optimizer，基于成本的优化器）。

优化器并不是直接测量每个候选计划的真实运行时间，而是使用：

• 表和索引统计信息；
• 谓词的选择率；
• 列数据分布；
• 系统统计信息；
• 访问路径和连接算法的成本模型；
• SQL 的语义约束与查询转换规则。

因此，优化器的选择本质上是：

$$P^* = \arg\min_{P \in \mathcal{P}} Cost(P)$$

其中：

• $P$ 是一个候选执行计划；
• $\mathcal{P}$ 是优化器生成的候选计划集合；
• $Cost(P)$ 是根据统计信息和成本模型估算出的成本；
• $P^*$ 是最终选择的计划。

这里的成本不是 SQL 的毫秒数，也不是一个跨环境可比较的性能分数。它主要用于同一数据库环境、同一优化过程中的计划比较。

---

二、索引的结构和访问语义

1. B-tree 索引

Oracle 最常用的是 B-tree 索引。它把索引键组织成平衡树，叶子节点保存键值以及对应表行的位置。

对于普通表上的索引，叶子节点通常包含：

• 索引列值；
• ROWID，指向堆表中的行。

例如：

CREATE INDEX ix_orders_customer_id
    ON orders(customer_id);

查询：

SELECT *
FROM orders
WHERE customer_id = 1001;

可能执行：

1. 从根节点沿索引树查找 customer_id = 1001；
2. 找到一个或多个叶子节点；
3. 读取叶子节点中的 ROWID；
4. 根据 ROWID 访问表块；
5. 返回表行。

这通常称为：

INDEX RANGE SCAN
TABLE ACCESS BY INDEX ROWID

但“有索引”并不意味着一定使用索引。优化器还要判断：

• 预计返回多少行；
• 返回表行需要访问多少个数据块；
• 索引访问是否会产生大量随机 I/O；
• 全表扫描是否更便宜；
• 查询是否只需要索引中的列。

2. 索引只读查询与覆盖访问

如果查询所需的列全部存在于索引中，Oracle 可能只扫描索引，而不访问表：

CREATE INDEX ix_orders_customer_status
    ON orders(customer_id, status);

SELECT customer_id, status
FROM orders
WHERE customer_id = 1001;

可能使用：

INDEX RANGE SCAN

这类查询有时被称为“覆盖索引”访问，但这不是 Oracle 中必须使用的专门对象类型，而是指执行计划不需要回表。

如果查询是：

SELECT order_id, customer_id, status, amount
FROM orders
WHERE customer_id = 1001;

而 amount 不在索引中，就可能需要通过 ROWID 回表。

3. 全表扫描可能是正确选择

假设一张表有 100 万行：

SELECT *
FROM orders
WHERE status = 'PAID';

如果 PAID 占 95%，使用索引可能需要：

1. 扫描索引中大量叶子项；
2. 根据大量 ROWID 访问许多表块；
3. 返回几乎整张表的数据。

此时全表扫描可能更便宜，甚至更适合多块顺序读和并行执行。

所以以下推理是不成立的：

查询条件使用了索引列
=> 必须走索引
=> 一定更快

真正相关的是谓词选择率、聚簇因子、返回列数量和访问的数据块范围。

---

三、常见索引类型及其边界

1. 单列索引

CREATE INDEX ix_orders_order_date
    ON orders(order_date);

适用于选择性较高的日期范围、等值条件等，但仍然需要由优化器判断是否值得使用。

2. 复合索引

CREATE INDEX ix_orders_customer_date
    ON orders(customer_id, order_date);

它的列顺序很重要。这个索引天然适合：

WHERE customer_id = :customer_id

以及：

WHERE customer_id = :customer_id
  AND order_date >= :start_date
  AND order_date <  :end_date

因为索引按 (customer_id, order_date) 排序，同一个客户的日期范围可以集中定位。

对于以下查询：

WHERE order_date >= :start_date
  AND order_date <  :end_date

该索引不能像以 order_date 开头的索引那样直接进行高效的前缀范围定位。Oracle 可能考虑全表扫描、索引快速全扫描，或者在特定条件下使用 index skip scan，但 skip scan 不是复合索引列顺序错误后的通用补救方案。

一个常见的经验规则是：

• 等值列通常放在范围列之前；
• 经常作为独立过滤条件的列，需要单独评估；
• 不能只根据“区分度高低”机械决定顺序；
• 还要考虑排序、连接、覆盖列、数据分布和实际 SQL。

3. 函数索引

如果条件对列使用了函数：

SELECT *
FROM orders
WHERE TRUNC(order_date) = DATE '2025-01-01';

普通的 order_date 索引不一定能直接支持这个表达式。可以改写为半开区间：

SELECT *
FROM orders
WHERE order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-01-02';

这种写法通常更容易使用普通索引，也避免了对每一行执行 TRUNC。

如果业务必须按表达式查询，可以建立函数索引：

CREATE INDEX ix_orders_trunc_date
    ON orders(TRUNC(order_date));

函数索引要求查询表达式与索引表达式在语义上匹配。函数依赖的环境设置、字符集、排序规则等也可能影响结果，应避免在索引表达式中使用不稳定或依赖会话环境的逻辑。

4. NULL 与 B-tree 索引

Oracle 的普通 B-tree 索引不会存储“所有索引列都为 NULL”的索引项。

因此：

CREATE INDEX ix_orders_cancelled_at
    ON orders(cancelled_at);

对以下查询：

WHERE cancelled_at IS NULL

普通单列 B-tree 索引通常没有可供定位的 NULL 索引项。

对于复合索引：

CREATE INDEX ix_orders_status_cancelled
    ON orders(status, cancelled_at);

如果 status 非 NULL，即使 cancelled_at 为 NULL，这一行仍然可以有索引项，因为不是所有索引列都为 NULL。

这也是理解“NULL 是否出现在索引中”的关键：不是简单地说“索引不存 NULL”，而是要看整条索引键是否全为空。

5. Bitmap 索引

Bitmap 索引适合：

• 数据仓库；
• 低基数列；
• 读多写少；
• 批量装载和分析查询。

例如性别、状态、区域编码等列可能只有少量不同值。

但 Bitmap 索引不适合高并发 OLTP 中频繁更新的表。更新一行可能修改覆盖多行的位图结构，从而增加并发事务之间的冲突风险。它的适用性还涉及事务模型、装载方式和并发写入模式，不能仅以“列的不同值少”作为依据。

6. 分区索引

分区表可能使用：

• Local index：索引分区与表分区对应；
• Global index：索引跨越多个表分区。

查询若能根据分区键排除不相关分区，就可能发生 partition pruning（分区裁剪）。这往往比单纯增加索引更重要。

例如：

WHERE order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-02-01'

如果表按 order_date 分区，优化器可能只访问相关分区。若对分区键施加不利于识别范围的函数，也可能削弱分区裁剪。

---

四、统计信息：优化器如何知道数据的大致样子

1. 统计信息是什么

优化器需要估算：

• 表有多少行；
• 表占多少数据块；
• 列有多少不同值；
• 列是否存在 NULL；
• 索引有多少叶子块；
• 索引聚簇因子；
• 数据是否存在明显倾斜；
• 多列之间是否存在相关性。

常见统计信息包括：

• NUM_ROWS：估算行数；
• BLOCKS：表占用的数据块数量；
• NUM_DISTINCT：列的不同值数量；
• DENSITY：选择率估算相关信息；
• NUM_NULLS：NULL 数量；
• LOW_VALUE、HIGH_VALUE：列值范围；
• CLUSTERING_FACTOR：索引顺序与表行物理顺序的相关程度；
• Histogram：直方图，用于描述列值分布。

统计信息是优化器的模型输入，不是事务数据本身，也不是实时精确的行计数。

2. 选择率与基数

设表中总行数为 $N$，谓词满足的行数估计为 $R$，则：

$$Selectivity = \frac{R}{N}$$

优化器通常先估算选择率，再得到行源基数：

$$Cardinality = N \times Selectivity$$

例如：

• 表有 $1{,}000{,}000$ 行；
• customer_id = 1001 预计匹配 100 行；
• 选择率为 $100 / 1{,}000{,}000 = 0.0001$；
• 该谓词的基数估算为 100。

基数会沿执行计划向上传递。例如两个表连接时，连接输入行数会影响：

• Nested Loops 是否合适；
• Hash Join 是否合适；
• 连接顺序；
• 是否需要排序；
• 是否应该先聚合或过滤。

因此，很多“索引没有被使用”的问题，根因并不是索引不存在，而是优化器错误估计了基数。

3. 多谓词不能简单相乘

如果有：

WHERE city = 'Beijing'
  AND province = 'Beijing'

最简单的估算可能假定两个列相互独立：

$$P(city='Beijing' \land province='Beijing') \approx P(city='Beijing') \times P(province='Beijing')$$

但现实中两个列高度相关。独立性假设会低估或高估结果行数。

这时可以考虑扩展统计信息，例如列组统计信息：

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(
         ownname   => USER,
         tabname   => 'CUSTOMERS',
         extension => '(city, province)'
       )
FROM dual;

之后重新收集表统计信息：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'CUSTOMERS',
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    no_invalidate    => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

这里的 extension 表达式应使用真实列名。扩展统计信息并不会改变表数据，也不会自动创建一个供查询访问的普通索引；它主要改善多列谓词的估算。

4. 直方图与数据倾斜

假设 status 只有两个值：

• PAID 占 99%；
• CANCELLED 占 1%。

如果没有足够的分布信息，优化器可能只能使用平均密度近似估算：

每个状态值大约占 50%

这对两个值差异极大的数据集显然不准确。

直方图帮助优化器区分不同值的频率。收集统计信息时常见写法是：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname       => USER,
    tabname       => 'ORDERS',
    method_opt    => 'FOR ALL COLUMNS SIZE AUTO',
    cascade       => TRUE,
    no_invalidate => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

SIZE AUTO 允许 Oracle 根据使用情况和数据特征决定是否建立直方图及其桶数。直方图不是越多越好：

• 增加统计信息维护成本；
• 统计信息变大；
• 对变化频繁的数据仍可能过时；
• 不能替代对 SQL 和数据分布的理解。

5. 聚簇因子

CLUSTERING_FACTOR 描述索引键顺序与表中行物理分布的关系。

假设表中的行按 customer_id 聚集存储：

customer_id = 1001 的行集中在少量数据块中

通过 customer_id 索引找到这些行时，回表可能只访问少量数据块。

如果同一个客户的行分散在大量数据块中，即使索引只找到少量 ROWID，回表也可能产生大量随机块访问。此时索引成本会更高。

因此，下面两张表即使有相同的行数、相同的索引和相同的谓词，也可能有不同的最佳执行计划，因为聚簇因子不同。

---

五、收集和验证统计信息

1. 查看统计信息

常用数据字典视图包括：

SELECT table_name,
       num_rows,
       blocks,
       last_analyzed,
       stale_stats
FROM user_tab_statistics
WHERE table_name = 'ORDERS';

查看列统计信息：

SELECT column_name,
       num_distinct,
       num_nulls,
       histogram,
       density,
       last_analyzed
FROM user_tab_col_statistics
WHERE table_name = 'ORDERS';

查看索引统计信息：

SELECT index_name,
       blevel,
       leaf_blocks,
       distinct_keys,
       clustering_factor,
       num_rows,
       last_analyzed
FROM user_indexes
WHERE table_name = 'ORDERS';

这些视图中的统计值是收集时的估算或记录，不是每次查询实时计算的结果。

2. 收集表和索引统计信息

一个明确的示例：

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => USER,
    tabname          => 'ORDERS',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    cascade          => TRUE,
    no_invalidate    => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

参数含义：

• estimate_percent：统计信息采样比例；AUTO_SAMPLE_SIZE 由 Oracle 选择；
• method_opt：列统计信息和直方图策略；
• cascade => TRUE：同时处理相关索引统计信息；
• no_invalidate：统计信息更新后，已有游标如何失效。AUTO_INVALIDATE 由 Oracle 管理。

生产系统中不应无条件地在业务高峰手动收集所有表的统计信息。统计信息改变可能使新解析的 SQL 选择不同计划。收集后应检查重要 SQL 的实际计划和性能。

3. 统计信息过期不等于一定错误

Oracle 可以根据表变化判断统计信息是否可能过期，但“stale”只是提示统计信息与当前数据变化之间存在一定距离。

以下情况都可能导致估算问题：

• 数据量发生重大变化，但统计信息尚未更新；
• 数据分布变化，行数变化却不明显；
• 重要列的值分布发生倾斜；
• 列之间相关性没有被建模；
• 使用绑定变量时，不同参数值对应完全不同的选择率；
• 分区级和全局统计信息不协调。

统计信息更新应结合实际执行计划验证，而不是只看 LAST_ANALYZED。

---

六、执行计划：计划树如何表示数据流

1. 访问路径、行源和连接算法

执行计划是一个行源树。每个操作从子节点取得行，处理后把行传给父节点。

常见操作包括：

• TABLE ACCESS FULL：全表扫描；
• INDEX UNIQUE SCAN：唯一索引等值定位；
• INDEX RANGE SCAN：索引范围扫描；
• INDEX FULL SCAN：按索引结构顺序读取；
• INDEX FAST FULL SCAN：类似读取索引段的全部块，但不保证索引顺序；
• TABLE ACCESS BY INDEX ROWID：通过 ROWID 回表；
• NESTED LOOPS：嵌套循环连接；
• HASH JOIN：哈希连接；
• MERGE JOIN：排序合并连接；
• SORT ORDER BY：排序；
• HASH GROUP BY：哈希聚合；
• FILTER：过滤条件。

例如：

SELECT STATEMENT
  HASH JOIN
    TABLE ACCESS FULL CUSTOMERS
    TABLE ACCESS FULL ORDERS

表示两个表被扫描后进行哈希连接。它并不自动代表性能差。如果两个表需要返回大量行，全表扫描和 Hash Join 可能正是合适的选择。

2. 执行顺序不能只看缩进

计划的父子关系和 STARTS、A-Rows 等运行时统计比单纯看文本缩进更重要。

例如：

NESTED LOOPS
  TABLE ACCESS BY INDEX ROWID ORDERS
    INDEX RANGE SCAN IX_ORDERS_CUSTOMER
  TABLE ACCESS BY INDEX ROWID ORDER_ITEMS
    INDEX RANGE SCAN IX_ITEMS_ORDER_ID

嵌套循环的逻辑是：

1. 从外层行源取得一行；
2. 根据这行的连接键访问内层；
3. 返回匹配行；
4. 重复执行内层访问。

如果外层实际返回 100 万行，内层索引访问也可能被启动 100 万次。若优化器错误地认为外层只有几十行，Nested Loops 就可能成为灾难性的选择。

3. 估算行数和实际行数

执行计划中常见两组重要信息：

• E-Rows：Estimated Rows，估算行数；
• A-Rows：Actual Rows，实际返回行数。

如果某节点出现：

E-Rows = 10
A-Rows = 500000

说明该节点存在严重基数估算偏差。后续连接顺序和连接算法可能因此全部失真。

但不能只比较最终结果行数。应从计划树底部开始，定位第一个明显偏差的节点，因为上游误差往往是下游错误的根源。

---

七、正确获取执行计划

1. EXPLAIN PLAN 的局限

可以使用：

EXPLAIN PLAN FOR
SELECT order_id, amount
FROM orders
WHERE customer_id = 1001;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    NULL,
    NULL,
    'BASIC +PREDICATE +ALIAS +NOTE'
  )
);

它展示的是优化器为这次 EXPLAIN PLAN 生成的计划，但不一定等于应用真正执行的游标计划。原因包括：

• 应用使用了绑定变量，而示例使用了字面量；
• 会话参数不同；
• 优化器环境不同；
• SQL 经过了不同的解析路径；
• 实际执行发生了自适应变化；
• EXPLAIN PLAN 没有提供真实运行时行数。

因此，EXPLAIN PLAN 适合快速观察计划形状，不适合作为唯一的生产诊断依据。

2. 查看实际游标计划

可以在测试环境使用 gather_plan_statistics：

SELECT /*+ gather_plan_statistics */
       order_id, amount
FROM orders
WHERE customer_id = 1001;

执行完成后查看最后一个游标：

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    NULL,
    NULL,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

ALLSTATS LAST 可以显示最近一次执行的运行时统计，常见字段包括：

• Starts：该行源被启动次数；
• E-Rows：估算行数；
• A-Rows：实际行数；
• A-Time：累计耗时；
• Buffers：逻辑读等信息。

如果通过应用执行，应根据 SQL_ID 和 CHILD_NUMBER 精确查看：

SELECT sql_id,
       child_number,
       executions,
       buffer_gets,
       disk_reads,
       rows_processed,
       elapsed_time
FROM v$sql
WHERE sql_text LIKE 'SELECT%FROM orders%';

然后：

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    '具体的SQL_ID',
    0,
    'ALLSTATS LAST +PEEKED_BINDS +PREDICATE +NOTE'
  )
);

示例中的 SQL_ID 需要从实际环境查询，不能人为猜测。

查看这些视图通常需要相应的数据字典权限。gather_plan_statistics 也会增加统计收集开销，不应在所有高频生产 SQL 上长期无条件使用。

3. 计划中的谓词位置

计划输出中的谓词通常分为：

• access predicate：用于定位索引或连接访问范围；
• filter predicate：数据取得后再过滤。

例如索引为：

CREATE INDEX ix_orders_customer_date
    ON orders(customer_id, order_date);

查询：

WHERE customer_id = :id
  AND order_date >= :begin_date
  AND order_date <  :end_date

理想情况下，两个条件都可能参与索引访问范围。

而如果执行计划显示只有 customer_id 是 access predicate，日期条件是 filter predicate，说明日期条件可能没有进一步缩小索引扫描范围，原因可能是表达式、隐式转换、列顺序或优化器判断。

---

八、为什么同一条 SQL 会出现不同计划

1. 字面量和绑定变量

字面量 SQL：

WHERE status = 'CANCELLED'

和绑定变量 SQL：

WHERE status = :status

在共享游标和选择率估算方面可能有不同表现。

如果列值分布严重倾斜：

CANCELLED：1%
PAID：99%

那么这两个参数值可能需要不同的访问路径：

• CANCELLED：索引范围扫描可能合适；
• PAID：全表扫描可能更合适。

Oracle 会结合绑定变量窥视、统计信息以及相关自适应机制处理这种问题。在某些场景下，同一 SQL 可能产生多个 child cursor，这就是 Adaptive Cursor Sharing 等机制试图解决的问题之一。但它并不保证所有参数分布都自动得到理想计划。

诊断时应同时观察：

• 是否使用绑定变量；
• PEEKED_BINDS 中的值；
• 不同 child cursor 的计划；
• 不同参数值的实际行数；
• V$SQL 中的执行次数、逻辑读和耗时。

2. 隐式数据类型转换

假设：

CREATE TABLE users (
  user_id NUMBER,
  user_name VARCHAR2(100)
);

CREATE INDEX ix_users_user_id ON users(user_id);

正确查询：

WHERE user_id = :user_id_number

如果应用把参数作为字符串传入：

WHERE user_id = :user_id_string

Oracle 可能需要进行隐式转换。具体转换方向与数据类型、表达式形式和环境有关，不能简单断言“必然对列做函数”，但隐式转换可能：

• 增加转换成本；
• 造成运行时转换错误；
• 改变谓词的可用性；
• 使估算与预期不一致。

应用层应让绑定变量类型与列类型一致，而不是依赖隐式转换。

3. 统计信息变化

统计信息收集后，新解析的 SQL 可能选择新的执行计划。旧游标是否立即失效，还与游标失效策略和数据库行为有关。

计划变化不一定是回归：

• 原数据分布确实改变了；
• 新计划实际逻辑读更少；
• 旧计划只是偶然在缓存中表现较好。

判断计划回归必须比较实际运行指标，而不是只比较计划文本。

---

九、Hint：给优化器的约束和偏好

1. Hint 的基本写法

Hint 写在关键字后面的注释中：

SELECT /*+ FULL(o) */
       o.order_id, o.amount
FROM orders o
WHERE o.customer_id = :id;

这里要求：

• FULL 是 Hint 名称；
• o 是表别名；
• Hint 注释必须位于正确的语句块位置；
• 表别名必须与 SQL 中的别名一致。

如果 SQL 使用：

FROM orders o

却写：

/*+ FULL(orders) */

可能无法作用于该表，因为 Hint 目标名称通常应使用语句中的别名。

2. 常见 Hint

索引访问：

SELECT /*+ INDEX(o ix_orders_customer_date) */
       ...
FROM orders o
WHERE ...

全表扫描：

SELECT /*+ FULL(o) */
       ...
FROM orders o;

连接顺序：

SELECT /*+ LEADING(c o) */
       ...
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id;

连接方法：

SELECT /*+ USE_NL(o) */
       ...
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id;

或：

SELECT /*+ USE_HASH(o) */
       ...
FROM customers c
JOIN orders o
  ON o.customer_id = c.customer_id;

并行提示：

SELECT /*+ PARALLEL(o, 4) */
       ...
FROM orders o;

这些 Hint 表达的是优化方向，但最终能否生效还取决于：

• Hint 是否拼写正确；
• 对象和别名是否匹配；
• Hint 是否位于正确的查询块；
• 指定的索引、连接方式是否可行；
• 查询转换后目标是否仍存在；
• 其他 Hint 是否冲突；
• 资源和并行策略是否允许。

3. Hint 不等于强制执行

以下写法不能证明 Hint 已生效：

SELECT /*+ INDEX(o ix_orders_customer_date) */
...

即使注释写法正确，优化器也可能因为索引不可用、谓词不支持、对象名称不匹配或其他语义限制而不采用它。

应通过实际游标计划验证：

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    NULL,
    NULL,
    'ALLSTATS LAST +OUTLINE +NOTE'
  )
);

在支持相应计划显示选项的版本和环境中，还可以查看 Hint 使用或未使用的报告信息。具体显示项应以当前版本 DBMS_XPLAN 文档为准。

4. Hint 的生产风险

Hint 把某个时期、某组数据、某个环境下的判断写进了 SQL。数据规模或分布改变后，原先合理的 Hint 可能变成限制：

过去：返回 10 行，INDEX RANGE SCAN 合适
现在：返回 500 万行，Hint 仍强制索引
结果：大量随机回表，性能恶化

因此 Hint 更适合作为：

• 诊断实验；
• 已确认优化器估算存在问题时的短期稳定措施；
• 经过版本、数据规模和参数范围验证的计划控制手段。

不应把 Hint 当作替代统计信息、SQL 改写或数据模型修复的常规方法。

---

十、SQL 调优的核心：让条件具有可估算、可访问性

1. 避免对索引列做不必要的函数

不利写法：

WHERE TRUNC(order_date) = :day

更适合范围索引的写法：

WHERE order_date >= :day
  AND order_date <  :day + INTERVAL '1' DAY

不利写法：

WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = :day_text

改写时还要注意 :day 的数据类型。若绑定变量是字符串，仍可能发生隐式转换。日期参数应以日期类型绑定。

2. 避免把列包在通配式表达式中

例如：

WHERE NVL(status, 'UNKNOWN') = :status

这可能使普通 status 索引难以直接用于访问。可根据业务语义改写为：

WHERE status = :status
   OR (status IS NULL AND :status = 'UNKNOWN')

但 OR 又可能改变计划。若查询模式稳定且表达式确有必要，可以评估函数索引：

CREATE INDEX ix_orders_status_nvl
    ON orders(NVL(status, 'UNKNOWN'));

这里不存在一个对所有数据分布都更好的写法，必须通过实际计划和运行指标验证。

3. 只返回需要的列

SELECT *
FROM orders
WHERE customer_id = :id;

会增加：

• 网络传输；
• 表访问；
• 应用对象构造；
• 覆盖索引失效的可能性。

应明确列清单：

SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = :id;

这不是单纯的代码风格问题，而是直接影响行源宽度、I/O、排序和临时空间。

4. 分页不能只看返回行数

以下写法：

SELECT *
FROM orders
ORDER BY order_date DESC
OFFSET :offset ROWS
FETCH NEXT :page_size ROWS ONLY;

当 :offset 很大时，数据库仍可能需要找到并跳过大量前置行。分页性能还取决于：

• 是否有稳定且唯一的排序键；
• 排序能否由索引支持；
• 深分页的访问范围；
• 数据在分页期间是否变化。

键集分页通常更适合连续翻页：

SELECT order_id, order_date, amount
FROM orders
WHERE (order_date, order_id) < (:last_date, :last_order_id)
ORDER BY order_date DESC, order_id DESC
FETCH FIRST :page_size ROWS ONLY;

该写法要求排序方向、比较语义和索引设计一致，并且 (order_date, order_id) 能提供稳定顺序。若 order_date 非唯一，必须加入唯一的次排序键，否则分页可能重复或遗漏行。

---

十一、连接算法与连接顺序

1. Nested Loops

Nested Loops 适合外层结果较少、内层可通过索引快速定位的场景：

外层行数很少
×
每行内层访问成本很低

近似直觉成本为：

$$Cost \approx Cost(Outer) + Starts(Inner) \times Cost(Inner)$$

其中 Starts(Inner) 通常接近外层实际输出行数。

如果优化器把外层估算为 10 行，但实际是 100 万行，Nested Loops 可能重复执行内层访问 100 万次。

2. Hash Join

Hash Join 通常适合：

• 大表之间的等值连接；
• 没有合适的内层索引；
• 需要处理较多行。

基本过程是：

1. 选择一个输入构建 Hash Table；
2. 读取另一个输入；
3. 对连接键计算哈希并查找匹配项。

构建输入过大时可能使用 PGA 之外的临时空间，内存压力和临时 I/O 会影响性能。

3. Merge Join

Merge Join 要求两个输入按连接键有序，可能需要先排序。如果输入本身已按需要的顺序提供，或者排序成本可接受，它可能是合适的选择。

不能通过“Nested Loops 永远适合小表”“Hash Join 永远适合大表”这类口号替代实际基数和计划验证。

---

十二、一个完整的诊断示例

下面使用简化的订单表说明从数据、索引、统计信息到实际计划的流程。

1. 创建对象和索引

CREATE TABLE orders (
  order_id     NUMBER PRIMARY KEY,
  customer_id  NUMBER NOT NULL,
  order_date   DATE NOT NULL,
  status       VARCHAR2(20) NOT NULL,
  amount       NUMBER(12,2) NOT NULL
);

CREATE INDEX ix_orders_customer_date
    ON orders(customer_id, order_date);

CREATE INDEX ix_orders_status
    ON orders(status);

插入测试数据时，应让数据分布尽量接近真实业务。例如 status 如果在生产中严重倾斜，测试数据也应体现这种倾斜，否则测试得到的计划不具有代表性。

2. 收集统计信息

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname       => USER,
    tabname       => 'ORDERS',
    method_opt    => 'FOR ALL COLUMNS SIZE AUTO',
    cascade       => TRUE,
    no_invalidate => DBMS_STATS.AUTO_INVALIDATE
  );
END;
/

3. 查看估算计划

EXPLAIN PLAN FOR
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = 1001
  AND order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-02-01';

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY(
    NULL,
    NULL,
    'BASIC +PREDICATE +ALIAS +NOTE'
  )
);

可能看到类似的计划形状：

SELECT STATEMENT
  TABLE ACCESS BY INDEX ROWID ORDERS
    INDEX RANGE SCAN IX_ORDERS_CUSTOMER_DATE

其因果关系是：

1. customer_id 是复合索引的前导列；
2. Oracle 先定位指定客户的索引范围；
3. order_date 继续缩小该客户范围；
4. 通过 ROWID 读取 order_id、amount 等不在索引中的列。

如果实际查询返回该客户的大量订单，全表扫描仍可能比索引回表更便宜；计划是否合理必须看实际行数和逻辑读。

4. 查看真实运行统计

SELECT /*+ gather_plan_statistics */
       order_id, order_date, amount
FROM orders
WHERE customer_id = 1001
  AND order_date >= DATE '2025-01-01'
  AND order_date <  DATE '2025-02-01';

然后：

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    NULL,
    NULL,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

重点比较：

E-Rows 与 A-Rows
Starts
Buffers
A-Time

例如，如果索引节点显示：

E-Rows = 20
A-Rows = 200000

应优先检查：

• customer_id 的列统计信息是否过期；
• 日期范围是否与预期一致；
• 查询是否使用了绑定变量；
• 是否存在数据倾斜；
• 是否发生了隐式转换；
• 表和索引统计信息是否与当前数据规模匹配。

此时直接添加：

/*+ INDEX(orders ix_orders_customer_date) */

并不能修复估算错误。它最多把错误估算下的访问路径固定下来。

---

十三、失败表现与诊断路径

1. “明明有索引，却走全表扫描”

可能原因：

• 谓词选择率很低，返回大量行；
• 统计信息估算出返回大量行；
• 索引聚簇因子较差；
• 查询需要大量不在索引中的列；
• 条件对列使用函数；
• 发生隐式数据类型转换；
• 索引不可用或状态异常；
• 查询经过转换后不再适合该索引；
• 表很小，全表扫描本身更便宜。

诊断顺序应是：

1. 获取真实游标计划；
2. 查看谓词是 access 还是 filter；
3. 比较 E-Rows 和 A-Rows；
4. 查看逻辑读、物理读和实际耗时；
5. 检查统计信息和数据分布；
6. 再决定改写 SQL、调整索引或修正统计信息。

2. “Hint 写了却没有改变计划”

检查：

Hint 是否位于正确位置
表别名是否一致
索引名是否正确
目标查询块是否正确
Hint 是否拼写错误
指定的访问路径是否在语义上可行
是否存在冲突 Hint

还要区分：

Hint 被接受但计划看起来没有变化

和：

Hint 实际未被使用

后者需要查看计划中的 Hint 报告、Outline 或 NOTE 信息，具体能力以当前数据库版本和 DBMS_XPLAN 支持为准。

3. “执行计划一样，但性能变差”

计划文本相同，不表示所有运行条件相同。性能可能受到以下因素影响：

• A-Rows 变化；
• Buffer Cache 命中率变化；
• 并发会话增加；
• 锁等待或其他等待事件；
• PGA 不足导致 Hash Join 或排序落盘；
• I/O 子系统变化；
• 绑定变量值变化；
• 数据库服务或资源管理策略变化。

因此调优不能只保存一份计划文本，还应关联：

• 执行次数；
• elapsed time；
• CPU time；
• buffer gets；
• disk reads；
• rows processed；
• 等待事件；
• 参数值和执行时间窗口。

---

十四、索引与事务、Undo、Redo 的关系

索引优化不能脱离事务机制。

一次 INSERT、UPDATE 或 DELETE 通常不仅修改表块，也可能修改相关索引块。这些变化需要：

• 产生 Redo，用于恢复；
• 在事务未提交前保持事务可见性规则；
• 在并发访问时参与锁和一致性处理；
• 在需要时借助 Undo 支持读一致性和回滚。

因此，索引的代价不只体现在查询：

增加索引
=> 可能减少查询访问成本
=> 增加 DML 维护成本
=> 增加存储和统计信息维护成本
=> 增加 Redo、块修改和恢复处理负担

在高并发 OLTP 中，索引过多可能使写入显著变慢。删除索引也不是简单的优化动作，因为它可能影响：

• 其他 SQL；
• 唯一性约束的实现；
• 外键相关操作；
• 排序和连接；
• 计划稳定性。

Oracle 的读一致性要求查询看到某个一致性点的数据版本。当数据块在查询期间被修改时，数据库可能需要使用 Undo 重建查询所需的旧版本。索引访问并不能绕过读一致性，也不能保证“走索引就不会产生一致性读取”。

长事务还可能导致：

• Undo 保留压力；
• ORA-01555: snapshot too old 风险；
• DML 和索引块竞争；
• 查询耗时与计划成本脱节。

这说明 SQL 调优需要同时观察访问路径、事务持续时间、Undo、锁和 I/O，而不是只看索引名称。

---

十五、如何形成可重复的调优过程

一个可靠的调优过程应当保留以下边界：

第一步：明确业务目标

先定义需要改善的指标：

• 单次延迟；
• 吞吐量；
• CPU；
• 逻辑读；
• 物理读；
• 并发下的尾延迟；
• DML 影响。

“SQL 很慢”不足以指导调优。

第二步：获取真实 SQL

不要只复制应用日志中带占位符的 SQL，也不要只运行人工改写后的字面量版本。应确认：

• 实际 SQL 文本；
• 绑定变量类型和典型值；
• 执行次数；
• child cursor；
• 真实用户、服务和会话环境。

第三步：查看真实执行计划

优先使用实际游标计划和运行统计，而不是单独依赖 EXPLAIN PLAN。

重点寻找：

• 第一个严重的 E-Rows/A-Rows 偏差；
• 反复启动次数很高的内层行源；
• 大量 TABLE ACCESS BY INDEX ROWID；
• 排序或哈希操作落入临时空间；
• 谓词停留在 filter 而非 access；
• 未发生预期的分区裁剪；
• 不同参数值对应不同计划。

第四步：验证统计信息

检查：

• 表、索引和列统计信息的更新时间；
• 数据规模是否发生重大变化；
• 是否存在直方图；
• 多列是否存在相关性；
• 分区统计信息是否合理；
• 统计信息收集是否覆盖了真实数据。

第五步：先修正语义和估算，再考虑 Hint

优先评估：

• 数据类型是否一致；
• 是否存在不必要的函数；
• 日期范围是否使用半开区间；
• 是否返回了不需要的列；
• 连接条件是否完整；
• 过滤和聚合是否可以提前；
• 索引列顺序是否匹配访问模式；
• 是否应增加扩展统计信息。

只有在确认优化器仍无法稳定选择合理计划时，才评估 Hint、SQL Plan Management 等计划控制手段。计划控制涉及版本、许可和运维流程，不能在不了解环境的情况下直接套用。

第六步：用同样的负载重新验证

验证不能只运行一次：

• 使用多组代表性参数；
• 包含高频和低频值；
• 比较逻辑读、CPU、耗时和并发；
• 检查 DML 及其他 SQL 是否受影响；
• 检查统计信息重新收集后的行为；
• 检查数据库重启、游标重新解析后的计划。

---

十六、几个需要避免的错误结论

错误一：索引越多越好

索引增加查询访问选择，但也增加写入、空间、Redo 和维护成本。索引是否有价值，应由真实 SQL 访问模式和完整负载决定。

错误二：成本越低，实际耗时一定越短

成本是优化器模型中的比较值，不是实时耗时。统计信息错误、系统负载、缓存、并发等待和参数变化都可能使低成本计划实际更慢。

错误三：全表扫描就是坏计划

当查询返回大量数据、表较小或索引回表代价较高时，全表扫描可能是正确计划。

错误四：Hint 可以修复一切计划问题

Hint 不能修复错误的数据类型、错误的连接条件、过期统计信息、严重数据倾斜或不合理的分页方式。它还可能把临时判断永久化。

错误五：只看最终计划，不看估算过程

真正有价值的诊断通常从第一个基数偏差开始。最终的 Nested Loops、Hash Join 或全表扫描只是错误估算逐层传播后的结果。

索引提供了访问数据的可能性，统计信息帮助优化器估算数据规模，执行计划展示了优化器选择的数据流，Hint 则是在必要时对选择过程施加额外约束。SQL 调优不是简单地“找索引”或“强制计划”，而是将 SQL 语义、数据分布、统计信息、访问路径、连接算法、事务并发和实际运行结果放在同一个因果链中验证。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：Oracle 事务与 Undo：读一致性、SCN、锁、Redo 和恢复机制
• 下一篇：Oracle RMAN、Data Guard 与 RAC：备份恢复和高可用边界
• 延伸：Oracle SQL 与 PL/SQL：数据类型、包、过程、异常和批处理

官方资料

• Oracle Database Documentation
• Oracle Database Concepts

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。