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 -> Re: query slower using order by asc (default index order) than oder by desc

Re: query slower using order by asc (default index order) than oder by desc

From: VIC <victorcy_at_gmail.com>
Date: 16 Feb 2006 12:07:17 -0800
Message-ID: <1140120437.363558.274270@o13g2000cwo.googlegroups.com>


Here are the plan information, basically the plan for those two queries are the same:
SQL> select * from system_log order by id desc ;

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=144188 Card=4091500
          Bytes=568718500)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SYSTEM_LOG' (Cost=144188
           Card=4091500 Bytes=568718500)

   2    1     INDEX (FULL SCAN DESCENDING) OF 'SYSTEM_LOG_PK' (UNIQUE)
           (Cost=22238 Card=4091500)

SQL> select * from system_log order by id asc;

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=144188 Card=4091500
          Bytes=568718500)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'SYSTEM_LOG' (Cost=144188
           Card=4091500 Bytes=568718500)

   2    1     INDEX (FULL SCAN) OF 'SYSTEM_LOG_PK' (UNIQUE) (Cost=2223
          8 Card=4091500)

Then I checked the actual plans when those two queries are running, the only difference is the index scan is usng FULL SCAN DESCENDING for "order by desc", while the index scan is using FULL SCAN for "order by asc"; everything else like cost are exactly the same. I also checked the wait event of those two queries when running, they are both waiting on db file sequencial read on index file.

Regards,
Vic Received on Thu Feb 16 2006 - 14:07:17 CST

Original text of this message

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