Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> NULLS FIRST option in CREATE INDEX?

NULLS FIRST option in CREATE INDEX?

From: Alanoly J. Andrews <alanolya_at_invera.com>
Date: 6 Jun 2003 10:39:11 -0700
Message-ID: <e76061b9.0306060939.5a08ffa1@posting.google.com>


Hi,

    Oracle 8.1.6, 9.1
    AIX 4.3.3, 5.1 Oracle has a NULLS FIRST option for its ORDER BY clause in a SELECT statement. Is there such or similar option in the CREATE INDEX statement so that the ordering for a nullable column in a composite index will have NULL values coming first?

Suppose I have a table:

     tab1 (col1  char(5) not null,
           col2  char(5) null).

I create an index tab1_ind1 (col1, col2).

col1 always has a value. col2 may have a few NULLs.

Now I run the query

           select *
           from tab1
           where col1='ABC' and col2 >= ' '
           order by col1, col2 nulls first;

Here the Optimizer may or may not use the index tab1_ind1. But a SORT is done for the required order. If I use a HINT to force the use of the index, the index is used but the SORT is still done because the desired order is not the same as the ordering in the index.

In my application I need to force the Optimizer to use the index (since we need a a row by row retrieval) and yet have the NULLS come first in the ordering. The obvious solution would be to force the index to be created in the same order as the ORDER BY with a statement similar to :

     "create index tab1_ind1 on tab1(col1, col2 nulls first)"; But such a statement does not exist in the syntax. The question is: Is there some workaround that someone has used or is aware of ? In the query such as the one above, I need to avoid a final SORT of retrieved rows.

Thanks.

Alanoly Andrews. Received on Fri Jun 06 2003 - 12:39:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US