Re: Calculating Index space

From: Chris Hamilton <chrish_at_cmprime.att.com>
Date: 1997/02/20
Message-ID: <01bc1f65$ac220580$2d89b687@chrish-pc.cis.att.com>#1/1

> Does anyone have a script that calculates Index space. We run a
> datawarehouse shop and want to run this calculation.
> --
> JP Soria

Here's one that I wrote...

---

Chris Hamilton -- chrish_at_cmprime.att.com DBA, AT&T WorldNet Service - Lincroft, NJ http://www.serve.com/cowpb/chamilton.html

rem *****************************************************************
rem Name:     calc_ndx.sql
rem Author:   Chris Hamilton, TYC Associates, Inc.
rem Date:     10-Dec-92.  Revised CHH, 05-May-94.  Added prompt for
rem           Block Size, changed ttitle to include table and column names.
rem Purpose:  This script (based on formula in DBA guide) calculates the
rem           space allotment required for an Oracle index.
rem NOTE:     # of Columns and Average Length refers to defined length
rem           of Indexed columns only.
rem NOTE:     Critical parameter in this script is blk_size.  Change this
rem           for your blocksize when prompted at runtime.  If you don't
rem           know the blocksize, check the db_block_size parameter in your
rem           init.ora file, or run the following query:
rem           select name, value
rem           from   sys.v_$parameter
rem           where  name = 'db_block_size'

rem Usage: sqlplus -s un/pw @calc_ndx.sql

rem *****************************************************************

set verify off;
set linesize 80;
set pagesize 58;
set space 1;
set feedback off;

prompt -------------------------------------------------;

prompt Calculate Index Space Required Program;

prompt -------------------------------------------------;

accept table_name char prompt 'Table Name: '; accept column_name char prompt 'Column Name(s): '; accept n_rows number prompt '# of Rows: '; accept n_cols number prompt '# of Columns: ';

accept avg_len number prompt 'Average Column Length:  ';
accept pct_free number prompt 'PCTFREE value:  ';
accept blk_size number prompt 'Block Size in Bytes:  ';

accept filename char prompt 'Filename to spool to: ';

prompt -------------------------------------------------;

prompt Working...;

column blocks_req format 999,999,999,990 heading "# of Blocks Req"; column bytes_req format 999,999,999,999,990 heading "# of Bytes Req"; column n_rows format 999,999,990 heading "# of Rows"; column n_cols format 990 heading "# of|Cols";

column avg_len format 999,990.0 heading "Avg Len";
column blk_size format 9990 heading "Block|Size";
column pct_free format 90 heading "Pct|Free";

column table_name format a24 heading "Indexed Table"; column table_name new_value tname noprint; column column_name new_value cname noprint; column today new_value report_date noprint;

break on today on table_name on column_name on report;

ttitle skip 1 report_date right "Page" sql.pno skip 2 -

       center "Estimated Space Required to Create an Index" -
       skip 2 center "Table:  " tname ".  Column(s):  " cname "." skip 3;

spool &&filename;

select initcap(sysdate) today,

       upper('&&table_name') table_name,
       upper('&&column_name') column_name,
       &&n_rows n_rows,
       &&n_cols n_cols,
       &&avg_len avg_len,
       nvl(&&blk_size,2048) blk_size,
       &&pct_free pct_free,
       (round
       (
       (&&n_rows * (11 + &&n_cols + &&avg_len))
       /
       ((nvl(&&blk_size,2048) - 90) * (1 - &&pct_free/100))
       )
       ) blocks_req,
       (
       (round
       (&&n_rows * (5 + &&n_cols * (1 + &avg_len)))
       /
       ((nvl(&&blk_size,2048) - 90) * (1 - &&pct_free/100))
       ) 
       * nvl(&&blk_size,2048)
       ) bytes_req

from dual;

spool off;

prompt ;
pause Press <Return> to continue...; Received on Thu Feb 20 1997 - 00:00:00 CST