From oracle-l-bounce@freelists.org Wed Feb 25 14:07:39 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i1PK7d026963 for ; Wed, 25 Feb 2004 14:07:39 -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 i1PK7do26958 for ; Wed, 25 Feb 2004 14:07:39 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B995E3955C9; Wed, 25 Feb 2004 15:09:03 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 25 Feb 2004 15:07:45 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from brmea-mail-3.sun.com (brmea-mail-3.Sun.COM [192.18.98.34]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D42DA395344 for ; Wed, 25 Feb 2004 15:07:42 -0500 (EST) Received: from phys-giza-1 ([129.147.4.102]) by brmea-mail-3.sun.com (8.12.10/8.12.9) with ESMTP id i1PKATi7018135 for ; Wed, 25 Feb 2004 13:10:29 -0700 (MST) Received: from sun.com (sr1-ubrm-03.Central.Sun.COM [129.147.4.9]) by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0HTN00FW7PDH0H@giza-mail1.Central.Sun.COM> for oracle-l@freelists.org; Wed, 25 Feb 2004 13:10:29 -0700 (MST) Date: Wed, 25 Feb 2004 13:10:29 -0700 From: Daniel Fink Subject: Re: Index scan and redundant sorting To: oracle-l@freelists.org Message-id: <403D0135.298714A2@sun.com> MIME-version: 1.0 X-Mailer: Mozilla 4.79C-CCK-MCD [en] (X11; U; SunOS 5.9 sun4u) Content-Type: multipart/alternative; boundary=------------68175ADCD7F384D39DE4CFD3 X-Accept-Language: en References: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF03B452C7@bosmail00.bos.il.pqe> X-archive-position: 1990 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Daniel.Fink@Sun.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l --------------68175ADCD7F384D39DE4CFD3 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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'. Dan "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 --------------68175ADCD7F384D39DE4CFD3 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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'.

Dan

"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

--------------68175ADCD7F384D39DE4CFD3-- ---------------------------------------------------------------- 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 -----------------------------------------------------------------