RE: Index scan and redundant sorting
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 27 Feb 2004 12:46:15 -0500
Correction submitted. Added references to the Oracle Performance manual
where topic is discussed. It could be a couple of weeks before the improved
version replaces the original.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Bobak, Mark
Sent: Friday, February 27, 2004 10:58 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Index scan and redundant sorting
Geez, who did such a shoddy job reviewing that FAQ entry??
Oh wait, that was me...
-----Original Message-----
From: Powell, Mark D [mailto:mark.powell@xxxxxxx]
Sent: Thursday, February 26, 2004 10:00 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Index scan and redundant sorting
Yes, l will submit a correction to the wording in the article.
[>>] -----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Khedr, Waleed
Sent: Thursday, February 26, 2004 9:42 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: Index scan and redundant sorting
I agree, it's describing index full scan!
Waleed
-----Original Message-----
From: Tanel Põder [mailto:tanel.poder.003@xxxxxxx]
Sent: Thursday, February 26, 2004 9:37 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Index scan and redundant sorting
However, 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.
Tanel.
----- Original Message -----
From: Powell, <mailto:mark.powell@xxxxxxx> Mark D
To: 'oracle-l@xxxxxxxxxxxxx' <mailto:'oracle-l@xxxxxxxxxxxxx'>
Sent: Thursday, February 26, 2004 4:21 PM
Subject: RE: Index scan and redundant sorting
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 ?
http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html
<http://www.jlcomp.demon.co.uk/faq/ind_rebuild.html>
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Tanel Põder
Sent: Wednesday, February 25, 2004 10:16 PM
To: oracle-l@xxxxxxxxxxxxx
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...
Tanel.
----- Original Message -----
From: Bobak, Mark <mailto:Mark.Bobak@xxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx <mailto: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:
