the driving tables, the first tables accessed.

when you join t1 to t2 to t3 to t4 we could go

t3 -> t4 -> t2 -> t1
t1 -> t2 -> t3 -> t4
t4 -> t3 -> t2 -> t1

and so on -- leading says "use this table to start the join chain"


ops$tkyte@ORA9IR2> create table t1 ( x int, y int );

Table created.

ops$tkyte@ORA9IR2> create table t2 ( x int, y int );

Table created.

ops$tkyte@ORA9IR2> create table t3 ( x int, y int );

Table created.

ops$tkyte@ORA9IR2> create table t4 ( x int, y int );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select /*+ leading( t3 ) */ *
2 from t1, t2, t3, t4
3 where t1.x = t2.y
4 and t2.x = t3.y
5 and t3.x = t4.y
6 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=82 Bytes=8528)
1 0 HASH JOIN (Cost=11 Card=82 Bytes=8528)
2 1 HASH JOIN (Cost=8 Card=82 Bytes=6396)
3 2 HASH JOIN (Cost=5 Card=82 Bytes=4264)
4 3 TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=82 Bytes=2132)
5 3 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=2132)
6 2 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=2132)
7 1 TABLE ACCESS (FULL) OF 'T4' (Cost=2 Card=82 Bytes=2132)

Here we said "start with t3", So Oracle is going to drive with T3, join it to T2, join that to T1 and then join all of that with T4...


ops$tkyte@ORA9IR2> select /*+ leading( t4 ) */ *
2 from t1, t2, t3, t4
3 where t1.x = t2.y
4 and t2.x = t3.y
5 and t3.x = t4.y
6 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=82 Bytes=8528)
1 0 HASH JOIN (Cost=11 Card=82 Bytes=8528)
2 1 HASH JOIN (Cost=8 Card=82 Bytes=6396)
3 2 HASH JOIN (Cost=5 Card=82 Bytes=4264)
4 3 TABLE ACCESS (FULL) OF 'T4' (Cost=2 Card=82 Bytes=2132)
5 3 TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=82 Bytes=2132)
6 2 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=82 Bytes=2132)
7 1 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=82 Bytes=2132)



ops$tkyte@ORA9IR2> set autotrace off

Now we said to start with T4, and Oracle goes T4 to T3 to T2 to T1....