Re: Table Joining Sequence

In article <8i4588$2022$1_at_adenine.netfront.net>,   Norris <jcheong_at_cooper.com.hk> wrote:
> It seems that my Oracle can't do this. I found the if I put the
empty table at the end of FROM clause, the query can speed up a lot of time.
>
> fangliu_at_my-deja.com wrote:
> > Hi, Norris:
 

> > It depends on the optimizaiton stratagy. Most DBMSs have their own
> > aptimizaiton algorithms, and they will re-order the join sequence
 when
> > the sql is executed. So, you need not take care too much of this
> > problem.
 

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> --
> http://www.cooper.com.hk
>

Yeah responses like the one of Fangliu always intend me to think, whether he knows what he is talking about. Algorithms like he describes are being used in Oracle, and they are fallible. In many cases Oracle can´t decide for the correct sequence, and simply takes the last table (Rule based optimizer) or first (Cost Based Optimizer). Your remark betrays you are still using the Rule Based Optimizer. This has been made obsolete since Oracle 7, just because of the algorithms are fallible and no development has been done on it for 7 years. The Cost Based Optimizer is less dependent of correct order of tables in your statement, and supports the Parallel Query Option, Partitioning and a whole lot of other features. The Rule Based Optimizer doesn´t. In short you should get away from using the rule based optimizer. To switch to cost based you´ll need to ANALYZE your tables, dbms_utility.analyze_schema is capable of analyzing them in one go.

Regards

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.