Re: Table Joining Sequence

From: Norris <jcheong_at_cooper.com.hk>
Date: 2000/06/14
Message-ID: <8i89ha$1p3m$2@adenine.netfront.net>#1/1

Yes, I think my problem is due to the incorrect statistics. But how can I know when I need to run analyze table compute statistics?

sybrandb_at_my-deja.com wrote:
> 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.

-- 
http://www.cooper.com.hk

Received on Wed Jun 14 2000 - 00:00:00 CDT