ORDER BY ... DESC

From: Hans Malherbe <hans_at_pegasus.marques.co.za>
Date: 1995/07/14
Message-ID: <3u58n5$8m0_at_hermes.is.co.za>#1/1


I have a 100 000 row table that I want to step forwards and backwards in. Using the INDEX_DESC hint the ORDER BY ... DESC is quick, but as soon as I add a join the SELECT takes forever. Doing an "explain plan" suggests that the join causes a SORT operation. How to avoid this?

Statement:

SELECT /*+ INDEX_DESC(DPatien DPatien_2) USE_NL(DPatien_MaritalStatus) */ DPatien.ID,DPatien.HospNr,DPatien.Surname,DPatien.Firstname,DPatien.IDNr FROM DPatien,RMaritl DPatien_MaritalStatus WHERE DPatien.MaritalStatus = DPatien_MaritalStatus.ID(+) AND DPatien.Surname <='NOMPETSHENI'
ORDER BY DPatien.Surname DESC

Plan_Table:

Optimizer Operation Options Object_name Object_type ID Pa Obj

--------- ---------------- ---------- ----------- ----------- -- -- --- 
FIRST_ROW SELECT STATEMENT                                    0         
          SORT             ORDER BY                           1  0      
          NESTED LOOPS     OUTER                              2  1      
ANALYZED  TABLE ACCESS     BY ROWID   DPATIEN                 3  2  1   
ANALYZED  INDEX            RANGE SCAN DPATIEN_2   NON-UNIQUE  4  3      
ANALYZED  INDEX            UNIQUE SCA RMARITL_0   UNIQUE      5  2      
Received on Fri Jul 14 1995 - 00:00:00 CEST

Original text of this message