概述

本来是不太想写HINT这篇的，毕竟比较乱，最后想了想还是写了，当做笔记记录在这吧，大家凑合看下咯。

hint的分类

主要分成初始化参数hint，查询转化hint，访问路径hint，连接提示hint，并行处理hint和其他hint

子查询应用范围

简单的 SOL 语句只有一个单独的查询块。当使用视图或类似子查询、内联视图、集合操作 符等结构时， 就会出现多个查询块（比如下面这个例子的查询就有两个查询块，第一个是引用了 dept 表的主查询，第二个是引用了 emp 表的子查询）。

上面总结了 hint的分类，除了第一类初始化参数 hint 外， 所有其他的hint都是仅针对单个查询块起作用。下面来看如何让各个模块的 HINT 生效的各种方法。

1、环境准备：

drop table emp purge;
create table emp as select * from scott.emp;
create index idx_emp_deptno on emp(deptno);
create index idx_emp_empno on emp(empno);
drop table dept purge;
create table dept as select * from scott.dept;
create index idx_dept_deptno on dept(deptno);

2、查看执行计划

set linesize 200
set pagesize 500
set autotrace traceonly
with emps as (select deptno,count(*) as cnt from emp where empno in (7369,7782,7499) group by deptno) select dept.dname,emps.cnt from dept,emps where dept.deptno=emps.deptno; 

3、控制在所在的查询块内

with emps as (select /*+full(emp)*/ deptno,count(*) as cnt 
 from emp where empno in (7369,7782,7499)
 group by deptno)
select /*+full(dept)*/ dept.dname,emps.cnt 
 from dept,emps
 where dept.deptno=emps.deptno;

4、全局的hint的别名引用

with emps as (select deptno,count(*) as cnt 
 from emp
 where empno in (7369,7782,7499)
 group by deptno)
select /*+full(dept) full(emps.emp)*/ dept.dname,emps.cnt 
 from dept,emps
 where dept.deptno=emps.deptno;

5、用qb_name定义方式

有的时候 SOL 不写子查询的别名，比如 WHERE 条件中的子查询显然用不到别名，这时可以 用 qb_name 定义方式，其中 ， qb_name(main）是固定必须写的 ， 比如如下的 full(@main dept) 就是来引用主表的。

with emps as (select /*+qb_name(sq)*/ deptno,count(*) as cnt 
 from emp
 where empno in (7369,7782,7499)
 group by deptno)
select /*+qb_name(main) full(@main dept) full(@sq emp)*/ dept.dname,emps.cnt 
 from dept,emps
 where dept.deptno=emps.deptno;

HINT 无效原因

HINT 在使用过程中时常会遇到无法生效的情况，一般来说都是算法无法支持、 Hint 有矛盾、根据 Hint 的结果执行会错、书写语法错这几个原因。

1、环境准备

DROP TABLE t1 CASCADE CONSTRAINTS PURGE; 
DROP TABLE t2 CASCADE CONSTRAINTS PURGE; 
CREATE TABLE t1 (
 id NUMBER NOT NULL,
 n NUMBER,
 contents VARCHAR2(4000)
 ); 
CREATE TABLE t2 (
 id NUMBER NOT NULL,
 t1_id NUMBER NOT NULL,
 n NUMBER,
 contents VARCHAR2(4000)
 ); 
execute dbms_random.seed(0); 
INSERT INTO t1 SELECT rownum, rownum, dbms_random.string('a', 50) FROM dual CONNECT BY level <= 100 ORDER BY dbms_random.random; 
INSERT INTO t2 SELECT rownum, rownum, rownum, dbms_random.string('b', 50) FROM dual CONNECT BY level <= 100000 ORDER BY dbms_random.random; 
COMMIT;

2、use_hash的算法不支持不等值连接

下面使用 use_hash 的 Hint 希望能走 Hash 连接，结果实际是 NL 连接， 因为 Hash 连接 不支持连接条件是 t1.id > t2.t1_id 这样不等的写法

set linesize 1000
set autotrace traceonly explain
SELECT /*+ leading(t1) use_hash(t2)*/ * FROM t1, t2 WHERE t1.id > t2.t1_id AND t1.n = 19;

3、use_hash的算法不支持LIKE连接

下面的连接条件是like，同样只能适用于 NL ， 而不能适用于其他，这里试验use_merge，一样以失败告终

set autotrace traceonly explain
SELECT /*+ leading(t1) use_merge(t2)*/ * FROM t1, t2 WHERE t1.id like t2.t1_id AND t1.n = 19;

4、组合Hint有矛盾

set linesize 300
set pagesize 300
alter session set statistics_level=all ;
SELECT /*+ leading(t2) use_nl(t2) */ * FROM t1, t2 WHERE t1.id = t2.t1_id;

这里可以发现用了hash连接。因为 use_nl(t2)表示 t2 被驱动，也就是 t2 表后访问，而 leading(t2）却表示 t2 表要前驱，先访问。这不是矛盾吗？所以这个 hint 失效了。

5、依据Hint结果执行会错

drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
set linesize 200
set pagesize 400
set autotrace traceonly;
select /*+index(t,idx_object_id)*/ count(*) from t;

这里发现依然走的是全表扫描， Hint 失效了。真正原因是 ，如果走索引，那就要依赖索引回答条数的问题。这里有巨大风险，因为索引不存储空值，而索引列并没有保证非空，这里的值会不正确。

6、Hint书写出现了错误

如果 SOL 的表有别名，必须用别名而不能用原表名 ，否则无法生效。

drop table test purge;
create table test as select * from dba_objects;
create index idx_test_objid on test(object_id);
set linesize 1000
set pagesize 2000
explain plan for select /*+index(test,idx_test_objid)*/ * from test t where object_id>0;
select * from table(dbms_xplan.display());

修改如下：

select /*+index(t,idx_test_objid)*/ * from test t where object_id>0;
select /*+index(test,idx_test_objid)*/ * from test where object_id>0;

关于hint的一些实验就介绍到这了，大家有空可以跟着做下，对于hint的一些用法也可以熟悉下，好记性不如烂笔头嘛。

后面会分享更多平时测试的实验，感兴趣的朋友可以关注下！