Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: NULLS FIRST option in CREATE INDEX?
"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