Index scan and redundant sorting
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 25 Feb 2004 12:34:00 -0700
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)
Other related posts:
