Re: Reordering the from-clause changes the query plan?

fredrik.groups_at_gmail.com wrote:
> Hi all!
>
> I am using Oracle 10.1.0.3 on a Red Hat Enterprise Linux server (see
> below for kernel version and so on).
>
> I have a complex query (joining quite a few tables) that is using
> full table scans in some places where I think that access by indexes
> would be more efficient. While experimening, I noticed that changing
> the order of the tables in the from-clause sometimes caused the query
> plan to change.
>
> I was under the impression that changing the order of the tables
> in the from-clause should not change the execution of the query
> while using the cost-based optimizer. Is this not correct?
>
> A (hopefully) complete test case follows below. Since this is my
> first usenet post in several years, I apologize in advance if I
> have messed up the formatting.
>
> The queries below are (obviously) simplified versions of the
> original queries. Right now, I am most interested in the
> possible reasons for the changing plan when only reordering
> the from-clause.
>
> Thank you in advance for any ideas or pointers to documentation.
>
> /Fredrik

< SNIP >

I have noticed the same sort of thing on: Oracle 10.1.0.3.0 RAC (egenera 2-cpu blades) Red Hat Linux XXX.XXXX.XXX 2.4.21-32.EL #1 SMP Fri Apr 15 21:02:58 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux

Evan Received on Mon Aug 15 2005 - 13:57:06 CDT