Oracle Logbook

October 4, 2007

Driving table

Filed under: Uncategorized — heliodias @ 2:03 pm

If your database is running on CBO than Oracle can join it in three ways:

1 – Nested Loop
2 – Sort Merge
3 – Hash Join

Driving table only apply to Nested Loop, 
If both tables have index on  join column, And both tables have a filter predicate,
Then Oracle will elect the table that will become smaller after filtering to be the drive table.

eg. 

 select t1.col1,t2.col2 from t1,t2 where t1.col3=t2.col3

where t1.col1=10 and t2.col2=20;

T1 have 10000 rows

T2 have 200000 rows

A lot of people made a mistake thinking that Oracle will elect the smaller one to be the driving table.

So let´s think about it for a moment, what difference between 10000*200000 and 200000*10000

That´s why it doesn´t work like this, 

In fact Oracle will check what table will become % smaller after filtering:

Eg. T1 will have 5,000 rows (50% of reduction)

But T2 will have 20,000 rows (90% of reduction)

So the question is , what is less effort?  5,000*200,000   or 10,000*20,000

Advertisements

5 Comments »

  1. I m happy about this comments. But I want more this.
    1. SELECT clause columns order (where would be driving table position)
    2. FROM clause tables order(where would be driving table position)
    3. WHERE clause filter orders (where would be driving table position)

    Comment by Crazy Oracle — January 22, 2008 @ 11:42 am | Reply

  2. Hi Crazy Oracle,
    The point is :
    on Cost Based Optimizer , doesn´t matter the order, only one thing is important: Correct Statistics.
    so if you write
    select col1,col2 from tab1,tab2 where col3=’x’ and col4=’y’ and col5=col6;

    Oracle Optimizer can start from tab1 or tab2,
    The driven table will the table that make less effort to evaluate the result.

    You can guide Oracle to use a table first (using leading hint)
    select /*+leading(tab2)*/ col1,col2 from tab1,tab2 where col3=’x’ and col4=’y’ and col5=col6;

    But rarelly it is a good thing to do,
    The best thing is adjust the statistics (even using hint to do this)
    Regards

    Comment by heliodias — January 22, 2008 @ 4:48 pm | Reply

  3. MANY THANKS HELIOS,

    I got this what u want to say that.

    1. in HASH JOIN, one table say T1 is fething 6K records, and if i create index on it. then it will use but NESTED LOOP JOIN is performing on it and
    i m getting execution plan as below:

    TABLE ACCESS BY INDEX ROW_ID T1 6k
    INDEX UNIQUE SCAN IDX_T1_C1 6k

    How to identify whether HASH is good without index (only 6k once)or index need to create (but 6k comes twice in explain plan).

    Thanks in advance for ur seggestion.

    Comment by Crazy Oracle — February 7, 2008 @ 5:06 pm | Reply

  4. 1. how to identify from TRACE DETAILS that recursive gets , consistent gets physical reads are reduced but db blocks are increased to some exent.

    so what is the combination of the these parameters reducing means tuning sql.

    Comment by Crazy Oracle — February 7, 2008 @ 5:09 pm | Reply

  5. Hi Crazy Oracle,
    Usually HASH_JOIN is the best solution to join a SMALL table with one BIG table;
    But the best guide to tell if you do the right thing is number of buffer gets (small buffer gets is better, no matter what cost said)

    If you post the query , i can help you more.

    Comment by heliodias — February 7, 2008 @ 6:36 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: