Re: oracle 8.1.7 SQL tuning

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Mon, 1 Oct 2001 18:30:16 +0200
Message-ID: <trh7fvh6m0tmfe@news.demon.nl>

"Jon" <jonathan.harvey_at_cerillion.com> wrote in message news:1001934612.4575.0.nnrp-07.c3adc012_at_news.demon.co.uk...
> I have (mistakenly) been reading the documentation and it seems to imply
> that the order at which you specify the joins affects the performance
> because it specifies to Oracle which table is to used as the driving table
> for the join. Can anyone please correct me or add details to this
seemingly
> surprising feature. Thanks in advance.
>
>

No, the order of the tables in the from clause will be used *only* when there is no access path available which is evidently better than all others. By design, the order of the tables in the CBO is far less important than the order of the tables in RBO, as CBO uses I/O estimates only. If you want to 'influence' the optimizer, and you know the desired join order, you should place the driving table left most, and use the /*+ ordered */ hint.
For RBO, you should reverse the order of the tables, as RBO parses from right to left.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Mon Oct 01 2001 - 11:30:16 CDT