Cost based vs rule based

Cost based vs rule based

Post by Rahul Sharm » Sat, 06 Jan 2001 04:11:39



I know that in SQL Server, the optimizer is always cost-based instead of
rule-based.  But I had a question on the driving table:

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

 
 
 

Cost based vs rule based

Post by Bill Mallar » Sat, 06 Jan 2001 04:54:33


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

 
 
 

Cost based vs rule based

Post by Doo » Sat, 06 Jan 2001 06:55:14


SELECT * FROM a, b, c will give you all columns from a, b and c.

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

 
 
 

1. cost based optimizer vs rule based optimizer

Hi
Does anyone have any opinions on which optimizer to use?
Are they comparable? Is one always better than the other?  
Does it depend on the database ( volume, rate of change)

Any info appreciated.
BTW we are using oracle 8.0.3 under NT.

Thanks
Kel Bahi
Creo Products

2. How can I use oci driver on client machine without install oracle client

3. rule based or cost based?

4. Web Database Survey REPOST

5. Cost-Based or Rule-Based?

6. JOB WANTED

7. optimizer: rule vs cost based

8. how do u design an access database?

9. Cost vs rule based optimizer

10. Cost versus Rule based Optimizer

11. RULE or COST based???

12. switch from rule to cost based optimizer

13. Does SQL 2000 offer Server based Rules similar to the Rules Wizard