Re:Indexing NULL values

In the Oracle Tuning documentation it states:

Indexing Null Values

Bitmap indexes index null values, whereas all other index types do not. Consider, for example, a table with STATE and PARTY columns, on which you want to perform the following query: SELECT COUNT(*) FROM people WHERE state'CA' and party !'R';

Indexing nulls enables a bitmap minus plan where bitmaps for party equal to 'R' and NULL are subtracted from state bitmaps equal to 'CA'. The EXPLAIN PLAN output would look like this: SELECT STATEMENT

  SORT                    AGGREGATE 
    BITMAP CONVERSION     COUNT 
      BITMAP MINUS 
        BITMAP MINUS 
          BITMAP INDEX    SINGLE VALUE    STATE BM 
          BITMAP INDEX    SINGLE VALUE    PARTY BM 
        BITMAP INDEX      SINGLE VALUE    PARTY BM 

Note that if a NOT NULL constraint existed on party the second minus operation (where party is null) would be left out because it is not needed.

> -----Original Message-----
> From: Mike Fotiou [SMTP:mfotiou_at_magma.ca]
> Posted At: Tuesday, March 06, 2001 10:30 PM
> Posted To: misc
> Conversation: Indexing NULL values
> Subject: Indexing NULL values
>
> 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
>

--