Re: Indexing NULL values

On Wed, 07 Mar 2001 04:30:08 GMT, "Mike Fotiou" <mfotiou_at_magma.ca> wrote:

>Hi,
>
> Is there any way to index null values in Oracle? Does the BITMAP type
>index include NULL values? We have several searches which use IS NULL/IS
>NOT NULL conditions. Currently, the Table Scan that Oracle uses is killing
>query performance. These fields do not really lend themselves to default
>values (e.g. a closing date). In SQL Server, there is the ability to
>include NULLS when the index is built. Is there any such feature in Oracle
>8/8i?
>
>Mike
>

If all columns of an index record are null, that record is not included in the index. IS NULL / IS NOT NULL will almost always mean a full table scan. IS NOT NULL can often be replaced by a dummy condition like >0 or > chr(0)
There is no feature to include NULLs, and that is advantageous, especially if only a small subset of the table will have NOT NULL columns.
You might also consider have the optimizer create histograms on those columns. (ANALYZE TABLE ... compute statistics for all indexed columns size <n>). This will help choosing the correct index. Finally: Luckily Oracle is not SQLServer, it is much more than that.

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Mar 06 2001 - 23:56:48 CST