Very Computer

when I have a query like
select * from a,b,c where a.col1 = b.col1 and b.col2 = c.col2
what is the driving table ?? I believe it's the first table i.e. a. If that
is the case, then isn't it rule based by default ??

In Oracle, the driving table is the last table in the list, so in the above
mentioned query, c is the driving table (rule based).  For cost based, based
on the statistics it will select the driving table.

Thanks,

Rahul

Quote:> when I have a query like
> select * from a,b,c where a.col1 = b.col1 and b.col2 = c.col2
> what is the driving table ?? I believe it's the first table i.e. a. If
that
> is the case, then isn't it rule based by default ??

A couple of additional notes:

1.  To force a rule-based approach to this query, you cost SET FORCEPLAN ON,
which would process the query as you originally suggested; table "a" first,
then "b" and "c".

2.  You might want to make explicit the implicit relationship "a.col2 =
c.col2".  The optimizer might be able to form a more efficient query plan
using this information.

Bill Mallary

SELECT a.* FROM a, b, c will give you all a columns that meet the WHREE
clause you specified.

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group

Quote:> > when I have a query like
> > select * from a,b,c where a.col1 = b.col1 and b.col2 = c.col2
> > what is the driving table ?? I believe it's the first table i.e. a. If
> that
> > is the case, then isn't it rule based by default ??

> Assuming that the optimizer chooses a nested loop stratefy, the driving
> table could be any one of the three tables in question, depending on the
> costs associated with each table in the query.

> A couple of additional notes:

> 1.  To force a rule-based approach to this query, you cost SET FORCEPLAN
ON,
> which would process the query as you originally suggested; table "a"
first,
> then "b" and "c".

> 2.  You might want to make explicit the implicit relationship "a.col2 =
> c.col2".  The optimizer might be able to form a more efficient query plan
> using this information.

> Bill Mallary