Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> NULLS FIRST option in CREATE INDEX?
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