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

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

RE: Index scan and redundant sorting

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 25 Feb 2004 15:00:17 -0500
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B452C8@bosmail00.bos.il.pqe>


Mladen,

A quick tahiti search yielded the following URL: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/opti mops.htm#51674

-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: Mladen Gogala [mailto:mladen_at_wangtrading.com] Sent: Wednesday, February 25, 2004 2:46 PM To: oracle-l_at_freelists.org
Subject: Re: Index scan and redundant sorting

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_at_Sun.COM]
> Sent: Wednesday, February 25, 2004 2:34 PM
> To: oracle-l_at_freelists.org
> 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_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
-----------------------------------------------------------------
----------------------------------------------------------------
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 - 14:21:45 CST

Original text of this message

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