order of joins by optimizer

Hi,

I want to know the order of execution of joins by oracle optimizer.
like if four tables are used in where clause and are joined.
which two tables will be joined first, and what is the role of driving table in this.

Please don't share me any link, please explain me in simple words.

Thanks,
sakthikumarAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
The best way to find out the order of joins is to do an explain plan.

Documentation for that is here -> http://docs.oracle.com/cd/E11882_01/server.112/e41573/ex_plan.htm#PFGRF009

Simply written, you need to do

EXPLAIN PLAN FOR <your sql>;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

This will display the plan and show you the order of the joins.

There is no way to determine the join order based on the order of the tables in the FROM clause.
slightwv (䄆 Netminder) Commented:
I know you said no links and simple words but some times things have already been explained and the link has the information.

Tom Kyte has about as simple explanation about driving tables that I've seen:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:192812348072

It also touches on the joins in general to answer you other question "which two tables will be joined first"
Order of joins hasn't mattered for many years.  It is all about object statistics and the Cos-Based optimizer (CBO).

The Optimizer decides on what to join to what first.  Unless you are silly enough to use hints to attempt to out-think the optimizer.  I've rarely seen the need for hints.
PortletPaulEE Topic AdvisorCommented:
I word of caution about that Tom Kyte reference, it is old. That doesn't invalidate the generic nature of what it says but there is way less (or should be way less) use of any rule based optimization now.

Also note that "The Oracle Optimizer" isn't static and has changed since the year 2000.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark GeerlingsDatabase AdministratorCommented:
Even though that article by Tom Kyte says: "When using CBO -- the order of tables is not relevant", in my experience (and I thought this was documented by Oracle somewhere) the order of tables in the "from" clause *IS* often relevant.  The table order now (with the CBO) should be exactly the opposite of what it was for the Rule-Based Optimizer, that is: put the table first in the "from" clause that you want to be the "driving" table, and list the other tables in relation to that first table.

The "where" clauses should have the "known" value (either a bind variable or a literal, or a value from a table listed earlier in the "from" clause) on the right side of the "=" sign.  And, the "where" clauses are normally evaluated by the optimizer in reverser order (from bottom to top).

Isn't Oracle's query optimizer smart enough to figure things out, regardless of whether you put the "known" value on the right or the left of the "=" sign, and regardless of the top-to-bottom order of the "where" clauses?  Yes, but it has to start somewhere with some assumptions, so the easier we make things for the optimizer, the faster the SQL will perform.

Here is what typical "from" and "where" clauses should look like for a four-table join that you want to use table_A as the driving table for, and have the other tables be accessed in A, B, C, D order:

select ...
from table_A a, table_B b, table_C c, table_D d
where d.primary_key = c.foreign_key
and c.primary_key = b.foreign_key
and b.primary_key = a.foreign_key
and a.primary_key (or indexed column) = bind_variable (or literal value)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
I believe that the order was taken as a suggestion in earlier versions of the optimizer where fewer plans were evaluated before determining the "correct" one.  With the number of permutations now being evaluated, I'm not sure that order is relevant anymore.  Of course, there is no doc to back that up, but that is what I see.

Also, working with a proprietary database that has its own rather complex optimizer, order definitely didn't matter in that.  And while complex, it was not as complex as Oracle's.
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Just consolidating everything.
use the following from SQLPlus

set autotrace on
select * from emp,dept where emp.dept_id=dept.dept_id;
johnsoneSenior Oracle DBACommented:
Why set autotrace on and run the query?  You have to wait for results and if your result set is large that is a total waste of time.  All you need is the plan.  Simply done with an EXPLAIN PLAN.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.