Why Sort...?

Giganews Newsgroups
Subject: Why Sort...?
Posted by:  Matt (mcc…@hotmail.com)
Date: 30 Apr 2004

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

Replies