Re: Why Sort...?

Matt wrote:

Hello Matt,

this is an interesting question. I wonder what's wrong myself.

Let me ask 'you something, if you delete the index, and try the SELECT statement again: Which costs does the plan show for the sort statement then?

> I have created a composite index on a table which includes 3
> descending columns...

Just to be precise, your statement includes only TWO descending columns. Perhaps there is some clue?

 > 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....

Again, to be precise, Oracle does not store the DATA in sorted order; instead it only stores the INDEX in sorted order.

Regards
Georg

> 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....
>
> Version 8.1.7.4.1 on W2K
>
> Thanks
>
> Mat
Received on Fri Apr 30 2004 - 07:02:04 CDT