Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Can't think of a subject...

Re: Can't think of a subject...

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 4 Feb 2004 06:16:57 -0800
Message-ID: <2687bb95.0402040616.26f839a1@posting.google.com>


kplasun_at_hotmail.com (kris p) wrote in message news:<db13b49.0402031808.487873ef_at_posting.google.com>...
> A question about SQL statement structure...
>
> I have a query with a number of joins and my question
> is does the order of the tables listed after the FROM
> have any impact in which order the tables are joined?
>
> What about the order of the statements after the WHERE
> clause, do they affect the order in which a statement
> is executed?
>
> Thanks,
> kristoff plasun

The answer is it depends. I have seen where re-arranging the where clause conditions without making any other changes has resulted in a plan change. The Cost Based Optimizer, CBO, will make so many passes looking for plans. The number of tables, the statistics, and the provided conditions along with various database parameter settings will interact to produce an optimzer plan.

As the optimizer has improved since version 7.0 I think you are less likely to see changes in the plan by rearranging the existing where clause conditions though even a minor change to one of the conditions is a new SQL and may provide different information to the optimizer.

I would recommend that you code your FROM clause tables in desired join order as a form of documentation and to allow the use of the ORDERED hint where the CBO appears to be making a bad plan choice.

HTH -- Mark D Powell -- Received on Wed Feb 04 2004 - 08:16:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US