Larry, I think Ian's idea might be correct. When you have a HWM the optimizer will make its mind based on segments' sizes. Having high percentage of deleted rows and fragmented segment in the table, leads the optimizer to read te table indirectly using the index. Why this index and not the others? It could be also related to status of the index: Size, percentage of deleted rows, clustering factor(very important), etc.
Regards, Waleed -----Original Message----- To: Multiple recipients of list ORACLE-L Sent: 3/13/02 10:14 PM Ian, You are going through the same thoughts I am. If the query could be satisfied within the index, then I could see an index FFS or an index FS. But, the query is picking values from non-indexed columns and *has* to hit the table. So, why go through the table via a full index scan? Highwater mark? That's something to think about! This *is* a staging table where depending upon the load cycle and processes, sometimes rows are deleted, sometimes the table truncated (which would lower the high-water mark, right?). I could see a case where 50 million rows are loaded but not processed, and then 10,00 rows loaded. And then the staging to real table process would process and delete 50 million rows leaving the HWM *way* up there and only 10,000 rows. But then when the 10,000 rows were processed and seeing that nothing else needs to be processed, the table would be truncated. So I guess I could see a case with an extremely HWM where it would use a full index scan to get rows when the table actually occupied very few blocks. But, why wouldn't it have chosen one of the indexed columns with criteria? Not considered as cost effective? And then you throw in the bit I mentioned about the table being rebuilt and still seeing the same plan. So, in that case, I don't think a HWM issue would come into play. But it is something I should take a look at. Thanks for bringing that up. Index access is sequential reads. FTS's are scattered reads. The first using single block I/O and the latter using multi-block I/O. In the case of a fast full index scan, multi-block reads are also used. But, for a full range scan on an index, I don't think, though I don't know for a fact, that multi-block I/O is used. Maybe it is. Listers, full index scans use multi-block I/O? The fact that index FFS's do, and a point is made of that in various docs, it seems to imply that full range scans don't? At least my impression has always been that the only time we would see multi-block I/O on an index is when fast full scans are used. Thanks for throwing some things out there. Regards, Larry G. Elkins [EMAIL PROTECTED] 214.954.1781 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of MacGregor, > Ian A. > Sent: Wednesday, March 13, 2002 7:59 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Index Full Scan -- Strange Issue > > > Some musings ... Another reason why Oracle might do a full index > scan is that the query can be satisfied by visiting the index > only, but then you wouldn't have the table lookup. Full Index > Scans are scattered reads which read more blocks at a time than > the one block read by the sequential read of a range scan, but > that would be slower than an FTS, unless the table has lots of > wasted space below the highwater mark. > > Ian MacGregor > Stanford Linear Accelerator Center > [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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).
