Re: Oracle Pro*C 1.3

From: Michael P. Stein <mstein_at_access.digex.net>
Date: 13 Dec 1993 09:30:36 -0500
Message-ID: <2ehuac$sb_at_access.digex.net>

Michael Stowe <Michael.Stowe%f573.n115.z1.fidonet.org_at_fidogate.nuars.nwu.edu> wrote:
>*** Quoting Ywleung_at_Ouray.Denver.Colorado.Edu to All dated 12-07-93 ***
>> He also mentioned if there are several table in the from clause, the
>> rightmost table will be used first when the where clauses are evaluated.
>
>This is 100% wrong. If there are several tables in the from clause, the
>largest possible index starting with the first arbitrary table (actually based
>on physical location, so it is NOT smart to rely on consistency).

    Only 98% wrong. The rule-based optimizer chooses on the basis of

indices, but if no index exists to drive the query, or there is a tie in 
scoring, the position of the tables in the FROM clause is used to break 
the tie, in right-to-left order.  This was true in V6 and is also 

mentioned on p. 2-29 of the V7 beta release Performance Tuning Guide. (I happen to have one of those at my desk, but not the production version; however, I doubt that anything other than perhaps the page number changed between beta and production in this particular area.) It's just that the number of cases where this tiebreaker comes into play is (or should be) pretty limited in a well-designed database/application coupling.

   The cost-based optimizer in V7 allows you to give a hint to process a join in left-to-right order, just to confuse the issue further. :)

-- 
Mike Stein			The above represents the Absolute Truth.
POB 10420			Therefore it cannot possibly be the official
Arlington, VA  22210		position of my employer.

Received on Mon Dec 13 1993 - 15:30:36 CET