alibaba@OCN>select /*+ ordered use_hash(t_max t) */ * from t,t_max where t.object_id = t_max.object_id ;
135168 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=882 Card=99830 Bytes
=35339820)
1 0 HASH JOIN (Cost=882 Card=99830 Bytes=35339820)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=17 Card=10960 Bytes=193
9920)
3 1 TABLE ACCESS (FULL) OF 'T_MAX' (Cost=17 Card=99827 Bytes
=17669379)
alibaba@OCN>alibaba@OCN>select /*+ ordered use_hash(t_max t) */ * from t_max,t where t.object_id = t_max.object_id ;
135168 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=548 Card=99830 Bytes
=35339820)
1 0 HASH JOIN (Cost=548 Card=99830 Bytes=35339820)
2 1 TABLE ACCESS (FULL) OF 'T_MAX' (Cost=17 Card=99827 Bytes
=17669379)
3 1 TABLE ACCESS (FULL) OF 'T' (Cost=17 Card=10960 Bytes=193
9920)
但是3个表的呢??比如这里我想控制 t_max,t 的join的集合作为 drive table 去和 t_min 进行 hash join ,如何控制?
alibaba@OCN>select /*+ ordered use_hash(t_max t) */ * from t_max,t,t_min where t.object_id = t_max.object_id and t_max.object_id = t_min.object_id;
63984 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1472 Card=43398 Byte
s=23044338)
1 0 HASH JOIN (Cost=1472 Card=43398 Bytes=23044338)
2 1 TABLE ACCESS (FULL) OF 'T_MIN' (Cost=9 Card=3999 Bytes=7
07823)
3 1 HASH JOIN (Cost=548 Card=99830 Bytes=35339820)
4 3 TABLE ACCESS (FULL) OF 'T_MAX' (Cost=17 Card=99827 Byt
es=17669379)
5 3 TABLE ACCESS (FULL) OF 'T' (Cost=17 Card=10960 Bytes=1
939920)
这是三个表的
SQL> select /*+ ordered use_hash(t_max t) */ *
2 from t_max,t,t_min where t.object_id = t_max.object_id
3 and t_max.object_id = t_min.object_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=747 Card=348800 Bytes=80572800)
1 0 HASH JOIN (Cost=747 Card=348800 Bytes=80572800)
2 1 TABLE ACCESS (FULL) OF 'T_MIN' (Cost=32 Card=40000 Bytes=3080000)
3 1 HASH JOIN (Cost=272 Card=87200 Bytes=13428800)
4 3 TABLE ACCESS (FULL) OF 'T_MAX' (Cost=63 Card=80000 Bytes=6160000)
5 3 TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=10900 Bytes=839300)
SQL> select /*+ leading(t_max) use_hash(t_max t) */ *
2 from t_max,t,t_min where t.object_id = t_max.object_id
3 and t_max.object_id = t_min.object_id;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=747 Card=348800 Bytes=80572800)
1 0 HASH JOIN (Cost=747 Card=348800 Bytes=80572800)
2 1 HASH JOIN (Cost=272 Card=87200 Bytes=13428800)
3 2 TABLE ACCESS (FULL) OF 'T_MAX' (Cost=63 Card=80000 Bytes=6160000)
4 2 TABLE ACCESS (FULL) OF 'T' (Cost=10 Card=10900 Bytes=839300)
5 1 TABLE ACCESS (FULL) OF 'T_MIN' (Cost=32 Card=40000 Bytes=3080000)
1./*+use_nl(t2,t) */ 仅仅提示oracle走nest loop,没有指定任何驱动表
2./*+ ordered use_nl(t2,t) */走nest loop,order指定以from后面的第一个表做为驱动表。
3./*+ leading(t2) use_nl(t) */ 走nest loop,提示t2为驱动表。
结论:use_nl不能让优化器确定谁是驱动表谁是被驱动的表,use_nl(t,t2)也没有指出哪个是驱动表,这时候我们需要使用ordered,leading来强制指定驱动表,以达到我们的目的。
ordered这个HINT是表示表的连接顺序按from后面表的顺序依次连接的,有多张表关联的时候也是走这个顺序,在多表关联的时候稳定执行计划这个HINT用得比较多,同时要注意from后面表的连接顺序不能随意更改。leading这个HINT主要是用来明确指定连接中的驱动表。另外还有一个比较常用的HINT是swap_join_inputs,比如有A、B、C三张表需要关联,a.col=b.col and a.col1=c.col1,这时候理想的执行计划是A和B先HASH JOIN,得出来的结果集再和C关联,由于相对来说C和A、B得出的结果集相比还是小表,显然用C来做为二次HASH JOIN的驱动表比较合适,那么就可以用swap_join_inputs来控制了。