Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!newsfeed1.earthlink.net!elnk-nf2-pas!newsfeed.earthlink.net!newshub.sdsu.edu!headwall.stanford.edu!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: alanolya@invera.com (Alanoly J. Andrews)
Newsgroups: comp.databases.oracle.server
Subject: NULLS FIRST option in CREATE INDEX?
Date: 6 Jun 2003 10:39:11 -0700
Organization: http://groups.google.com/
Lines: 44
Message-ID: <e76061b9.0306060939.5a08ffa1@posting.google.com>
NNTP-Posting-Host: 198.168.207.252
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1054921151 31003 127.0.0.1 (6 Jun 2003 17:39:11 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 6 Jun 2003 17:39:11 GMT
Xref: core-easynews comp.databases.oracle.server:188901
X-Received-Date: Fri, 06 Jun 2003 10:38:29 MST (news.easynews.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.