RE: Index Full Scan -- Strange Issue

  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
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: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

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