Hint管理¶
Hint指的是"查询优化提示"，它会提示优化器按照一定的方式去优化，让你的SQL语句更具灵活性，这会让你的查询更快，当然也可能更慢，这完全取决于你对优化器的理解和场景的了解。Hint的使用详情参见 HINT使用参考
为什么引入Hint¶
虽然优化器如今很智能几乎不用我们去干扰SQL语句的执行计划，但面对大量的数据和频繁DML操作的表，优化器也未必就能完全给出最优的执行计划。
因此，对于某一条特定的查询语句，如果用户对查询所涉及的表数据非常了解，那么通过使用Hint，用户就可以提示优化器生成自己指定的执行计划，从而获得更好的查询性能。
Hint与注释的关系¶
Hint作为一种特殊的注释来添加，不能像普通注释那样在SQL语句中随处添加，必须紧跟在SQL语句块的第一个关键字之后，且在注释分隔符之后的第一个字符必须是加号， 加号前不允许有空格。
Hint注意事项¶
Hint可以影响优化器对于执行计划的选择，但这种影响不是强制性的。
一般情况下，优化器会按照Hint指示生成对应的执行计划，但也存在一些特殊情况，一旦发生如下特殊情况，优化器会忽略Hint指示，按照默认的优化方式生成计划。
- Hint存在语法和语义错误时，优化器会自动忽略这些错误的Hint，同时不会影响其它正确Hint的执行。
- Hint所指定的计划是优化器不可能生成的计划或生成的执行计划代价太大，优化器会自动忽略这些Hint，同时优化器会给出一个自认为最优的执行计划。
- Hint之间组合是冲突的，两个Hint都会被优化器忽略，如果使用的组合Hint中还有其他有效的Hint，则剩余有效Hint不受影响。
- Hint注释中如果遇到的词不是Hint关键字时，将忽略这个词之后的所有Hint，词之前有效Hint不受影响。
- Hint受视图合并或子查询提升的影响，如果视图没有合并或子查询没有提升，则父查询中作用于当前视图或子查询中的Hint将也会被忽略。
- Hint里的表和视图在SQL中有别名，在Hint里必须使用别名，否则优化器会自动忽略这些没有使用别名的Hint。
注解
可使用 Hint报告 功能来检查hint是否被忽略。
Hint被忽略的常见情况¶
例1：Hint有语法或者拼写错误
一旦使用的Hint中有语法或者拼写错误，优化器就会忽略该Hint。
--建表
create table test1(id int, name varchar(30));
CREATE TABLE
--插入数据
BEGIN
FOR i IN 1..800 LOOP
insert into test1(id, name) values(i, to_char(i)) ;
END LOOP;
RETURN;
END;
/
--分析数据
analyze test1;
ANALYZE
--建简单索引
create index test1_idx1 on test1(id);
CREATE INDEX
--顺序执行计划
explain select /*+ full(test1) */ * from test1 where id <500 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on TEST1 (cost=0.00..18.00 rows=500 width=11)
Scan Key: (ID < 500)
(2 rows)
--Hint语法或者拼写错误，Hint被忽视
explain select /*+ fuii(test1) */ * from test1 where id <500 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..12.32 rows=500 width=11)
Index Key: (ID < 500)
(2 rows)
--Hint语法或者拼写错误，Hint被忽视
explain select /* + full(test1) */ * from test1 where id <500 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..12.32 rows=500 width=11)
Index Key: (ID < 500)
(2 rows)
两个Hint都是无效的，它们都会被忽略，关键字应该是"full"而不是"fuii"，第一个*和+之间不能出现空格。
例2：使用的Hint无效
即使语法是正确的，但如果由于某种原因导致优化器认为这个Hint无效，则还是会忽略该Hint。
--使用的Hint无效
SQL> explain select /*+ index(test1 test1_idx1) */ * from test1;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on TEST1 (cost=100000000.00..100000016.00 rows=800 width=11)
(1 row)
--删除简单索引
drop index test1_idx1;
CREATE INDEX
--使用的Hint无效
SQL> explain select /*+ index(test1 test1_idx1) */ * from test1 where id = 500;
QUERY PLAN
-------------------------------------------------------
Seq Scan on TEST1 (cost=0.00..18.00 rows=1 width=11)
Scan Key: (ID = 500)
(2 rows)
从上面的输出可以看出，执行计划走的是顺序执行计划，而不是Hint里的test1_idx1（没有连接和选择条件不会创建索引扫描路径），这就说明Hint在这个SQL失效了。即使不改where条件，如果把索引test1_idx1删除，这个Hint也会失效。
--建表
create table test2(id int, name varchar(30));
CREATE TABLE
--使用的Hint无效
explain select /*+ use_hj(test1,test2) */ test1.id,test1.name,test2.id from test1,test2;
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop(Inner Join) (cost=100000016.80..100131178.72 rows=6553600 width=15)
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=4)
-> Materialize (cost=16.80..24.80 rows=800 width=11)
-> Seq Scan on TEST1 (cost=0.00..16.00 rows=800 width=11)
(4 rows)
--添加等值连接条件
explain select /*+ use_hj(test1,test2) */ test1.id,test1.name,test2.id from test1,test2 where test2.name=test1.name;
QUERY PLAN
----------------------------------------------------------------------
Hash Join(Inner Join) (cost=192.32..1112.24 rows=8192 width=15)
Hash Cond: ("outer"."NAME" = "inner"."NAME")
-> Seq Scan on TEST1 (cost=0.00..16.00 rows=800 width=11)
-> Hash (cost=89.92..89.92 rows=8192 width=37)
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
(5 rows)
从上面的执行计划中看出use_hj确实是被优化器忽略了，这是因为哈希连接只适用于等值连接条件，不等值的连接条件对哈希连接而言是没有意义的，所以上述Hint就被忽略了。
例3：Hint之间发生冲突
如果使用的组合Hint是自相冲突的，则这些自相冲突的Hint都会被优化器忽略。但优化器只会将自相矛盾的Hint全部忽略掉，但如果使用的组合Hint中还有其他有效的Hint，则这些有效Hint不受影响。
--默认执行计划
explain select * from test1 where id < 500;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..13.67 rows=500 width=11)
Index Key: (ID < 500)
(2 rows)
--顺序执行计划和索引执行计划发生冲突，导致两者Hint失效
explain select /*+ full(test1) index(test1 test1_idx1) */ * from test1 where id < 500;
--NOTICE, 两个Hint发生冲突, 第0号Hint和第1号Hint冲突, 冲突Hint均失效!
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..9.22 rows=100 width=11)
Index Key: (ID < 100)
(2 rows)
从上面的输出可以看出执行计划没有走Hint中指定的执行计划，这两个Hint都失效了。
--默认执行计划
explain select * from test1, test2 where test1.id < 500;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Nested Loop(Inner Join) (cost=98.11..82031.78 rows=4096000 width=48)
-> Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..13.67 rows=500 width=11)
Index Key: (ID < 500)
-> Materialize (cost=98.11..180.03 rows=8192 width=37)
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
(5 rows)
--use_nl连接方式和use_semi_nl连接方式发生冲突，导致两者Hint失效，但其他Hint不受影响
explain select /*+ use_nl(test1,test2) use_semi_nl(test1,test2) full(test1) */ * from test1, test2 where test1.id < 500;
--NOTICE, 两个Hint发生冲突, 第6号Hint和第11号Hint冲突, 冲突Hint均失效!
QUERY PLAN
-----------------------------------------------------------------------
Nested Loop(Inner Join) (cost=18.50..82028.42 rows=4096000 width=48)
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
-> Materialize (cost=18.50..23.50 rows=500 width=11)
-> Seq Scan on TEST1 (cost=0.00..18.00 rows=500 width=11)
Scan Key: (ID < 500)
(5 rows)
从上面的输出可以看出执行计划走的仍然是顺序执行计划，说明full(test1)生效了，也验证了如果使用的组合Hint中还有其他有效的Hint，则这些有效Hint不受影响。
例4：不是Hint关键字的影响
解析Hint时，是按照从左到右的顺序进行的，如果遇到的词是有效的Hint，那么优化器就会保留该Hint，如果遇到的词不是Hint关键字，则将忽略这个词以及之后的所有词，导致后续相关的Hint失效。
--默认执行计划
explain select * from test1, test2 where test1.id = test2.id and test1.id < 500;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Hash Join(Inner Join) (cost=19.92..191.76 rows=5120 width=48)
Hash Cond: ("outer".ID = "inner".ID)
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
-> Hash (cost=13.67..13.67 rows=500 width=11)
-> Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..13.67 rows=500 width=11)
Index Key: (ID < 500)
(6 rows)
--使用的Hint无效
explain select /*+ int full(test1) use_mj(test1,test2) */ * from test1, test2 where test1.id = test2.id and test1.id < 500;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Hash Join(Inner Join) (cost=19.92..191.76 rows=5120 width=48)
Hash Cond: ("outer".ID = "inner".ID)
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
-> Hash (cost=13.67..13.67 rows=500 width=11)
-> Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..13.67 rows=500 width=11)
Index Key: (ID < 500)
(6 rows)
--去掉int的执行计划
explain select /*+ full(test1) use_mj(test1,test2) */ * from test1, test2 where test1.id = test2.id and test1.id < 500;
QUERY PLAN
----------------------------------------------------------------------
Merge Join(Inner Join) (cost=662.81..757.47 rows=5120 width=48)
Merge Cond: ("outer".ID = "inner".ID)
-> Sort (cost=40.41..41.66 rows=500 width=11)
Sort Key: ID
-> Seq Scan on TEST1 (cost=0.00..18.00 rows=500 width=11)
Scan Key: (ID < 500)
-> Sort (cost=622.40..642.88 rows=8192 width=37)
Sort Key: ID
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
(9 rows)
从执行计划中看出，现在走的是跟一开始的执行计划一样，说明Hint中的两个都失效了，因为这两个都在int（不是Hint关键字）后面。
--默认执行计划
explain select * from test1, test2 where test1.id = test2.id and test1.id < 500;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Hash Join(Inner Join) (cost=19.92..191.76 rows=5120 width=48)
Hash Cond: ("outer".ID = "inner".ID)
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
-> Hash (cost=13.67..13.67 rows=500 width=11)
-> Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..13.67 rows=500 width=11)
Index Key: (ID < 500)
(6 rows)
--使用的full无效
explain select /*+ use_mj(test1,test2) int full(test1) */ * from test1, test2 where test1.id = test2.id and test1.id < 500;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Merge Join(Inner Join) (cost=622.40..729.48 rows=5120 width=48)
Merge Cond: ("outer".ID = "inner".ID)
-> Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 (cost=0.00..13.67 rows=500 width=11)
Index Key: (ID < 500)
-> Sort (cost=622.40..642.88 rows=8192 width=37)
Sort Key: ID
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
(7 rows)
--去掉int的执行计划
explain select /*+ full(test1) use_mj(test1,test2) */ * from test1, test2 where test1.id = test2.id and test1.id < 500;
QUERY PLAN
----------------------------------------------------------------------
Merge Join(Inner Join) (cost=662.81..757.47 rows=5120 width=48)
Merge Cond: ("outer".ID = "inner".ID)
-> Sort (cost=40.41..41.66 rows=500 width=11)
Sort Key: ID
-> Seq Scan on TEST1 (cost=0.00..18.00 rows=500 width=11)
Scan Key: (ID < 500)
-> Sort (cost=622.40..642.88 rows=8192 width=37)
Sort Key: ID
-> Seq Scan on TEST2 (cost=0.00..89.92 rows=8192 width=37)
(9 rows)
从执行计划中看出，使用了Merge Join的执行计划，说明Hint中的use_mj成功被使用了，但int（不是Hint关键字）后面的Hint失效了，则说明如果当前词不是有效的Hint，前面的Hint不会被忽略。
例5：Hint受视图合并的影响
视图没有合并或子查询没有被提升也会导致父查询中关于视图或子查询的Hint失效，同样视图合并或子查询提升，其中的Hint也会作用于父查询中。
--建表
create table a(a1 int, a2 int);
CREATE TABLE
--建表
create table b(b1 int, b2 int);
CREATE TABLE
--建表
create table c(c1 int, c2 int);
CREATE TABLE
--建索引
create index idx_b1 on b(b1);
CREATE INDEX
create index idx_b2 on b(b2);
CREATE INDEX
--建索引
create index idx_c1 on c(c1);
CREATE INDEX
create index idx_c2 on c(c2);
CREATE INDEX
--默认执行计划
explain select * from a, (select b1 from b, c where c.c1 = 100) T where a.a1 =T.b1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Nested Loop(Inner Join) (cost=7733.51..349946.04 rows=13743895 width=12)
-> RowcountMaterial (cost=1.52..1.52 rows=41 width=0)
-> Index Scan using IDX_C1(Fast Index Scan) on C (cost=0.00..1.41 rows=41 width=0)
Index Key: (C1 = 100)
-> Materialize (cost=7733.51..12727.95 rows=335544 width=12)
-> Merge Join(Inner Join) (cost=622.40..5758.96 rows=335544 width=12)
Merge Cond: ("outer".B1 = "inner".A1)
-> Index Scan using IDX_B1(Fast Index Scan) on B (cost=0.00..82.92 rows=8192 width=4)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
(11 rows)
--视图合并，使用父查询中的Hint
explain select /*+ full(b) full(c) */ * from a, (select b1 from b, c where c.c1 = 100) T where a.a1 = T.b1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Nested Loop(Inner Join) (cost=732.84..280755.58 rows=13743895 width=12)
-> Merge Join(Inner Join) (cost=622.40..5766.96 rows=335544 width=12)
Merge Cond: ("outer".A1 = "inner".B1)
-> Index Scan using IDX_A1(Normal Index Scan) on A (cost=0.00..90.92 rows=8192 width=8)
-> Sort (cost=622.40..642.88 rows=8192 width=4)
Sort Key: B1
-> Seq Scan on B (cost=0.00..89.92 rows=8192 width=4)
-> RowcountMaterial (cost=110.50..110.50 rows=41 width=0)
-> Seq Scan on C (cost=0.00..110.40 rows=41 width=0)
Scan Key: (C1 = 100)
(10 rows)
--视图未合并，使用的Hint无效
explain select /*+ full(b) full(c)*/ * from a, (select /*+ no_use_mergeview */ b1 from b,c where c.c1 = 100) T where a.a1 = T.b1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Merge Join(Inner Join) (cost=40297.51..246497.04 rows=13743895 width=12)
Merge Cond: ("outer".A1 = "inner".B1)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Sort (cost=39675.11..40513.97 rows=335544 width=4)
Sort Key: B1
-> Subquery Scan T (cost=0.00..6756.61 rows=335544 width=4)
-> Nested Loop(Inner Join) (cost=0.00..6756.61 rows=335544 width=4)
-> RowcountMaterial (cost=1.52..1.52 rows=41 width=0)
-> Index Scan using IDX_C1(Fast Index Scan) on C (cost=0.00..1.41 rows=41 width=0)
Index Key: (C1 = 100)
-> Index Scan using IDX_B1(Fast Index Scan) on B (cost=0.00..82.92 rows=8192 width=4)
(13 rows)
视图合并时，从执行计划中看出，使用了顺序执行计划，说明Hint中的full成功被使用了，视图未合并时，从执行计划中看出，所使用的Hint都失效了。
--默认执行计划
explain select * from a, (select b1 from b) T where a.a1 = T.b1;
QUERY PLAN
-------------------------------------------------------------------------------------------
Merge Join(Inner Join) (cost=622.40..5758.96 rows=335544 width=12)
Merge Cond: ("outer".B1 = "inner".A1)
-> Index Scan using IDX_B1(Fast Index Scan) on B (cost=0.00..82.92 rows=8192 width=4)
-> Sort (cost=622.40..642.88 rows=8192 width=8)
Sort Key: A1
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
(6 rows)
--视图合并，视图中的Hint作用于父查询
explain select * from a, (select /*+ use_nl(a,b) */ b1 from b) T where a.a1 = T.b1;
QUERY PLAN
-------------------------------------------------------------------------------------------
Nested Loop(Inner Join) (cost=0.00..1518239.55 rows=335544 width=12)
Join Filter: ("outer".A1 = "inner".B1)
-> Seq Scan on A (cost=0.00..89.92 rows=8192 width=8)
-> Index Scan using IDX_B1(Fast Index Scan) on B (cost=0.00..82.92 rows=8192 width=4)
(4 rows)
视图合并时，从执行计划中看出，使用了Nested Loop的执行计划，说明视图中的use_nl成功被父查询使用了。
例6：Hint受别名的影响
语句中有别名，Hint中必须使用别名，如果使用表的名称，则会忽略当前Hint。
--默认执行计划
explain select * from test1 t1, test2 t2 where t1.id = t2.id and t1.id < 500;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Hash Join(Inner Join) (cost=19.92..191.76 rows=5120 width=48)
Hash Cond: ("outer".ID = "inner".ID)
-> Seq Scan on TEST2 T2 (cost=0.00..89.92 rows=8192 width=37)
-> Hash (cost=13.67..13.67 rows=500 width=11)
-> Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 T1 (cost=0.00..13.67 rows=500 width=11)
Index Key: (ID < 500)
(6 rows)
--有别名未使用别名，导致Hint失效
explain select /*+ full(test1) use_mj(test1,test2) */ * from test1 t1, test2 t2 where t1.id = t2.id and t1.id < 500;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Hash Join(Inner Join) (cost=19.92..191.76 rows=5120 width=48)
Hash Cond: ("outer".ID = "inner".ID)
-> Seq Scan on TEST2 T2 (cost=0.00..89.92 rows=8192 width=37)
-> Hash (cost=13.67..13.67 rows=500 width=11)
-> Index Scan using TEST1_IDX1(Normal Index Scan) on TEST1 T1 (cost=0.00..13.67 rows=500 width=11)
Index Key: (ID < 500)
(6 rows)
--Hint中使用别名的执行计划
explain select /*+ full(t1) use_mj(t1,t2) */ * from test1 t1, test2 t2 where t1.id = t2.id and t1.id < 500;
QUERY PLAN
-------------------------------------------------------------------------
Merge Join(Inner Join) (cost=662.81..757.47 rows=5120 width=48)
Merge Cond: ("outer".ID = "inner".ID)
-> Sort (cost=40.41..41.66 rows=500 width=11)
Sort Key: ID
-> Seq Scan on TEST1 T1 (cost=0.00..18.00 rows=500 width=11)
Scan Key: (ID < 500)
-> Sort (cost=622.40..642.88 rows=8192 width=37)
Sort Key: ID
-> Seq Scan on TEST2 T2 (cost=0.00..89.92 rows=8192 width=37)
(9 rows)
从执行计划中看出，两个Hint都失效了，因为Hint中在有别名的情况未使用别名，导致Hint失效了。