Do you have any reference? Where can I find that in the literature? On 02/25/2004 02:37:33 PM, "Bobak, Mark" wrote: > Dan, > > Only an INDEX FULL SCAN (walks the tree, does single block reads) > provides sorted output. > An INDEX FAST FULL SCAN (does not walk tree structure, does multi-block > reads, discards branch blocks) does NOT provide sorted output. > > -Mark > > > > Mark J. Bobak > Oracle DBA > ProQuest Company > Ann Arbor, MI > "Imagination was given to man to compensate him for what he is not, and > a sense of humor was provided to console him for what he is." --Horace > Walpole > > -----Original Message----- > From: Daniel Fink [mailto:Daniel.Fink@xxxxxxx] > Sent: Wednesday, February 25, 2004 2:34 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: Index scan and redundant sorting > > > 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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------