Re: Oracle Index Sizing Question

From: Sanjay D.S. <sanjay_at_fsg.prusec.com>
Date: 1996/03/29
Message-ID: <4jhd6t$ktr_at_prufire4.prusec.com>#1/1

Gerry Lurette <gerard_at_magi.com> wrote:
>This is based on an 'Analyze Table(or Index) Compute Statistics' script
>already having been run.
>
>Is there a more simplified way of sizing Oracle indexes to get a general
>estimate than following the 'Calculating Space for Indexes' in the
>Oracle7 Server Administrators Guide?
>
>In sizing tables, I multiply num_rows*avg_row_len from user_tables to get
>a general estimate.

For calculating index size on any ORACLE table one should take in consideration following:

Total number of rows ( approx. current + future). [rows] Percent Free factor per block (In case indexed column value changes) [pctfree] Most of the times pctfree for index should be zero. Average column length (You could use vsize to find out length of all columns that

                       constite the index) [tot_col_len]

Number of columns forming the index [number_of_cols] Uniqueness factor (UNIQUE = 1, NONUNIQUE = 0) [uniqueness] Initial number of transactions (Mostly 1, but in case you have very large number of transaction affecting this table, you may want to set to higher number)[inittrans]

Block header (This is constant and should be subtracted from total block size so as to calculate available space per block) [blk_hdr]

I use this algorithm to calculate index from the above available information:

       blk_size := (1024,2046,4096,..)

     net_space := blk_size - 113 - 23*inittrans;
                  (BLK SIZE) ( BLOCK HEADER )

     index_size(in blocks) := 
   GREATEST(4, (1.01) * 
            ((rows/  
                FLOOR((net_space * (1 - pctfree/100))
                       /(10 + uniqueness + number_of_cols + tot_col_len)))  )
          );

Sanjay D.S.
Oracle Consultant
Prudential Securities, Inc. Received on Fri Mar 29 1996 - 00:00:00 CET