Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: Index scan and redundant sorting

From: Tanel Põder <>
Date: Thu, 26 Feb 2004 16:37:24 +0200
Message-ID: <245c01c3fc76$0e738ca0$73f823d5@porgand>

MessageHowever, the first statement in this note is wrong and contradicts with the next one. When doing a fast full scan, Oracle will not traverse from root to leaves, it just reads the segment header block for the index, gets the extent map from there and scans all the extents belonging to index up to it's HWM.


  Here is an article hosted on the cooperative FAQ on Jonathan's site that demonstrates that Oracle sorts on an index rebuild and that an index rebuild will sometimes perform a full table scan rather than read the index:

  When I rebuild an index, I see Oracle doing a sort. Why should this be necessary, why doesn't it simply read the existing index ?


    -----Original Message-----
    From: []On Behalf Of Tanel Põder     Sent: Wednesday, February 25, 2004 10:16 PM     To:
    Subject: Re: Index scan and redundant sorting

    That's also the reason why index rebuild requires sorting, controversary to a myth that it doesn't...



      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 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_at_Sun.COM] Sent: Wednesday, February 25, 2004 2:34 PM To: 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:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Feb 26 2004 - 08:34:35 CST

Original text of this message