From oracle-l-bounce@freelists.org Tue Mar 9 09:47:36 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i29Flar30139 for ; Tue, 9 Mar 2004 09:47:36 -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 i29Flao30125 for ; Tue, 9 Mar 2004 09:47:36 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3C35A395D04; Tue, 9 Mar 2004 10:36:40 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Mar 2004 10:35:27 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from ahmler1.mail.eds.com (ahmler1.mail.eds.com [192.85.154.71]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F2FC7395C39 for ; Tue, 9 Mar 2004 10:35:18 -0500 (EST) Received: from ahmlir3.mail.eds.com (ahmlir3-2.mail.eds.com [192.85.154.133]) by ahmler1.mail.eds.com (8.12.10/8.12.9) with ESMTP id i29Fe2Gj028572 for ; Tue, 9 Mar 2004 10:40:02 -0500 Received: from ahmlir3.mail.eds.com (localhost [127.0.0.1]) by ahmlir3.mail.eds.com (8.12.10/8.12.10) with ESMTP id i29Fdt2Q000757 for ; Tue, 9 Mar 2004 10:39:55 -0500 Received: from USAHM102.amer.corp.eds.com (usahm102.exmi01.exch.eds.com [207.37.138.190]) by ahmlir3.mail.eds.com (8.12.10/8.12.10) with ESMTP id i29FdsHo000747 for ; Tue, 9 Mar 2004 10:39:54 -0500 Received: by USAHM102.amer.corp.eds.com with Internet Mail Service (5.5.2657.72) id ; Tue, 9 Mar 2004 10:39:56 -0500 Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AA37@USAHM018.amer.corp.eds.com> From: "Powell, Mark D" To: "'oracle-l@freelists.org'" Subject: RE: Maximum height of an Oracle B-tree index Date: Tue, 9 Mar 2004 10:39:52 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain; charset="iso-8859-1" X-archive-position: 184 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mark.powell@eds.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l >> Why isn't it good to rebuild an index when the height increases? The formula for calculating I/O of an index is as follows << Because the height of an index may be the direct result of the quantity of data in the index. Thus rebuilding the index may not reduce the height and in this case the rebuild operation would be a waste of time and effort. Better rebuild criteria are called for. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of ryan.gaffuri@cox.net Sent: Tuesday, March 09, 2004 8:21 AM To: oracle-l@freelists.org Subject: Re: Maximum height of an Oracle B-tree index what type of algorithm do you run to increase the height of a b-tree index? My understanding is that oracle dynamically increases the number of pointers each block can have(which is different than other b-trees) in order to keep the height low? am I correct in this assumption? Why isn't it good to rebuild an index when the height increases? The formula for calculating I/O of an index is as follows LOG_height(blocks) = estimated I/O That is LOG of the height of an index to the base of its total number of blocks. Now I think there is a fudge factor based on the size of your blocks, because larger blocks incur more LIOs. This is not oracle specific. Its general tree theory. > > From: "Richard Foote" > Date: 2004/03/09 Tue AM 09:18:59 EST > To: > Subject: Maximum height of an Oracle B-tree index > > Hi All, > ---------------------------------------------------------------- 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 -----------------------------------------------------------------