RE: Index Full Scan -- Strange Issue

Waleed,

Yeah, Ian's idea got me thinking. If you ask the question "When is it more efficient to access *all* rows in a table via an index versus a full table scan?", a HWM way up there and a small number of rows seems plausible. And when someone asks "why does a full table scan take so long when there are no rows?" the first idea we all think of is the HWM. So it is logical to extend that thinking to the first question? So, what would happen if I create a table with a few million rows and have an index on a column in the table. Then, I delete all the rows, drop and re-create the index, analyze, and issue a select * from table with no criteria? The index would be in good shape and we would have the HWM way up there. Would an index full scan be used? Can't complete the test -- I have to go to work and the delete is still running :-(

But it is something to consider and something I can come back to. The goal here is to duplicate the case. And I now have some more question about how the tables in question have been handled. This is fun!

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 Khedr,
> Waleed
> Sent: Wednesday, March 13, 2002 11:48 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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

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