| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Index scan and redundant sorting
The keys stored in a block are in sorted order.
The blocks within an index are not necessarily in sorted order.
Jared
Mladen Gogala <mladen_at_wangtrading.com>
Sent by: oracle-l-bounce_at_freelists.org
02/25/2004 11:43 AM
Please respond to oracle-l
To: oracle-l_at_freelists.org
cc:
Subject: Re: Index scan and redundant sorting
Daniel, If I'm not mistaken, keys are stored in index in sorted order so that sort appears unnecessary. Try fiddling around with hints like /*+ index_asc(random_data,IX_RD_SMALL_RN) */
On 02/25/2004 02:34:00 PM, Daniel Fink wrote:
> 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)
>
-- 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 - 13:59:42 CST
 |
 |