Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: NULLS FIRST option in CREATE INDEX?
Hi!
You should never rely on ordering that comes from using an index. If the index is not used for some reason, or fast full scan is used, you will probably get completely different-ordered result. Order by is a must if you want order.
Tanel.
"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
Received on Mon Jun 16 2003 - 15:53:05 CDT