Estimate space used by an Oracle index

Question:  I am creating a new Oracle index and I want to get the storage clause properly so that the index will fit within a single initial extent.  What formula can I use to estimate the size of an Oracle index?

Answer:  For global sizing estimate of tables and indexes, I recommend the Oracle capacity planning spreadsheets.  To estimate the size of an index for the storage clause, you can use the following procedure:

1. Calculate the required database block header size (BHS).
       BHS = fixed header + variable transaction header
       where:
       fixed header = 113
        variable transaction header = 24  *INITRANS

2. Calculate available data space (ADS).
       ADS = ((Blocksize - BHS)  * (PCTFREE/100))

3. Calculate the average data length (ADL) per row.
   
   Note: Step 3 is the same as step 3 in the table-sizing section in Chapter 5. However, size only for those columns in the index.

4. Calculate the average row length (ARL).
      bytes/entry = entry header + ROWID length + F + V + D
   
      where:
      entry header = 2
        ROWID = 6
   
   F = Total length of bytes of all columns that store 127 bytes or fewer—one header byte per column.
   V = Total length of bytes of all columns that store more than 127 bytes—two header bytes per column.
   For UNIQUE indexes, the entry header is 0.

5. Calculate number of blocks for index.
       # of Blocks = 1.05 * (((# of NOT NULL rows) / (ADS) / ARL))
   
   The 1.05 factor allows for branch space and is an empirically (SWAG) derived value.

6. Calculate the number of bytes required.
       Size in bytes = BLOCKSIZE - number of blocks