oracle hint原理

oracle的hint很多人都知道，那原理是什么呢？在系统中该不该用呢？先来做个试验：

create table test1 as select * from dba_objects where rownum <=100;

create table test2 as select * from dba_objects where rownum <=1000;
alter system flush shared_pool;
alter session set events '10053 trace name context forever, level 1';
select /*+use_nl(t1,t2)*/count(*) from test1 t1,test2 t2 where t1.object_id = t2.object_id;
alter session set events '10053 trace name context off';

trace的结果：

***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  TEST1[T1]#0  TEST2[T2]#1
***************
Now joining: TEST2[T2]#1
***************
NL Join
  Outer table: Card: 100.00  Cost: 3.00  Resp: 3.00  Degree: 1  Bytes: 72
  Inner table: TEST2  Alias: T2
  Access Path: TableScan
    NL Join:  Cost: 311.61  Resp: 311.61  Degree: 0
      Cost_io: 310.00  Cost_cpu: 49756767
      Resp_io: 310.00  Resp_cpu: 49756767
  Best NL cost: 311.61
          resc: 311.61 resc_io: 310.00 resc_cpu: 49756767
          resp: 311.61 resp_io: 310.00 resp_cpu: 49756767
Join Card:  100.00 = outer (100.00) * inner (1000.00) * sel (1.0000e-003)
Join Card - Rounded: 100 Computed: 100.00
Best:: JoinMethod: NestedLoop
       Cost: 311.61  Degree: 1  Resp: 311.61  Card: 100.00  Bytes: 152
***********************
Best so far: Table#: 0  cost: 3.0024  card: 100.0000  bytes: 7200
             Table#: 1  cost: 311.6068  card: 100.0000  bytes: 15200
***********************
Join order[2]:  TEST2[T2]#1  TEST1[T1]#0
***************
Now joining: TEST1[T1]#0
***************
NL Join
  Outer table: Card: 1000.00  Cost: 5.02  Resp: 5.02  Degree: 1  Bytes: 80
  Inner table: TEST1  Alias: T1
  Access Path: TableScan
    NL Join:  Cost: 1026.43  Resp: 1026.43  Degree: 0
      Cost_io: 1024.00  Cost_cpu: 75104022
      Resp_io: 1024.00  Resp_cpu: 75104022
  Best NL cost: 1026.43
          resc: 1026.43 resc_io: 1024.00 resc_cpu: 75104022
          resp: 1026.43 resp_io: 1024.00 resp_cpu: 75104022
Join Card:  100.00 = outer (1000.00) * inner (100.00) * sel (1.0000e-003)
Join Card - Rounded: 100 Computed: 100.00
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
(newjo-save)    [1 0 ]
Final - All Rows Plan:  Best join order: 1
  Cost: 311.6068  Degree: 1  Card: 100.0000  Bytes: 15200
  Resc: 311.6068  Resc_io: 310.0000  Resc_cpu: 49756767
  Resp: 311.6068  Resp_io: 310.0000  Resc_cpu: 49756767

如果不加hint的话，oracle会对比两张表hash join,nested loop,merge join的cost，比较后选择最优，但用了hint后，只会出现nested loop的评估。所以说hint的原理就是给oracle加限制。

那到底用不用hint呢？短期来说Hint也许是个解决问题的最快方案，但它从长远来看会引入风险。无法保证以后数据分布情况不会变化；无法保证以后表结构不会变化，无法保证以后数据库不会升级。环境一直在变化，只有通过计算出各种执行计划的成本，选择最优的计划，才是最科学的。

结论是能不用就不用。