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

without a tkprof report of a level 12, 10046 trace we can only guess as what is really going on under the covers. Though it looks to me like the optimizer is looking to optimizer for throughput, whereas you say the query runs quicker when the optimizer is looking to show results as quickly as possible.

I assume that your times are based on retreiving all expected rows?

If you want to get the optimizer to optimize for "show me some results as quickly as possible"

Have a look at the following (good) hints:

• ALL_ROWS
• FIRST_ROWS(n)

See:

http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a87503/hintsref.htm#4896

For info on 10046 tracing go to http://www.hotsos.com and have a look at "How to Activate Extended SQL Trace"

Hope this helps,

Mat.

jason.buchanan_at_gmail.com (Jason Buchanan) wrote in message news:<5f258456.0407131534.6bb4042d_at_posting.google.com>...
> 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.
>
> 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.
>
Received on Thu Jul 15 2004 - 18:24:14 CDT