Re: NULLS FIRST option in CREATE INDEX?

"Alanoly J. Andrews" <alanolya_at_invera.com> wrote in message news:e76061b9.0306060939.5a08ffa1_at_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.

Why not just UNION ALL two selects together? This example will give you nulls first without doing a sort:

select * from table where key_expr is null union all
select * from table where key_expr is not null Received on Fri Jun 06 2003 - 14:36:11 CDT