Cheeky Cockatoo

set verify off
accept INITTRANS NUMBER PROMPT 'Enter INITTRANS value: '
accept BLOCKSIZE NUMBER PROMPT 'Enter database block size: '
accept PCTFREE NUMBER PROMPT 'Enter PCTFREE value: '
accept TABLE_NAME CHAR PROMPT 'Table to index: '
define FIXED_HEADER = 113
define BLOCK_HEADER = &FIXED_HEADER + (23*&INITTRANS)
define ENTRY_HEADER = 2
define ROWID_LENGTH = 6
accept F NUMBER PROMPT 'Number of columns to be indexed that store <= 128 bytes: '
accept V NUMBER PROMPT 'Number of columns to be indexed that store >= 128 bytes: '
accept NOT_NULL NUMBER PROMPT 'Estimate of number of not null rows in table: '

set serverout on

VARIABLE D NUMBER
VARIABLE AVE_ENTRY_SIZE NUMBER
VARIABLE BLOCKS NUMBER

declare
  AVE_ENTRY_SIZE number;
  d number;
  BLOCKS number;
  DATA_S number;
begin
select AVG(NVL(VSIZE(&column_name),0)) 
--+ AVG(NVL(VSIZE(open_tar_flag),0))
into D from &TABLE_NAME where rownum < 1000;
AVE_ENTRY_SIZE:=&ENTRY_HEADER+&ROWID_LENGTH+(1*&F)+(3*&V)+D;
DATA_S := (&BLOCKSIZE-&BLOCK_HEADER) - ((&BLOCKSIZE-&BLOCK_HEADER) * (&PCTFREE/100));
BLOCKS:= (1.05 * &NOT_NULL * AVE_ENTRY_SIZE) /
((FLOOR(DATA_S/AVE_ENTRY_SIZE))*AVE_ENTRY_SIZE);
dbms_output.put_line('Blocks required = '||to_char (BLOCKS));
end;
/