Re: ACTUAL INDEX SIZE

From: PaulCinVT <paulcinvt_at_aol.com>
Date: 12 Oct 1999 19:06:27 GMT
Message-ID: <19991012150627.26681.00000089@ngol02.aol.com>

SQL: Script to determine INDEX storage requirement Last Updated on 28-JUL-97
The following formula and associated sample demonstrate a method of determining index storage size requirements on a fully populated table.

A commonly used formula that I've seen used and that generally errors on the conservative side is detailed below. This formula returns the index size in DB blocks. Also, this formula is only valid if the CREATE INDEX is issued on a fully populated table. Precreating the index on an empty table and subsequently populating the table will require storage requirements greater than these calulations.

Forumla:

greatest (4, (1.05) *
(( row_count /
((

      floor (
        (( db_block_size - 113 - ( initrans * 23 )) * (1 - (percent_free / 100
))) /
        (( 10 + uniqueness ) + number_col_index + ( total_col_length ))
      )

))
) * 2 )
)

Legend:

row_count => Estimated number of rows in the table Value: select count(*) from tab_name

db_block_size => Actual number of bytes available in the block

Value: select to_number(value) from v$parameter where name = 'db_block_size'

initrans => Bytes used for each initrans Value: The default for indexes is 2 (see INITRANS parameter of CREATE INDEX) Note: In 7.2 and higher may be better to multiply this value by 24 rather than 23

percent_free => Percent free specified for the index

Value: The default for indexes is 10 (see PCTFREE parameter of CREATE INDEX)

uniqueness => If the index is unique or not (1 for unique, 0 for not unique)

Value: Self-explanatory, will you having repeating values or not

number_col_index => Number of columns in the index

Value: Self-explanatory, how many columns will be in the index key

total_col_length => Estimated length of the index columns Value: select avg(nvl(vsize(col_name1),0))+avg(nvl(vsize(col_name2),0)) from tab_name

Sample:

Estimate the size of an index on the EMPNO and ENAME columns of the SCOTT.EMP table:

set serveroutput on
declare
row_count number;
db_block_size number;

  initrans         number := 2;   -- Assuming default INITRANS
  percent_free     number := 10;  -- Assuming default PCTFREE
  uniqueness       number := 1;   -- Assuming that the index will be UNIQUE
  number_col_index number := 2;   -- Assuming index is based on EMPNO and ENAME

  total_col_length number;
  size_in_blocks number;
begin
  select count(*) into row_count from scott.emp;   select to_number(value)
    into db_block_size
    from v$parameter where name = 'db_block_size';   select avg(nvl(vsize(empno),0))+avg(nvl(vsize(ename),0))     into total_col_length
    from scott.emp;
  select greatest (4, (1.05) *

      (( row_count /
        ((
          floor (
            (( db_block_size - 113 - ( initrans * 23 )) * (1 - (percent_free /
100 ))) /
            (( 10 + uniqueness ) + number_col_index + ( total_col_length ))
          )
        ))
      ) * 2 )

)

    into size_in_blocks
    from dual;
  dbms_output.put_line('Size in blocks: '||round(size_in_blocks)); end;
/

Paul in VT Received on Tue Oct 12 1999 - 14:06:27 CDT