Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!news-feed01.roc.ny.frontiernet.net!nntp.frontiernet.net!chi1.webusenet.com!news.webusenet.com!pd2nf1so.cg.shawcable.net!residential.shaw.ca!news3.calgary.shaw.ca.POSTED!not-for-mail X-Trace-PostClient-IP: 24.81.85.140 From: "Maximus" Newsgroups: comp.databases.oracle.server References: Subject: Re: NULLS FIRST option in CREATE INDEX? Lines: 58 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: Date: Fri, 06 Jun 2003 19:36:11 GMT NNTP-Posting-Host: 24.67.253.205 X-Complaints-To: abuse@shaw.ca X-Trace: news3.calgary.shaw.ca 1054928171 24.67.253.205 (Fri, 06 Jun 2003 13:36:11 MDT) NNTP-Posting-Date: Fri, 06 Jun 2003 13:36:11 MDT Organization: Shaw Residential Internet Xref: core-easynews comp.databases.oracle.server:188919 X-Received-Date: Fri, 06 Jun 2003 12:35:28 MST (news.easynews.com) "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. 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