第十九章 使用sql hint
一、综述
hint是对优化器的说明信息。写代码时，可能我们知道一些优化器不知道的信息，通过hint可以调整执行计划。

使用hint的缺点在于：额外的代码需要管理、检查和控制。对数据库或主机的更改可能造成hint不可用。因此，hint一般用于测试，而用其他首段代替来生成执行计划。
比如sql tuning advisor和sql plan baselines
oracle有六十多种hint，但一个语句块最多只能包含一个hint，并且紧跟在select、update、insert、merge、delete关键字后面

1.hint的类型
single-table：在单个表或视图上使用，如index、use_nl
multi-table：与单表hint相比，可以指定多个表的hint。如leading。use_nl不算多表hint，因为use_nl(t1 t2)是use_nl(t1) use_nl(t2)的简写
query block：在单个的查询块上指定hint。如stat_transformation和unnest
statement：对整个sql语句的hint，如all_rows

2.分类
1）hints for optimization approaches and goals
如果sql语句中hint指定了优化的路径和目标，优化器就是用特定的路径，而不管统计信息是否存在等。

二、指定hint
hint用于语句块的优化。语句块包括：
简单的select、update、delete语句
复杂语句的父语句或子句
混合语句的一部分，比如union前面的hint，只对子句有效

1.指定全局hint
比如有一个很复杂的语句，包含多表关联，如果只是对一个给定的表指定了index的hint，优化器必须决定要使用的路径和连接方式。因此，即使制定了hint“INDEX”，优化器可能还是用不到这个hint，因为优化器可能已经认为指定的索引由于连接方式、访问路径（顺序）而不能使用。

下面的例子中明确指定了需要的hint：
SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk)
USE_MERGE(j) FULL(j) */
e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, employees e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

2.在hint中指定查询块
为了在查询中指定一个查询块，可以在hint中使用可选的查询块名明确指定hint所作用的块。
查询块的定义语法为@queryblock   queryblock制定了具体的查询块。既可以是系统生成的，也可以由用户定义。

可以通过explain plan for语句来获得系统生成的块名。在语句中需要指定hint：NO_QUERY_TRANSFORMATION
用户定义的块名需要使用hint：QB_NAME

比如，现在想创建一个包含employees和job_history表的视图，其中包含嵌套查询块。
我们想查询视图中的所有行，但只对查询块使用no_unnest这个hint

1）启动sqlplus
2）创建视图
CREATE OR REPLACE VIEW v_emp_job_history AS
SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, (SELECT * FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
AND e1.salary =   ( SELECT max(e2.salary)--该部分是queryblock
   FROM employees e2
   WHERE e2.department_id = e1.department_id )
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;
3）查看执行计划：
EXPLAIN PLAN FOR SELECT * FROM v_emp_job_history;
4）查看plan table
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ALL'));
5）找到查询块的operation ID，并用这个id找到query block name
比如执行计划显示为：
------------------------------------------------------------------------------
|Id| Operation |Name |Rows|Bytes|Cost |Time
------------------------------------------------------------------------------
|0 | SELECT STATEMENT | |1 |46 |9(34)|00:00:01|
.
.
.
|11| TABLE ACCESS FULL | EMPLOYEES |107 |749 |3(0) |00:00:01|
.
.
.
-------------------------------------------------------------------------------
19-10 Oracle Database Performance Tuning Guide
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$2980E977 / V_EMP_JOB_HISTORY@SEL$1
2 - SEL$2980E977
8 - SEL$8F9407EC / VW_SQ_1@SEL$32F848CB
9 - SEL$8F9407EC
11 - SEL$8F9407EC / E2@SEL$4 --我们要找的ID是11，即对employee表的查询，从这行可以看到，对应的查询块名称为@SEL$4
6）使用no_unnest查询视图：
select /*+no_unnest(@SEL$4)*/* from v_emp_job_history;

3.指定全表hint
hint明确了在删、改、查语句块中用到的表，而不是由语句指定的视图内的表。当需要对视图中的某个表采用hint时，oracle推荐使用全局hint，而不是将hint放入view中。
可以使用扩展的tablespec包含视图名和表名，将以上的表hint转换成为global hint。
比如，创建了一个视图v用于返回职员的名字、工作、所在部门的最高薪水。想要在视图v的表e3上使用索引emp_job_ix
CREATE OR REPLACE VIEW v AS
SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1, ( SELECT * FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
AND e1.salary = ( SELECT max(e2.salary) FROM employees e2
WHERE e2.department_id = e1.department_id )
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

指定hint的方法为如下三种之一即可：
SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */ * FROM v;
SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ * FROM v;
SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ * FROM v;

还可以使用global hint指定unmergeable views：
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, departments
WHERE v1.department_id = departments.department_id;

SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
FULL(v2.departments) */ *
FROM v2
WHERE department_id = 30;

4.指定复杂索引hint
既可以使用索引名直接指定，也可以通过table.column指定。函数索引只能用索引名的形式

三、在视图中使用hint
oracle不建议在视图上使用hint，由于视图的数据就是不确定的，这样使用会使执行计划不稳定。
如果是为视图或子查询中的表指定hint，采用global hint syntax更好（以上已经介绍了）

1.hints和复杂视图
通常来说，为视图指定hint是不会传递到视图内部的（不生效的），除非hint是在基本视图上，才可能传递到视图内。

2.hints和mergeable 视图
对于可转化的视图，oracle在进行优化时将其转化为对表的操作
1）Optimization Approaches and Goal Hints in Views
优化器在选择hint时是自顶向下的，即：
如果在顶层使用了hint，就不管视图内部使用了什么hint；
如果顶层没有使用hint，数据库使用所参照表上的mode hints和所有视图上的mode hints；
如果hints发生了冲突，就将所有hint都忽略

2）Access path and join hints on views
Access path and join hints on referenced views are ignored unless the view contains a single table or references an Additional Hints view with a single table
只有单表视图或者调用了其他指定单表视图的视图的执行路径和连接hint能生效。

3）access path and join hints inside views
访问路径和连接hint可以在视图定义中出现。
如果视图是inline view（即只出现在select的from子句中），那么视图中的所有访问路径和连接hint在该视图被顶层查询调用时都生效
对于non-inline views，视图中的访问路径和连接方法hint只有在调用语句的from子句中不包含其他表的情况下才会生效。

4.hints and nonmergeable views
对于不可转换的视图，视图中的优化路径和goal hints将被忽略，顶层查询决定优化器模式。