RE: Index Full Scan -- Strange Issue

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

-----Original Message-----
Sent: Wednesday, March 13, 2002 7:14 PM
To: Multiple recipients of list ORACLE-L

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
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]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
> ian_at_slac.stanford.edu

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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).