From oracle-l-bounce@freelists.org Wed Feb 25 14:13:43 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1PKDhK27869 for ; Wed, 25 Feb 2004 14:13:43 -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 i1PKDho27864 for ; Wed, 25 Feb 2004 14:13:43 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 88588395742; Wed, 25 Feb 2004 15:15:10 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 25 Feb 2004 15:14:04 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from tera.umi.com (tera.umi.com [192.195.245.144]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 81C97395718 for ; Wed, 25 Feb 2004 15:14:00 -0500 (EST) Received: from bosmail02.bos.il.pqe (bosmail02.bos.il.pqe [172.24.3.66]) by tera.umi.com (8.11.6/8.11.6) with ESMTP id i1PKGl922333 for ; Wed, 25 Feb 2004 15:16:47 -0500 Received: from bosmail00.bos.il.pqe ([172.24.3.64]) by bosmail02.bos.il.pqe with Microsoft SMTPSVC(5.0.2195.6713); Wed, 25 Feb 2004 15:16:47 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C3FBDC.4B6572E4" Subject: RE: Index scan and redundant sorting Date: Wed, 25 Feb 2004 15:16:47 -0500 Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B452C9@bosmail00.bos.il.pqe> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Index scan and redundant sorting Thread-Index: AcP725RRUrZZtnK0R6mtWlhxRr6M2wAALC3w From: "Bobak, Mark" To: X-OriginalArrivalTime: 25 Feb 2004 20:16:47.0471 (UTC) FILETIME=[4B823FF0:01C3FBDC] X-archive-position: 1991 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Mark.Bobak@il.proquest.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l ------_=_NextPart_001_01C3FBDC.4B6572E4 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Yep, you got it. =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 3:10 PM To: oracle-l@freelists.org Subject: Re: Index scan and redundant sorting =09 =09 Let's see if I understand correctly. For an IFS or IRS, the blocks of an index are read (in single block read mode) in the order of their existence within the tree. For an IFFS, all blocks in the segment (below the HWM) are read in order of their 'physical' existence within the segment. This is not the order of their existence within the tree, so the blocks are 'out of order'.=20 Dan=20 "Bobak, Mark" wrote:=20 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 =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 ------_=_NextPart_001_01C3FBDC.4B6572E4 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Message
Yep,=20 you got it.
 
 

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

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

Let's see if = I understand=20 correctly. For an IFS or IRS, the blocks of an index are read (in = single block=20 read mode) in the order of their existence within the tree. For an = IFFS, all=20 blocks in the segment (below the HWM) are read in order of their = 'physical'=20 existence within the segment. This is not the order of their existence = within=20 the tree, so the blocks are 'out of order'.=20

Dan=20

"Bobak, Mark" wrote:=20

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

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

=00 ------_=_NextPart_001_01C3FBDC.4B6572E4-- ---------------------------------------------------------------- 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 -----------------------------------------------------------------