Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sizing indexes, does anyone have an accurate script or algorithm?

Re: sizing indexes, does anyone have an accurate script or algorithm?

From: BobH <b-horton_at_NOSPAM.net>
Date: 1998/03/25
Message-ID: <6fc98v$qe3@bgtnsc03.worldnet.att.net>#1/1

Nick Borrell wrote:
>
> Does anyone have an index sizing script for Oracle8 (or 7) which they
> are happy with, and willing to share? I really find it hard to believe
> that there isn't an abundance of these scripts. Wouldn't it be logical
> for Oracle to distribute sizing tools with OEM?
>
> I've spent quite some time going thru the Oracle8 documentation and
> various other resources trying to accurately size or estimate the space
> requirements for primary keys and unique indexes. The estimates are
> generally well below the actual number of consumed blocks shown in
> dba_segments.
>
> To test my scripts I've tended to create simple tables, with 500 or 1000
> rows. Then I create a unique index, with an initial and next extent of
> one block. Unfortunately, the results aren't very inspiring. For
> example, if my estimate says I will need 4 blocks for the index, I have
> found that it will actually consume 7 blocks. Yesterday I created an
> index that was estimated to need 8 blocks, and actually consumed 18.
>
> The only immediate way I can see to improve things (immediately) is to
> increase the overhead for branches from 10% to more like 80%, but that
> would be ridiculous wouldn't it?
>
> I've repeatedly asked support for either a script or a better algorithm,
> and got no where. (Although, one support person did tell me that I'd be
> surprised to know how many people keep asking them about this.) I'm
> still optimistic that they have the script I want, but that for some
> reason I haven't given them exactly the right keyword to find it in
> their database.
>
> So, can anyone provide me with a script (or TAR number to get one), that
> is satisfactorily accurate? And how about sizing tables?
>
> Thanks in advance for any help,
>
> Nick Borrell
> DBA/Developer
> New Zealand Police
> +64 237 2811 x5459

You wanting to estimate for space allocation of an index or the length of each index? I have a nice formula in Excel that is straight from Oracle' documentation. works good. Received on Wed Mar 25 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US