Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Unnecessary SORT on Order By DESC

Unnecessary SORT on Order By DESC

From: Fidelio Software (ISRAEL) Ltd <mulyoved_at_netvision.net.il>
Date: 1996/11/12
Message-ID: <01bbd0c7$94477180$32675ac2@muly-new>#1/1

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                REGINDEX
Received on Tue Nov 12 1996 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US