Re: Puzzing performance results with SQL

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Fri, 30 Jan 2004 16:24:30 GMT
Message-ID: <2zvSb.185$9p5.121@nwrddc02.gnilink.net>

Running RBO? tables not analyzed?

check the optimizer_goal .. and then check if all tables are analyzed.

Anurag

"Samuel Becker" <s_becker_at_voila.fr> wrote in message news:acf86b1.0401300800.1834aae6_at_posting.google.com...
> 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.
Received on Fri Jan 30 2004 - 10:24:30 CST