Re: Index scan and redundant sorting

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

Tanel.

  ----- Original Message ----- 
  From: Bobak, Mark 
  To: oracle-l@xxxxxxxxxxxxx 
  Sent: Wednesday, February 25, 2004 9:37 PM
  Subject: RE: Index scan and redundant sorting


  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)

Other related posts: