Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Unnecessary SORT on Order By DESC
select * from RESR where REG between 'A' and 'Z' order by REG; select * from RESR where REG between 'A' and 'Z' order by REG DESC;
RESR has INDEX REGINDEX on REG
I don't understand way ORACLE need to sort the values in the 2nd SELECT
(which is very slow on a big database)
ORACLE has a Index on that field all he need to do is to go backword!
Any idea how to improve the proformance of the 2nd query?
My ideas is
1. Create another index by REG DESC.
2. Limit to number of record included in the Select to say to 100 if all I
need is just a limited number of records, How you do samething like that?
TIA Muly.
The show plan of the commands:
select * from RESR where REG between 'A' and 'Z' order by REG;
OPERATION OPTIONS OBJECT_NAME ------------------------------ ------------------------------ ------------ SELECT STATEMENT TABLE ACCESS BY ROWID RESR INDEX RANGE SCAN REGINDEX
select * from RESR where REG between 'A' and 'Z' order by REG DESC;
OPERATION OPTIONS OBJECT_NAME ------------------------------ ------------------------------ ----------- SELECT STATEMENT SORT ORDER BY TABLE ACCESS BY ROWID RESR INDEX RANGE SCAN REGINDEXReceived on Tue Nov 12 1996 - 00:00:00 CST