Re: NULLS FIRST option in CREATE INDEX?

See if this Helps.

Os-User_at_db9i on 9.2.0.3.0>drop index edidx;

Index dropped.

Os-User_at_db9i on 9.2.0.3.0>Create Index EDIDX on Emp(deptno desc, empno);

Index created.

Os-User_at_db9i on 9.2.0.3.0>select /*+INDEX(EMP EDIDX) */ empno, deptno from emp;

     EMPNO DEPTNO
---------- ----------

      7782
      7839
      7934
      7499         30
      7521         30
      7654         30
      7698         30
      7844         30
      7900         30
      7369         20
      7566         20

     EMPNO     DEPTNO
---------- ----------
      7788         20
      7876         20
      7902         20

14 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=82 Bytes=213
          2)

   1    0   INDEX (FULL SCAN) OF 'EDIDX' (NON-UNIQUE) (Cost=26 Card=82
           Bytes=2132)

Os-User_at_db9i on 9.2.0.3.0>

--
HTH

Regards,
Ganesh R


"Alanoly J. Andrews" <alanolya_at_invera.com> wrote in message
news:e76061b9.0306100449.6b6fec3f_at_posting.google.com...


> "Ganesh Raja" <ganesh_at_gtfs-gulf.com> wrote in message

news:<1055206226.914685_at_cswreg.cos.agilent.com>...


> > Select * From (

> > select * from table where key_expr is null

> > union all

> > select * from table where key_expr is not null)

> > Order By 1

> >

> > See if this Helps...

> >

>

> Ganesh,

>

> Thanks....Your sql should probably work, but then so will

> my first SQL which is simpler. But the point of my inquiry

> is not the production of any working SQL. I think you might

> need to read my original message which started off this thread.

> I have a fairly large table with several columns some of which

> are nullable and form parts of composite indexes. I need the

> DBMS to read the table through the index and return the result

> row by row to my application. Without using any temporary tables

> (as your SQL would) and without doing any sort (as your SQL would).

> For nullable columns, the null values should come first in the

> ordering. The problem with Oracle is that in its index ordering,

> the nulls come last. Is there any workaround to this...is it

> possible to force Oracle to change the ordering at the index

> creation stage?

>

> A.A.