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
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 2Received on Fri Jul 14 1995 - 00:00:00 CEST