Re: table scan?/

D'laila Pereira wrote:
>
> Why might the optimiser use a table scan even index is available?
>
> Is it justcase where there is a query like
> select *
> From DEPT;
>
> where u need to retrieve all the rows from the table , so why bother to
> use the index, just scan the entire table?
> Please correct.

It depends on how good the optimizer is and which RDBMS you are using.

For example, if you are using Oracle and are accessing 25% of the rows in a table Oracle will do a full table scan. Apparently, based on the way Oracle manages tables and indexes, it is cheaper to do a full table scan than to read the index and then read the table. The reason is the extra seek time required to read the data.

When you read through the index you are reading the table in its logical sequence. This will usually mean that the disk will do a lot of unnecessary seeks. When you do a full table scan there are no unnecessary seeks, therefore full scans become faster when the number of rows retrieved is somewhere between 10% and 25% of the number of rows in the table. Received on Mon Feb 18 2002 - 05:01:10 CET