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: Rule-based optimizer wins big over CBO - why?

Re: Rule-based optimizer wins big over CBO - why?

From: <ctcgag_at_hotmail.com>
Date: 14 Jul 2004 15:55:51 GMT
Message-ID: <20040714115551.519$tE@newsreader.com>


jason.buchanan_at_gmail.com (Jason Buchanan) wrote:
> I'm faced with a problem that I cannot identify or solve. For a
> mysterious reason the RBO beats the CBO in stunning ways for the query
> shown below.

They are only a factor of 40 apart, that is not so stunning. 4000, now that is stunning. :)

>
> With the RBO the query is completed in a half second but with CBO it
> takes 20 seconds or more. I have gathered system statistics as well
> as statistics against the indexes in play to no avail.
>

...
>
> Any advice or explanation would be greatly appreciated.
>
> The query:
>
> SELECT DISTINCT t1.art_id, t1.publish_date
> FROM article t1, article_taxonomy t2
> WHERE t2.art_id = t1.art_id
> AND t2.taxonomy_element_id IN
> (14051,

...
> )
> AND t1.publish_date <=
> TO_DATE ('2004-07-12 09:00:00', 'YYYY-MM-DD
> HH.MI.SS')
> ORDER BY t1.publish_date DESC

if you change the in list to equality with one of those values and run the query under CBO, what is the explain plan and cost for that?

I thought there was some kind of inlist iterator hint, but now I can't find it. I would try a First_rows hint, or maybe a use_concat hint.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB
Received on Wed Jul 14 2004 - 10:55:51 CDT

Original text of this message

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