Re: ** SQL QUERY TUNING

Jürgen Kling wrote:
>
> Who can help me to optimize the following SQL-Statement
>
> select * from table
>
> where tab_column = 1
>
> or tab_column is null
>
> Thje column TAB_COLUMN having an index, but ORACLE cant't use it. Thus
>
> Oracle do an Full Table scan.
>
> jkling_at_w-4.de

From the SQL language Reference manual (Oracle7): "Nulls are not indexed"
(What a bummer, I wasn't aware of that, has that changed with Oracle8?)

That seems to be your problem. If the rowids of rows which contain NULL are not kept in the index, there is no way a query can use that index to find those rows.
It seems your only chance to get to use that index is to go back to the old days
and replace the NULLs with -999999 or something, if that's possible in your case.
Of course that will screw up all your group functions like sum avg min on that column.

Christian Received on Fri Apr 17 1998 - 13:47:55 CDT