The "ordered" hint is extremely useful for cutting-down query parse time and ensuring proper table join order for static tables and queries.

Good article on using the ordered hint

[@more@]/* LEADING */ Hint – specifies the set of tables to be used as the prefix in the execution plan.

The "leading" hint is ignored if the tables specified cannot be joined first in the order specified
If you specify two or more conflicting LEADING hints – all of them are ignored
The ORDERED hint overrides all LEADING hints

/* ORDERED */ Hint – Oracle joins tables in the order in which they appear in the FROM clause

The optimizer normally chooses the order in which to join the tables, but it's time-consuming and wrong if you have bad CBO stats (especially histograms)
You may want to specify the "ordered" hint if you know something about the number of rows selected from each table that the optimizer does not

The Oracle documentation notes the difference between the "ordered" and "leading" hints:

The LEADING hint causes Oracle to use the specified table as the first table in the join order.

If you specify two or more LEADING hints on different tables, then all of them are ignored. If you specify the ORDERED hint, then it overrides all LEADING hints.

#####################################################

"The LEADING hint specifies the set of tables to be used as the prefix in the execution 
plan. "

What does this statement mean by "prefix in the execution plan"

Could you please clarify

thanks

and we said...

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)

Oracle leading vs. ordered hints

and we said...

相關文章