概述

本来是不太想写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 200set pagesize 500set autotrace traceonlywith 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 1000set autotrace traceonly explainSELECT /*+ 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 explainSELECT /*+ leading(t1) use_merge(t2)*/ * FROM t1, t2 WHERE t1.id like t2.t1_id AND t1.n = 19;

4、组合Hint有矛盾

set linesize 300set pagesize 300alter 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 200set pagesize 400set 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 1000set pagesize 2000explain 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的一些用法也可以熟悉下，好记性不如烂笔头嘛。

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