Re: full table scan cheaper than index??

"Jan Haase" <jh_at_informationsdesign.de> wrote

> Obviously the full table scan is indeed cheaper than the use of the index.
 But
> why?

A few reasons. The size of the table. The size of the indexes. The amount of data that needs to be processed. The criteria used.

 select distinct id from gnd_storm where  stb <= TO_DATE('01/03/2001','DD/MM/YYYY') and  ste >= TO_DATE('01/03/2001','DD/MM/YYYY') + 1;

If there are indexed on std and ste, Oracle will need to perform index range scans to find the applicable rows.

Index range scans on a large table can be extremely slow, as a sizeable chunk of the index may need to be processed to find the range of rows that meet the criteria.

Next, each row matching the results needs to be read from the disk. Due to amount of index data that needs to be scanned, Oracle can rather put that time to use simply doing a full table scan.

Also consider PQ. With an index range scan (you need two of them for the above query) and the subsequent db file reads to get the rows from disk, Oracle uses a single process.

With PQ you can perform the full table scan in parallel. Thus instead of having a single process grinding its way through the indexes, you have multiple processes doign a full table scan of the table, each doing a unique chunk of the table.

Try the following:

 select
 /*+ full(gnd_storm) parallel(gnd_storm,5) */  distinct id from gnd_storm where
 stb <= TO_DATE('01/03/2001','DD/MM/YYYY') and  ste >= TO_DATE('01/03/2001','DD/MM/YYYY') + 1;

This will use 5 PQ slaves for the full table scan, and should be considerable faster than doing index range scans.. assuming of course you are dealing with sizeable volumes of data. Also make sure that you have PQ installed and configured in the init.ora.

Glo my - jy kan 'n baie groot verskil sien in spoed. :-)

--
Billy