使用HINTS控制执行计划

  优化器不是完美的，它可能因为各种原因，未走高效的执行计划。如优化器本身缺陷：子查询里有OR走FILTER，或遇到BUG等,这时候可以通过HINTS来控制优化器生成目标执行计划。

HINTS种类：
访问路径提示： 如index hints JOIN方式提示：如use_hash,use_nl,use_merge
查询转换提示：如merge,or_expand,unnest
概要信息提示：如qb_name,ignore_optim_embedded_hints,opt_param
统计数字提示：如dynamic_sampling，cardinality
优化器提示：如all_rows,first_rows
其它提示：如append,parallel,driving_site等

  对于目前的oracle数据库来说，一般不需要在SQL里嵌入hints，可以使用SQL PROFILE等来调整执行计划。 编写HINTS要符合对应HINTS使用场景，有别名的要使用别名，组合HINTS没有冲突，语义正确，视图HINTS要用视图别名.对象别名。
  启用HINTS意味着使用CBO（除了RULE和DRIVING_SITE HINTS），HINTS是一种提示，有各种原因各种不生效，比如HINTS有语法错误、CBO无法走指定HINTS执行计划等。

  下面看对视图使用hints的例子：

CREATE OR REPLACE VIEW v1 AS

SELECT *

FROM employees

WHERE employee_id < 150;

CREATE OR REPLACE VIEW v2 AS

SELECT v1.employee_id employee_id, departments.department_id department_id

FROM v1, hr.departments

WHERE v1.department_id = departments.department_id;

SELECT /*+ NO_MERGE(v2.v1) INDEX(v2.v1.employees idx_dept_id)

FULL(v2.departments) */ *

FROM v2

WHERE department_id = 30;

执行计划如下：

使用HINTS控制执行计划顺序：
1）leading或ordered是指定驱动表顺序,建议使用leading,更加灵活，ordered可能会导致笛卡尔积。
2）leading(a,b,c,d),use_nl(a,b,c,d)按照顺序做NESTED LOOPS
3）leading(a,b,c,d),use_hash(a,b,c,d)，a,b先做HASH JOIN，但是驱动表是CBO决定的，这时候{BANNED}最佳好配合swap_join_inputs指定driving table,no_swap_join_inputs非driving_table
4）use_nl和use_hash指定多个表，实际上等价于写多个use_nl或use_hash的单表，比如use_nl(a,b)<=>use_nl(a) use_nl(b)
5）对于use_nl,use_hash,use_merge必须指定leading或ordered,否则可能得不到想要的执行计划

下面看一个leading和use_hash的例子，是否有什么问题？
  leading和use_hash，和前面的use_nl不同，它的驱动表的顺序是CBO决定的，比如按照字面理解上面的应该是A,B做HASH JOIN,驱动表是A，然后结果与C做HASH JOIN，C被驱动，{BANNED}最佳后与D做HASH JOIN，D是被驱动的，但是实际上D是驱动表。这时候需要使用(no_)swap_join_inputs来指定驱动表（只针对HASH JOIN有效），使用这个可以灵活控制HASH JOIN的驱动表，实现复杂HASH JOIN。

  通过leading(a,b,c,d) use_hash(a,b,c,d) swap_join_inputs(a) no_swap_join_inputs(c) no_swap_join_inputs(d)确定hash join顺序是((A,B),C),D。


  通过leading(a,b,c,d) use_hash(a,b,c,d) swap_join_inputs(a) swap_join_inputs(c) swap_join_inputs(d)确定hash join顺序是D,(C,(A,B))。