Thanks,
Thanks for this information. I am more understanding of
the underlying causes for what was going on now.
I was the one that probably caused you to misinterpret
the what my real issue was. Sorry for that.
I am swamped here and have to brief. Thank You for
your time.
I enjoy referencing your book. Very nice work.
Mike
-----Original Message-----
Sent: Saturday, January 25, 2003 5:39 AM
To: Multiple recipients of list ORACLE-L
Michael,
Somewhere along the line, I mis-interpreted your
note and thought you were saying that CBO was
ignoring the hint - hence the irrelevant comments.
RBO uses the index for this query because
that's one of the rules.
The CBO under ALL_ROWS optimisation is going
to compare figures (in your case) like:
leaf_blocks * optimizer_index_caching +
clustering_factor * optimizer_index_cost_adj.
and
table_blocks / 6.5 +
cost of sort
Since clustering_factor bottoms out at table_blocks,
your optimizer_index_cost_adj would probably have to
be less than 15 before you had much chance of
using the index.
You state elsewhere that the timing difference is fractions
of a second against seven seconds for "thousands of rows".
I wonder if this is the giveaway - I would be a little surprised
if your client can actually acquire thousands of rows in
a fraction of a second, although it might acquire the first
few in that time. If this is the case, you might like to find
out how long it really takes to acquire all the rows using
the index - this might make the scan and sort path seem
more reasonable..
If you switch the session's optimizer_mode to FIRST_ROWS,
you will find that the optimizer will use the index - there is
a heuristic hard-coded in to FIRST_ROWS (introduce in 8.1.6
I think) that always uses an index to avoid a sort. It is
possible the FIRST_ROWS is actually the nominally correct
optimizer mode for this bit of the application.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )
____UK_______March
____USA_(FL)_May
Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )
____USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johnson, Michael
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
