You Asked
Here I mention a snippest of the "Expert Oracle Database 11g Administration By SAM ALAPTI writtern on page number 1053.
but I have some confusion on how oracle affect join order?
-: Book Snippest :-
-------------------
Choosing the Join Order
Once the optimizer chooses the join method, it determines the order in which the tables are joined.
The goal of the optimizer is always to join tables in such a way that the driving table eliminates the
largest number of rows. A query with four tables has a maximum of 4 factorial, or 24, possible ways
in which the tables can be joined. Each such join order would lead to a number of different execution
plans, based on the available indexes and the access methods. The search for an optimal join strategy
could take a long time in a query with a large number of tables, so Oracle depends on an adaptive
search strategy to limit the time it takes to find the best execution plan. An adaptive search strategy
means that the time taken for optimization is always a small percentage of the total time that is taken
for execution of the query itself.
Now Before going to question is suppose I have four tables like Item, Customer, Order, Employee.
Item table contain unique 10,000 Rows.
Customer table contain unique 100,000 Rows.
Order table contain order given by customer and an one customer contain multiple Items and have 500,000 Rows.
Employee table contain unique data who ships customer items and having 150,000 Rows.
Now My question is Suppose I use following method to join a table like Item,Employee,Order and Customer in a from clause.
so how oracle decide joining method like we supply in a form cluase or depends on a where clause.
Or First scan Item table then result of Item table scan with Employee and so on ... at last stage result set of Item, Employee, Order join with customer.
when oracel automatically decide optimal join order?
and Connor said...
Whilst the contents are somewhat cryptic, its good to have a look inside an 10053 trace (an optimizer trace). So you can do something like:
SQL> alter session set events = '10053 trace name context forever, level 1';
SQL> explain plan for [your sql]
In the trace file, you'll see stuff like:
"SINGLE TABLE ACCESS PATH"
where we work out what it would cost to start with each of the tables based on its size and the predicates you've provided. So for each of of item, customer, employee, order, we'll have an estimate of what it would cost to start with each.
Then through the trace file, you'll see things like:
Join order[1]: item, customer, order, employee
Join order[2]: item, order, customer, employee
...
Join order[13]: customer, item, employee, order
etc etc...where we go through the potential joins and come up with a decision on which we think is best.
Now we have all sort of 'short cuts' in there to make this efficient.
For example, let's say at the end of "join order 1", we think it will cost 500 to do the query in that order. When we get to join order 13, lets say we previously worked out the cost of *starting* with customer is 620. Then we wouldnt bother even looking at the joins, because if we are starting with 620, the cost of joining will simply get higher and we *already* have a join which in total costs only 500.
etc etc.
Hope this helps.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment