Re: Puzzing performance results with SQL

Samuel Becker wrote:

> Hi there
>
> I use Oracle 8.1.7 and i have a strong difference of performance
> when i execute the two following SQL queries which are very similar:
>
> Query1:
>
> SELECT t1.TITLE, t1.ISBN, t1.PRICE, t1.PUBLISHER_ID
> FROM TAUTHOR t0, TBOOK_AUTHOR t2, TBOOK t1
> WHERE ((t0.LAST_NAME LIKE '%XXX-AUTHOR-3_1-24_1-YYY%')
> AND ((t2.ISBN = t1.ISBN) AND (t0.AUTHOR_ID = t2.AUTHOR_ID)))
>
>
> Query2:
> SELECT t1.TITLE, t1.ISBN, t1.PRICE, t1.PUBLISHER_ID
> FROM TBOOK t1, TAUTHOR t0, TBOOK_AUTHOR t2
> WHERE ((t0.LAST_NAME LIKE '%XXX-AUTHOR-3_1-24_1-YYY%')
> AND ((t2.ISBN = t1.ISBN) AND (t0.AUTHOR_ID = t2.AUTHOR_ID)))
>
> The "query1" takes 1 second to execute whereas "query2" takes less than
> 300ms !
> The only difference between these two queries is just in the "FROM" clause !
>
> Any idea ?
> Thanks.

What's the value of OPTIMIZER_MODE or OPTIMIZER_GOAL (session) ? It looks like the good old RBO (Rule Based Optimizer) is doing harm, here... If I recall correctly, it joins the tables in reverse FROM order. Received on Fri Jan 30 2004 - 10:08:31 CST