The ordered and leading principles in Oracle hint are very good posts ____oracle

Question: Ask hint to do the spelling
I have a SQL add as follows hint, the purpose is to specify Hash_join mode.

Select/*+ordered Use_hash (a,b,c,d) *
From A,b,c,d
Where ...

which
A relates only to B, B relates only to C, B relates only to C, and C relates only to D,
Order of magnitude: a:1000, b:100 million, C: 8 million, d:100 million


The execution plan is:
Hash Join
---Hash Join
-----Hash Join
------A
-----b
-----C
---d

Given the small size of the D table, can I do it as a driver table, and a,b,c the result of association as a prob appearance?
Through ordered there seems to be no way to control this, add can only be a as a driver table load memory, b as a prob table associated with it,
The results are then used as the driver table, and so on.

Can you use leading? Please give me the grammar, thank you.
Answer:In Oracle 10g

The Hash_join can force a build table by no_swap_join_inputs/swap_join_inputs, with leading or ordered to control the sequence of connections before multiple tables.

For example, T1,t2,t3,t4 a total of 4 tables to do Hash_join
Can be achieved by ordered+no_swap_join_inputs/swap_join_inputs.
Like what
If you want to achieve

(T3 hash-join (T1 hash-join T2)) Hash-join T4

The T1 acts as a build table and T2 Hash_join, then T3 as the result set for the build table and T1,t2, hash_join the result set of the t3,t1,t2 as a scale and T4
SQL can be written as

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
One where t1.object_id=t2.object_id
T2.object_name=t3.object_name
T3.owner=t4.owner
T4.owner= ' MYDB '
15/
Time used: 00:00:00.07

Execution plan
----------------------------------------------------------
Plan Hash value:3494725078

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | tempspc| Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 |      SELECT STATEMENT |  |   2137 |       801k|   | 182 (2) | 00:00:03 |
|* 1 |      HASH JOIN |  |   2137 |       801k|   | 182 (2) | 00:00:03 |
|* 2 |      HASH JOIN |    | 52 |       14976 |   | 167 (2) | 00:00:03 |
|* 3 | TABLE ACCESS Full |    T3 |  40 |       3840 |    | 15 (0) | 00:00:01 |
|* 4 |      HASH JOIN | |  11651 |  2184k|   1232k| 151 (1) | 00:00:02 |
|     5 | TABLE ACCESS full| T1 |  11651 |       1092k|    | 15 (0) | 00:00:01 |
|     6 | TABLE ACCESS full| T2 |  11652 |       1092k|    | 15 (0) | 00:00:01 |
|* 7 | TABLE ACCESS Full |    T4 |  41 |       3936 |    | 15 (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 ')

Note
-----
-Dynamic sampling used for this statement
Description:

Ordered represents the connection based on the order of the tables written from the back.
Write hints, separate some ideas clear ~ ordered from T1, T2, T3, T4 description First use T1 to do the driver table to connect T2, how to connect it. Look behind the hint Use_hash (T2)
The way to represent the connection T2 is Hash_join, and then Use_hash (T3) to indicate that the connection T3 is Hash-join, then who makes the build table. Look at the back of Swap_join_inputs (T3) on behalf of the T3 as a build table and t1-t2 result set for the connection .... And so forth ~

Standard hint should be so written ~ Use_hash (x,y,z) Such writing is not standard, so just say the x,y,z to join Way ordered is old hints,leading is used to replace ordered ~ leading does not require the writing of SQL (from the following order not required), directly can be defined in the leading connection order ~ leading and ordered can not be used together, there is no need to use together ~
As for the example of "whether or not a ordered cannot be achieved and a leading is to be used", it has been said that leading can be used in the case where the from follows cannot be modified. Use Google, a lot of documents.for leading use:10g in the leading did strengthen ~ can be directly in the back of the table to write the sequence of joins, that is, the use of leading does not need a fixed order from the back of the
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
where t1.object_id=t2.object_id
One and T2.object_name=t3.object_name
T3.owner=t4.owner
T4.owner= ' MYDB '
14/
Time used: 00:00:00.01

Execution plan
----------------------------------------------------------
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
where t1.object_id=t2.object_id
One and T2.object_name=t3.object_name
T3.owner=t4.owner
T4.owner= ' MYDB '
14/
Time used: 00:00:00.00

Execution plan
----------------------------------------------------------
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 ')