| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index Full Scan -- Strange Issue
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 <ORACLE-L_at_fatcity.com>
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
ian_at_slac.stanford.edu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jonathan Lewis
INET: jonathan_at_jlcomp.demon.co.uk
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: ListGuru_at_fatcity.com (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). Received on Thu Mar 14 2002 - 05:26:10 CST
 |
 |