Re: Why Sort...?

Matt wrote:
> I have created a composite index on a table which includes 3
> descending columns...
>
> create index ps_job_desc on ps_job(emplid, empl_rcd, effdt desc,
> effseq desc)
>
> Why does the following query perform a SORT operation..?
>
> 1 select emplid, empl_rcd, effdt, effseq from ps_job
> 2* order by emplid, empl_rcd, effdt desc, effseq desc
> SQL> /
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=651 Card=162094 Byte
> s=2917692)
>
> 1 0 SORT (ORDER BY) (Cost=651 Card=162094 Bytes=2917692)
> 2 1 INDEX (FAST FULL SCAN) OF 'PS_JOB_DESC' (NON-UNIQUE) (Co
> st=4 Card=162094 Bytes=2917692)
>
> It uses my index but it still performs a sort despite the fact that
> Oracle has stored the data in the order I have requested it....
>

Hello Mat,
are your columns varchar2 or char? In this case the order by cannot use the index because it is binary sorted whereas order by considers your nls_sort setting. So some characters are sorted differently from their ASCII values.
You may try this:

alter session set nls_sort=binary;

Then your order by should use the index for sorting but you get strictly ASCII (or whatever you character set is) sort order.

Hope that helps,
Lothar

-- 
Lothar Armbrüster       | la_at_oktagramm.de
Hauptstr. 26            | la_at_heptagramm.de
D-65346 Eltville        | lothar.armbruester_at_t-online.de