ordered表示依據from后面寫的表的順序來做聯結~
寫hints，分開些思路清晰~ ordered后
from t1 ,t2 ,t3 ,t4說明首先使用t1做驅動表來連接t2,如何連接呢？看后面的hint
use_hash(t2)
代表連接t2的方式是hash_join;然后用use_hash(t3)表示連接t3的方式是hash-join，那么誰作build表呢？看后面的swap_join_inputs(t3)代表t3作build表和t1-t2的結果集作連接....依此類推~
標準的hint就應該這么寫~
use_hash(x,y,z)這樣的寫法是不規范，這樣只是說出了x,y,z地聯接方式
ordered 是陳舊的hints,leading是用來代替ordered的~
leading不要求sql的寫法(from后面的順序不要求)，直接可以在leading中定義連接順序~
leading和ordered不能一起使用，也沒必要一起使用~
至于"能不能舉個Ordered不能實現而要用Leading的例子"，上面已經說了，from后面的不能被修改的情況下可以使用leading.用法google一下,一大堆文檔
針對leading使用：
10g中對leading做了加強~
可以直接在后面寫多表的連接順序了，也就是說使用leading不需要from后面的固定順序了
MYDB@MYDB10G >select
2
/*+
3 leading(t1 t2 t3 t4)
4 use_hash(t2)
5
use_hash(t3)
6 swap_join_inputs(t3)
7 use_hash(t4)
8
no_swap_join_inputs(t4)
9 */ * from t3,t4,t2,t1
10 where
t1.object_id=t2.object_id
11 and t2.object_name=t3.object_name
12 and
t3.owner=t4.owner
13 and t4.owner='MYDB'
14 /
已用時間: 00: 00:
00.01
執行計劃
----------------------------------------------------------
Plan
hash value:
3494725078
-----------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 2069 | 179K| 57 (4)| 00:00:01
|
|* 1 | HASH JOIN | | 2069 | 179K| 57 (4)| 00:00:01
|
|* 2 | HASH JOIN | | 50 | 3100 | 38 (3)| 00:00:01
|
|* 3 | TABLE ACCESS FULL | T3 | 40 | 1080 | 18 (0)| 00:00:01
|
|* 4 | HASH JOIN | | 82 | 2870 | 20 (5)| 00:00:01
|
| 5 | TABLE ACCESS FULL| T1 | 11584 | 248K| 16 (0)| 00:00:01
|
| 6 | TABLE ACCESS FULL| T2 | 82 | 1066 | 3 (0)| 00:00:01
|
|* 7 | TABLE ACCESS FULL | T4 | 41 | 1107 | 18 (0)| 00:00:01
|
-----------------------------------------------------------------------------
Predicate
Information (identified by operation
id):
---------------------------------------------------
1 -
access("T3"."OWNER"="T4"."OWNER")
2 -
access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
3 -
filter("T3"."OWNER"='MYDB')
4 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
7 -
filter("T4"."OWNER"='MYDB')
MYDB@MYDB10G >select
2
/*+
3 ordered
4 use_hash(t2)
5
use_hash(t3)
6 swap_join_inputs(t3)
7 use_hash(t4)
8
no_swap_join_inputs(t4)
9 */ * from t1,t2,t3,t4
10 where
t1.object_id=t2.object_id
11 and t2.object_name=t3.object_name
12 and
t3.owner=t4.owner
13 and t4.owner='MYDB'
14 /
已用時間: 00: 00:
00.00
執行計劃
----------------------------------------------------------
Plan
hash value:
3494725078
-----------------------------------------------------------------------------
|
Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-----------------------------------------------------------------------------
|
0 | SELECT STATEMENT | | 2069 | 179K| 57 (4)| 00:00:01
|
|* 1 | HASH JOIN | | 2069 | 179K| 57 (4)| 00:00:01
|
|* 2 | HASH JOIN | | 50 | 3100 | 38 (3)| 00:00:01
|
|* 3 | TABLE ACCESS FULL | T3 | 40 | 1080 | 18 (0)| 00:00:01
|
|* 4 | HASH JOIN | | 82 | 2870 | 20 (5)| 00:00:01
|
| 5 | TABLE ACCESS FULL| T1 | 11584 | 248K| 16 (0)| 00:00:01
|
| 6 | TABLE ACCESS FULL| T2 | 82 | 1066 | 3 (0)| 00:00:01
|
|* 7 | TABLE ACCESS FULL | T4 | 41 | 1107 | 18 (0)| 00:00:01
|
-----------------------------------------------------------------------------
Predicate
Information (identified by operation
id):
---------------------------------------------------
1 -
access("T3"."OWNER"="T4"."OWNER")
2 -
access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
3 -
filter("T3"."OWNER"='MYDB')
4 -
access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
7 - filter("T4"."OWNER"='MYDB'
