Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: NULLS FIRST option in CREATE INDEX?

Re: NULLS FIRST option in CREATE INDEX?

From: Alanoly J. Andrews <alanolya_at_invera.com>
Date: 11 Jun 2003 05:25:35 -0700
Message-ID: <e76061b9.0306110425.63e978c3@posting.google.com>


"Ganesh Raja" <ganesh_at_gtfs-gulf.com> wrote in message news:<1055300873.896158_at_cswreg.cos.agilent.com>...
> 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
>

Thanks again....but, my original query was

      select * 
      from tab1
      where col1='ABC' and col2 >= ' '
      order by col1, col2 nulls first;

This means that I need col2 (ie, the nullable column) values in the ascending order, but with the nulls first. The "order by" clause achieves this, but at the cost of doing a SORT on the retrieved rows. Your solution will work only for a desc order of the nullable columns. Not what I need.

A.A. Received on Wed Jun 11 2003 - 07:25:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US