An interesting point I noted in some 10053 traces recently
is that Oracle can use a predictive readahead mechanism
when doing an index full scan (as opposed to an index
fast full scan). This still appears to do single block reads,
however. There is also a hidden parameter named something
like _ncmb_read_count, with an event in the 10,000 range
you can set, which refers to 'non-contiguous multiblock
read count'. This may be related. It's another of those little
details that are on the todo list to investigate.
There has been a thread on metalink about a similar problem,
involving a PK and bitmap indexes where the PK index is
chosen despite producing a higher cost.
The thread is in the Enterprise Server forum, and called:
Optimizer uses high cost primary key instead of bitmap index
The last report was:
Here's a note from development in the bug:
optimizer gave a preference to a concatenated index which as at
least 2 predicates in the query.
Workaround: event 10112 to disable probing of b*tree index in
bitmap plan.
This leads to another random thought - if there is
a condition in the WHERE clause, which covers
just one column in the index, though not the leading
column, is it possible that 8.1.7 has some early
code relating to the path 'index skip scan' - which
nevertheless gets reported as 'index full scan' ?
Otherwise, the thought about having statistics
for the table which make Oracle think it is a
big table, but statistics for the index that make
Oracle think it is a small index seems a possible
cause - but that shouldn't be possible after a
table rebuild. (Unless someone's playing with
the dbms_stats package).
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Author of:
Practical Oracle 8i: Building Efficient Databases
-----Original Message-----
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 14 March 2002 06:54
You are right Fast Full Scans use multiblock IO. Other index scans do
not. That had slipped by me. So my speculation is moot. If it was
an FFS, I could come up with a scenario where such a path would be
better than range scan. But that's moot, and I'm pretty sure such
scenarios are beyond Oracle's optimizer.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
