Oracle SQL优化：30条核心Hint语法实战指南

优化方法选择与基础扫描

SQL优化通常有两种基本逻辑：基于代价的优化（CBO）和基于规则的优化（RBO）。正确选择优化目标是优化的第一步。

第1步：选择基于代价或规则的优化模式

目的：决定优化器是依据统计信息计算成本，还是依据历史规则路径执行。

• /*+ALL_ROWS*/：基于CBO，追求系统吞吐量最大，资源消耗最小化。
  示例：SELECT /*+ALL_ROWS*/ EMP_NO FROM BSEMPMS;
• /*+FIRST_ROWS*/：基于CBO，追求最快返回首行，适用于交互式查询。
  示例：SELECT /*+FIRST_ROWS*/ EMP_NO FROM BSEMPMS;
• /*+CHOOSE*/：智能混合模式。若有统计信息则走CBO，否则降级为RBO。
  示例：SELECT /*+CHOOSE*/ EMP_NO FROM BSEMPMS;
• /*+RULE*/：强制使用基于规则的优化方法。
  示例：SELECT /*+RULE*/ EMP_NO FROM BSEMPMS;

第2步：选择表的访问路径

目的：指定数据库读取表数据的具体方式，如全表扫描或特定索引扫描。

• /*+FULL(TABLE)*/：强制对表进行全局全表扫描。
  示例：SELECT /*+FULL(A)*/ EMP_NO FROM BSEMPMS A;
• /*+ROWID(TABLE)*/：明确按RowID访问，适用于已知物理位置的记录。
  示例：SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA';
• /*+CLUSTER(TABLE)*/：针对簇对象，采用簇扫描方式，仅对聚簇表有效。
• /*+INDEX(TABLE INDEX_NAME)*/：强制使用指定索引进行扫描。
  示例：SELECT /*+INDEX(BSEMPMS IDX_EMP) */ * FROM BSEMPMS;
• /*+INDEX_ASC(TABLE INDEX_NAME)*/：按索引升序扫描。
• /*+INDEX_DESC(TABLE INDEX_NAME)*/：按索引降序扫描，适用于倒序排列需求。
• /*+INDEX_FFS(TABLE INDEX_NAME)*/：快速全索引扫描（Fast Full Scan），比全表扫描更高效，用于获取所有列且索引覆盖所需字段时。

第3步：组合索引与位图优化

目的：在处理多条件过滤或大数据量分析时，利用复合索引策略。

• /*+INDEX_COMBINE(...)：为表选择位图索引访问路径。若未指定参数，优化器将自动选择位图索引的布尔组合方式。
  示例：SELECT /*+INDEX_COMBINE(SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS WHERE SAL<5000000;
• /*+INDEX_JOIN(TABLE INDEX_NAME)*/：指示优化器使用索引作为唯一访问路径，避免回表。
• /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2...*/：强制将几个单列索引的扫描结果进行合并（AND操作）。

复杂查询结构与控制

当查询涉及多个条件组合或视图连接时，Hint可以改变查询的重写和执行顺序。

第4步：优化OR条件与视图处理

目的：解决OR条件导致的效率低下，以及优化物化视图的连接方式。

• /*+USE_CONCAT*/：将WHERE子句中的OR条件转换为UNION ALL的组合查询，从而利用索引。
  示例：SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' OR SEX='M';
• /*+NO_EXPAND*/：禁止优化器对WHERE后面的OR或IN-LIST条件进行展开扩展。
• /*+MERGE(TABLE)*/：将视图的查询块合并到主查询中，减少中间结果集。
  示例：SELECT /*+MERGE(V) */ * FROM TABLE_A A, (SELECT ... ) V WHERE ...;
• /*+NO_MERGE(TABLE)*/：阻止视图合并，保持视图独立性。
• /*+NOWRITE*/：禁止对查询块进行任何重写优化。
• /*+REWRITE*/：允许将查询转换为可重写的形式，常用于视图重写。

第5步：控制表连接顺序与算法

目的：在多表关联时，确定驱动表及连接算法，这对大表Join至关重要。

• /*+ORDERED*/：按照FROM子句中表的书写顺序依次连接，即第一个表作为驱动表。
• /*+LEADING(TABLE)*/：显式指定某张表作为连接次序的首表（驱动表）。
• /*+USE_NL(TABLE)*/：强制使用嵌套循环（Nested Loops）连接，并将指定表作为内部表。适合小表驱动大表场景。
• /*+USE_HASH(TABLE)*/：强制使用哈希连接（Hash Join）。在内存允许的情况下，这是处理大规模数据连接的高效方式。
• /*+USE_MERGE(TABLE)*/：强制使用排序合并连接（Merge Sort Join）。适合有序的大数据集连接。

高级特性：并行、缓存与分布

第6步：分布式查询与缓存控制

目的：优化跨库查询性能及缓冲区的利用效率。

• /*+DRIVING_SITE(TABLE)*/：强制在与Oracle默认选择不同的位置执行远程表查询，用于优化分布式环境。
• /*+CACHE(TABLE)*/：在全表扫描时，将读入的块放置在缓冲区LRU列表的最近期使用端，防止被过早置换。
  示例：SELECT /*+FULL(BSEMPMS) CACHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
• /*+NOCACHE(TABLE)*/：禁用Cache效果，释放缓冲区压力。

第7步：数据写入优化

目的：在进行大批量数据插入时，绕过常规日志写入以提高速度。

• /*+APPEND*/：直接追加数据到表的末尾，提高插入速度，但可能导致表空间暂时不可用或需要重组。
  示例：INSERT /*+APPEND*/ INTO test1 SELECT * FROM test4;
• /*+NOAPPEND*/：启动常规插入模式，停止并行插入行为。

总结

掌握这30条Hint是深入理解Oracle执行计划的关键。然而，Hint的使用并非万能，过度依赖Hint可能会在数据结构变化或统计信息更新后导致性能回退。建议在使用Hint前，先通过EXPLAIN PLAN查看当前执行计划，确认瓶颈所在，再针对性地添加提示。以上就是SQL语句优化30个经典方法的详细内容，更多关于数据库性能调优的资料请关注本站其它相关文章！