Why Sort...?

From: Matt <mccmx_at_hotmail.com>
Date: 30 Apr 2004 04:21:59 -0700
Message-ID: <cfee5bcf.0404300321.42bf0ee9@posting.google.com>

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

Version 8.1.7.4.1 on W2K

Thanks

Mat Received on Fri Apr 30 2004 - 06:21:59 CDT