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

Home -> Community -> Mailing Lists -> Oracle-L -> Index scan and redundant sorting

Index scan and redundant sorting

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Wed, 25 Feb 2004 12:34:00 -0700
Message-id: <403CF8A8.6944A2CF@sun.com>


A query (with an order by) is able to satisfy it's column list by scanning an index. This scan will return the rows in sorted order, but the query still executes a sort (confirmed by 10046 trace). Should not the result set from the fast full scan be correctly ordered? This would make the sort redundant, but very expensive in terms of response time.

Table:
 random_data

 Name                Null?    Type
 ------------------- -------- -------------------
 REC_NO              NOT NULL NUMBER
 INSERT_TEXT                  VARCHAR2(200)
 INSERT_DATE                  DATE
 LARGE_RANDOM_NUM             NUMBER
 SMALL_RANDOM_NUM    NOT NULL NUMBER  <--- COLUMN OF INTEREST
 ROWID_BLOCKNUM               NUMBER
 ROWID_ROWNUM                 NUMBER


select column_name
from user_ind_columns
where index_name = 'IX_RD_SMALL_RN'

COLUMN_NAME



SMALL_RANDOM_NUM set autotrace traceonly explain
select small_random_num
from random_data
order by small_random_num;

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7477 Card=1000000 Bytes=2000000)

   1 0 SORT (ORDER BY) (Cost=7477 Card=1000000

Bytes=2000000)       <------ Is this sort needed?
   2    1     INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN'
(NON-UNIQUE) (Cost=722 Card=1000000 Bytes=2000000)

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

-----------------------------------------------------------------
Received on Wed Feb 25 2004 - 13:30:57 CST

Original text of this message

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