Re: Driving Table in the execution plan

Reply below:

<sweidanz_at_yahoo.com> wrote in message
news:0XWk7.1020$Qq2.1330_at_nsw.nnrp.telstra.net...
> Hi All,
> I was reading one of Oracle books and was surprised to find out that in
> Oracle the order of the table names in the FROM clause can make a
> difference in the execution plan.
> Basically, the following as quoted:
>
> "The execution plan chosen by Oracle determines a driving table based on
> the order of the table names in the FROM clause.
> Rule-based: Oracle optimiser chooses the last table as the driving table.
> Cost-based : Oracle optimiser chooses the first table as the driving
> table"
>
> Question1: Is that true in Oracle8i or it was on previous versions?

The above statements are not entirely true. To understand it, you will need to dig a little
deeper into how Rule Based and Cost Based optimizers work. A good start will be to get the Oracle Concepts documentation from OTN. In brief: Rule Based Optimizers develop an execution plan based on a set of rules which are prioritized internally. For example: if you want to join tables A and B together, then the order in which RBO will join them depends on what indexes it can use etc.
In case RBO cannot decide with certainty, which order to join the tables, it then joins them from "last" to "first".
Cost Based Optimizers on the other hand develop execution based on table statistics available. Thus CBO is able to make a more informed decision. IF CBO is unable to decide the order in which to join the tables, it then joins them "first" to "last".

In reality the above statements have been seen to hold true more for RBO than for CBO.

> And as recommended by the Author:
> "for the cost-based method order the tables with the smallest table first"
>
> Question2: I don't really understand this recommendation. Why the smallest
> table should be the driving table?

This advice should not be taken as a command from the higher authority :) Though this statement holds true for most of the cases (BUT not always). Tuning essentially means reducing the amount of Physical I/O done by the query.
In most cases following the above advice will achieve a lower Physical I/O.

For example: Consider join of Tables A (Rows = 2) to Table B (Rows = 10000).
Assuming Nested Loops Join:
If you join them A to B then essentially Oracle will have to scan Table B (or its Index)
only twice.
However if you join then B to A then Oracle will have to scan Table A (or its index)
a 10000 times.
So joining A to B is definitely is a better choice.

Now consider join of Tables C (Rows = 1000) to Table D (Rows = 10000). Assume that employing of an index on D filters most of its rows while employing index on C will retain most of its rows. We have a situation when after index filter, we are essentially joining 1000 rows of C to 2 Rows of D.
In this case, although D is physically the biggest table, it should be considered as the smallest table in terms of the number of rows we will be joining. This is what I mean by "not taking the above statement literally". In this case we will join D to C.

• You should use Tkprof to tune your queries. Nothing can assist you better in your tuning quest than Tkprof.

..... additions and corrections welcome.

Anurag

>
>
> Thanks,
> ZS
Received on Tue Sep 04 2001 - 09:35:16 CDT