Oracle hint Leading 的使用

关注

原创最新推荐文章于 2024-10-20 22:21:06 发布 1.1w 阅读 AI 写同款· GEO 优化 ›

Oracle查询优化神器：Hint命令使用大全 Oracle Hint是一种嵌入在SQL注释中的特殊指令，用于指导优化器选择执行计划。主要分为访问路径Hint（FULL、INDEX等）、连接顺序Hint（ORDERED、LEADING）、连接方法Hint（USE_NL、USE_HASH等）、并行执行Hint（PARALLEL）和高级Hint（MATERIALIZE、FIRST_ROWS等）。使用时需遵循特定语法格式，可通过执行计划对比验证效果。需注意Hint可能因语法错误、别名不匹配等原因失效，建议先分析后逐步添加，并定期复查。合理使用Hint能优化查询阅读详情

Leading()：指示Oracle在执行join(hash join, nested loop join, merge join)时的连接顺序。

当执行计划不按照最优的表连接顺序时，考虑使用Leading 改变执行计划。使用leading 的目的是减少检索数据量，提高查询性能。

eg:

select v., t. from v_view1 join (select /+ leading(t1, tftc) / * from tftc, t1 where t1.id=tftc.id) t
on v.id=t.id and v.name=t.name left join t2 on t2.name=v.name

;

LEADING Hint

Description of the illustration leading_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint is more versatile than the ORDERED hint. For example:

SELECT /*+ LEADING(e j) */ *
    FROM employees e, departments d, job_history j
    WHERE e.department_id = d.department_id
      AND e.hire_date = j.start_date;

The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If you specify the ORDERED hint, it overrides all LEADING hints.