Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!small1.nntp.aus1.giganews.com!border3.nntp.aus1.giganews.com!nntp.giganews.com!wn14feed!worldnet.att.net!204.127.198.203!attbi_feed3!attbi.com!rwcrnsc53.POSTED!not-for-mail Reply-To: "Jim Kennedy" From: "Jim Kennedy" Newsgroups: comp.databases.oracle.server References: Subject: Re: NULLS FIRST option in CREATE INDEX? Lines: 55 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Message-ID: NNTP-Posting-Host: 12.224.116.189 X-Complaints-To: abuse@attbi.com X-Trace: rwcrnsc53 1054926232 12.224.116.189 (Fri, 06 Jun 2003 19:03:52 GMT) NNTP-Posting-Date: Fri, 06 Jun 2003 19:03:52 GMT Organization: AT&T Broadband Date: Fri, 06 Jun 2003 19:03:52 GMT Xref: core-easynews comp.databases.oracle.server:188916 X-Received-Date: Fri, 06 Jun 2003 12:03:14 MST (news.easynews.com) Oracle does not index null values. Jim -- Replace part of the email address: kennedy-down_with_spammers@attbi.com with family. Remove the negative part, keep the minus sign. You can figure it out. "Alanoly J. Andrews" wrote in message news: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.