From oracle-l-bounce@freelists.org Thu Feb 26 08:40:04 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1QEe4j12703 for ; Thu, 26 Feb 2004 08:40:04 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i1QEdto12679 for ; Thu, 26 Feb 2004 08:39:55 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A5CEE394F71; Thu, 26 Feb 2004 09:41:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 26 Feb 2004 09:39:59 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from maillnx-us111.fmr.com (maillnx-us111.fmr.com [192.223.198.26]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B0A80394F5B for ; Thu, 26 Feb 2004 09:39:54 -0500 (EST) Received: from virmro111nts.fmr.com (VIRMRO111NTS.fmr.com [172.26.5.99]) by maillnx-us111.fmr.com (Switch-3.1.2/Switch-3.1.0) with SMTP id i1QEg29c003944 for ; Thu, 26 Feb 2004 09:42:02 -0500 Received: by msgmmk102nts.fmr.com with Internet Mail Service (5.5.2657.72) id ; Thu, 26 Feb 2004 09:42:02 -0500 Message-ID: From: "Khedr, Waleed" To: "'oracle-l@freelists.org'" Subject: RE: Index scan and redundant sorting Date: Thu, 26 Feb 2004 09:41:56 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-d6d44e21-01d8-4727-8c0a-143a197950f7" X-archive-position: 2062 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Waleed.Khedr@FMR.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------=_NextPartTM-000-d6d44e21-01d8-4727-8c0a-143a197950f7 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C3FC76.98D2AA96" ------_=_NextPart_001_01C3FC76.98D2AA96 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I agree, it's describing index full scan! =20 Waleed -----Original Message----- From: Tanel P=F5der [mailto:tanel.poder.003@mail.ee] Sent: Thursday, February 26, 2004 9:37 AM To: oracle-l@freelists.org 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. =20 Tanel. =20 ----- Original Message -----=20 From: Powell, Mark D=20 To: 'oracle-l@freelists.org' =20 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: =20 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 =20 =20 -----Original Message----- From: oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org]On Behalf Of Tanel P=F5der Sent: Wednesday, February 25, 2004 10:16 PM To: oracle-l@freelists.org 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... =20 Tanel. =20 ----- Original Message -----=20 From: Bobak, Mark =20 To: oracle-l@freelists.org =20 Sent: Wednesday, February 25, 2004 9:37 PM Subject: RE: Index scan and redundant sorting Dan, =20 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. =20 -Mark =20 =20 Mark J. Bobak=20 Oracle DBA=20 ProQuest Company=20 Ann Arbor, MI=20 "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@Sun.COM]=20 Sent: Wednesday, February 25, 2004 2:34 PM To: oracle-l@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.=20 =20 Table:=20 random_data=20 Name Null? Type=20 ------------------- -------- -------------------=20 REC_NO NOT NULL NUMBER=20 INSERT_TEXT VARCHAR2(200)=20 INSERT_DATE DATE=20 LARGE_RANDOM_NUM NUMBER=20 SMALL_RANDOM_NUM NOT NULL NUMBER <--- COLUMN OF INTEREST=20 ROWID_BLOCKNUM NUMBER=20 ROWID_ROWNUM NUMBER=20 =20 select column_name=20 from user_ind_columns=20 where index_name =3D 'IX_RD_SMALL_RN'=20 COLUMN_NAME=20 -----------------=20 SMALL_RANDOM_NUM=20 =20 set autotrace traceonly explain=20 select small_random_num=20 from random_data=20 order by small_random_num;=20 Execution Plan=20 ----------------------------------------------------------=20 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D7477 = Card=3D1000000 Bytes=3D2000000)=20 1 0 SORT (ORDER BY) (Cost=3D7477 Card=3D1000000 = Bytes=3D2000000) <------ Is this sort needed?=20 2 1 INDEX (FAST FULL SCAN) OF 'IX_RD_SMALL_RN' (NON-UNIQUE) (Cost=3D722 Card=3D1000000 Bytes=3D2000000)=20 ------_=_NextPart_001_01C3FC76.98D2AA96 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message
I = agree, it's=20 describing index full scan!
 
Waleed
-----Original Message-----
From: Tanel P=F5der=20 [mailto:tanel.poder.003@mail.ee]
Sent: Thursday, February = 26, 2004=20 9:37 AM
To: oracle-l@freelists.org
Subject: Re: = Index scan=20 and redundant sorting

However, the first statement in this = note is=20 wrong and contradicts with the next one. When doing a fast full scan, = Oracle=20 will not traverse from root to leaves, it just reads the segment = header block=20 for the index, gets the extent map from there and scans all the = extents=20 belonging to index up to it's HWM.
 
Tanel.
 
----- Original Message -----
From:=20 Powell, Mark=20 D
Sent: Thursday, February 26, = 2004 4:21=20 PM
Subject: RE: Index scan and = redundant=20 sorting

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

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

           =20 http://www.j= lcomp.demon.co.uk/faq/ind_rebuild.html=20
&nb= sp;
-----Original Message-----
From:=20 oracle-l-bounce@freelists.org = [mailto:oracle-l-bounce@freelists.org]On=20 Behalf Of Tanel P=F5der
Sent: Wednesday, February = 25, 2004=20 10:16 PM
To: oracle-l@freelists.org
Subject: = Re: Index=20 scan and redundant sorting

That's also the reason why index = rebuild=20 requires sorting, controversary to a myth that it = doesn't...
 
Tanel.
 
----- Original Message ----- =
From:=20 Bobak, Mark =
Sent: Wednesday, = February 25, 2004=20 9:37 PM
Subject: RE: Index scan = and=20 redundant sorting

Dan,
 
Only an INDEX FULL SCAN (walks the tree, does single = block reads)=20 provides sorted output.
An INDEX FAST FULL SCAN (does not walk tree structure, = does=20 multi-block reads, discards branch blocks) does NOT provide = sorted=20 output.
 
-Mark
 
 

Mark=20 J. Bobak
Oracle DBA
ProQuest = Company=20
Ann=20 Arbor, MI
"Imagination was given to man to compensate him for = what he is=20 not, and a sense of humor was provided to console him for what = he=20 is."  --Horace Walpole

-----Original = Message-----
From: Daniel=20 Fink [mailto:Daniel.Fink@Sun.COM]
Sent: Wednesday, = February=20 25, 2004 2:34 PM
To:=20 oracle-l@freelists.org
Subject: Index scan and = redundant=20 sorting

A query (with an order by) is = able to=20 satisfy it's column list by scanning an index. This scan will = return=20 the rows in sorted order, but the query still executes a sort = (confirmed by 10046 trace). Should not the result set from = the fast=20 full scan be correctly ordered? This would make the sort = redundant,=20 but very expensive in terms of response time.
 =20

Table:
 random_data=20 =
 Name         =       =20 Null?    Type =
 -------------------=20 -------- -------------------=20 =
 REC_NO        &nbs= p;    =20 NOT NULL NUMBER=20 =
 INSERT_TEXT        = ;         =20 VARCHAR2(200)=20 =
 INSERT_DATE        = ;         =20 DATE=20 =
 LARGE_RANDOM_NUM       =      =20 NUMBER
 SMALL_RANDOM_NUM    = NOT NULL=20 NUMBER  <--- COLUMN OF INTEREST=20 =
 ROWID_BLOCKNUM       &n= bsp;      =20 NUMBER=20 =
 ROWID_ROWNUM       &nbs= p;        =20 NUMBER
 =20

select column_name
from user_ind_columns
where = index_name =3D=20 'IX_RD_SMALL_RN'=20

COLUMN_NAME
-----------------
SMALL_RANDOM_NUM =
 =20

set autotrace traceonly explain
select = small_random_num=20
from random_data
order by small_random_num;=20

Execution Plan=20 =
----------------------------------------------------------=20
   0      SELECT = STATEMENT=20 Optimizer=3DCHOOSE (Cost=3D7477 Card=3D1000000 = Bytes=3D2000000)=20
   1    0   SORT = (ORDER BY)=20 (Cost=3D7477 Card=3D1000000=20 Bytes=3D2000000)       = <------ Is this=20 sort needed?
   2   =20 1     INDEX (FAST FULL SCAN) OF = 'IX_RD_SMALL_RN'=20 (NON-UNIQUE) (Cost=3D722 Card=3D1000000 Bytes=3D2000000)=20 =

------_=_NextPart_001_01C3FC76.98D2AA96-- ------=_NextPartTM-000-d6d44e21-01d8-4727-8c0a-143a197950f7-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------