Re: ** SQL QUERY TUNING

On Fri, 17 Apr 1998 17:08:03 +0200, Jürgen Kling <jkling_at_w-4.de> 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

If it is critical that this query be as fast as possible, you may want to consider using a default value in place of nulls.

For instance, if I need to query on a date that may be null, and the access must be quick, I use a date of '01/01/0001' to represent a null. More work for your program, ( and you ) but it has the benefit of using an index.

This may not be necessary if your null column is at or near the end of a compound index.

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;) Regence Blue Cross Blue Shield of Oregon jkstill_at_bcbso.com
jkstill_at_teleport.com Received on Sat Apr 25 1998 - 21:45:17 CDT