Re: ORACLE optimiser question

On Thu, 13 Jun 1996 13:38:11 -0400, Gene Gurevich <geneg_at_umich.edu> wrote:

> I have a query:
>
>SELECT FILED1, count(*)
>FROM TABLE1
>GROUP BY FILELD1;
>
>The TABLE1 has an index on FILELD1. The ORACLE optimizer (7.1), however, does
>not use this index, accrording to the analyze statement, rather it does a
>FULL table search.. . Does anyone know why?
>
>TIA
Nulls are not indexed. select field1, count(*) from T group by field1 would give the wrong answer if field1 contains some null values. If for example, field1 was a number field consisting of only positive numbers:

select field1, count(*) from T WHERE FIELD1 > -1 gropu by field1

would probably make it use the index. Note that indexes are read a block at a time whereas a table will be read N blocks at a time (where n = db_file_multiblock_read_count). Doing a full scan is most often more efficient then doing a lot of index reads when you have to read all of the records anyway. You may find (unless the table is really wide) that the full scan is faster then the index.

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Jun 14 1996 - 00:00:00 CEST