3.Oracle-HINT1

1.oracle里面常用hint学习

1.--执行SQL时收集额外的统计信息。
select /*+gather_plan_statistics*/ t1.empno,t1.ename,t2.dname from scott.emp t1.scott.dept t2 where t1.deptno=t2.deptno; 
 
2.--显示执行计划时显示统计信息
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

3.--对表执行全表扫描 
select /*+full(t2)*/ t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO';
--这种写法后面不能跟任何内容，必须另起一行。
select --+ full(emp)    
* from emp where empno=7369; 

--多个查询块使用full;HINT; 
select /*+ full(t1)*/ t1.ename,t1.deptno from emp t1 where t1.deptno in 
(select /*+ full(t2) */t2.deptno from dept t2 where t2.loc='CHICAGO');

--第一个查询块,第二个查询块都走全表扫描。
--方法一:
select /*+ full(@sel$1 t1) full(@sel$2 t2)*/ t1.ename,t1.deptno from emp t1 where t1.deptno in 
(select t2.deptno from dept t2 where t2.loc='CHICAGO');
--方法二:
select /*+ full(t1@sel$1) full(t2@sel$2)*/ t1.ename,t1.deptno from emp t1 where t1.deptno in 
(select t2.deptno from dept t2 where t2.loc='CHICAGO');

--方法三:
--自定义查询块的名称。
select /*+ full(t1) full(@qb_name1 t2)*/ t1.ename,t1.deptno from emp t1 where t1.deptno in 
(select /*+ qb_name(qb_name1)*/t2.deptno from dept t2 where t2.loc='CHICAGO');
--方法四:
select /*+ full(t1@sel$1) full(t2@qb_name1)*/ t1.ename,t1.deptno from emp t1 where t1.deptno in 
(select /*+ qb_name(qb_name1)*/t2.deptno from dept t2 where t2.loc='CHICAGO');


--执行完SQL,就执行如下可以查看执行计划。
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
--Outline Data 部分列出的hint可以直接用在 HINT里面。
--Outline Data部分实际上是Oracle用来固定执行计划的内部HINT组合,它是非常全面的HINT组合。




4.--使用基于规则的优化器 
--
select /*+rule*/ t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO';

5.--DRIVING_SITE 
--用于使用了分布式SQL,指定目标SQL的执行节点。
--driving_site是可以在RBO下使用同时不启用CBO的一个例外。
--在EMP本地节点执行。执行RBO
select /*+driving_site(t1)*/ t1.empno,t1.ename,t2.loc from emp t1,dept@dblink1 t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO';

--发送到远程节点DEPT表所在节点执行。使用CBO;
select /*+driving_site(t2)*/ t1.empno,t1.ename,t2.loc from emp t1,dept@dblink1 t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO';

--rule+full=full失效。
select /*+rule full(t2)*/ t1.empno,t1.ename,t2.loc from emp t1,dept  t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO';

--rule+driving_site=rule失效 
select /*+rule driving_site(t2)*/ t1.empno,t1.ename,t2.loc from emp t1,dept@dblink1  t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO';

--所以rule最好单独使用。
--HINT必须紧随:SELECT,INSERT,UPDATE,DELETE,MERGE;否则失效。
--加号和星号必须连写,否则失效。
--HINT中不能写SCHEMA的名称。
--HINT中,SQL中表有别名，必须使用别名。

6._OPTIMIZER_IGNORE_HINTS 
--可以在系统级别或者会话级别让所有的HINT失效。
alter session set "_OPTIMIZER_IGNORE_HINTS"=true; 
select /*+full(emp)*/ empno,ename from emp where empno=7369;

--此时会话级别禁用所有的HINT;即HINT失效。

7.INDEX
--索引列中有Null值的情况下会失效。
alter table dept modify (loc null);
select /*index(dept idx_dept_loc)*/deptno,dname from dept where deptno=30;
--NULL值不存储单键值B树索引。此时HINT失效。

8.PARALLEL 
--全表扫描并行执行。
select /*+full(dept) parallel(dept 2)*/deptno from dept; 

--非分区索引的并行会失效，如下并行失效。
select /*+index(dept pk_dept) parallel(dept 2)*/ deptno from dept; 
--全表扫描可以并行
--分区索引扫描可以并行

9.USE_HASH 
--HASH连接必须是等值连接,被驱动表数据量大(有索引),驱动表数量小。

select /*+use_hash(t1)*/t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno 
and t2.loc='CHICAGO';
--EMP表数量大，DEPT表数量少。

--将数量小的表作为被驱动表,违反HASH连接的规则，失效。
select /*+use_hash(t2)*/t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno 
and t2.loc='CHICAGO';

--非等值连接,use_hash失效。
select /*+use_hash(t2)*/t1.empno,t1.ename,t2.loc from emp t1,dept t2 where t1.deptno>=t2.deptno 
and t2.loc='CHICAGO';
--哈希连接只适用于等职连接,不等值连接对哈希连接而言是没有意义的。


10.INDEX_FFS
set autotrace traceonly 
select /*+index_fss(dept pk_dept)*/ deptno from dept; 
--索引快速全扫描。

--自相矛盾的HINT会失效,如下SQL HINT失效。其他部分会生效。
select /*+full(dept) index_fss(dept pk_dept)*/ deptno from dept ;

--只有cardinality生效,其他自相矛盾的失效。CARDINALITY修改为100;
select /*+full(dept) index_fss(dept pk_dept) cardinality(dept 100)*/ deptno from dept ;

2.总结

熟记常用Hint，对oracle优化很重要。