Re: Driving Table in the execution plan

<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?

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?

Thanks,
ZS

Please don't use HTML to post your questions. Many newsreaders (Forte Agent being among them) won't be able to cope with it, and the user will only see garbage.

Question 1
This still holds true
It is an attribute of the optimizer, not of version.

Question 2
This statement is not very accurate.
It should read:
use the table, which will, using any predicates (= conditions in the where clause), return the smallest number of rows. One of the join method is nested loops. For each record in resultset a (let's avoid the word table) it start a retrieval for the rest of query. Consider the emp dept situation.
If dept is driving and only one row of dept is returned (your query reads 'dept.deptno=10'), it will look ony once in emp to retrieve the corresponding emp records
However, if for some reason emp is driving, and emp returns 5 records (your query is 'emp.deptno=10') , it will look 5 times in the dept table to retrieve the corresponding dept., which will use extra resources.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Mon Sep 03 2001 - 22:38:44 CDT