rem $DBA/cindex.sql
rem
rem This script will create a SQL script which can be used to create a
rem correctly-sized (based on the formulas in the Oracle7 Admin guide) index
rem for a given schema and table.  The index name is specified in &3 and the
rem list of the indexed columns are stored in cindex_temp.sql.
rem
rem Last Change 09/30/97 by Brian Lomasky
rem

set echo off
set verify off
set linesize 80
set heading off
set pagesize 0
set feedback off
ttitle off

set term off
column tname new_value tnm
column dbb new_value blk_size
column cnt new_value n_rows
column own new_value ownr
column fre new_value pct_free
column itr new_value initrans
select owner own, table_name tname, pct_free fre, ini_trans itr
	from dba_tables
	where owner = upper('&&1') and table_name = upper('&&2');

rem Create a temp table to hold the columns to be indexed
drop table cindex_temp;
set term on
create table cindex_temp (
	lineno NUMBER,
	colname VARCHAR2(30));
rem Store the columns to be indexed in our temp table
@cindex_temp
set term off
rem Calculate average entry size for each indexed row
create table create_index_tmp (aes number);
declare
	total_size	number;
	cursor c1 is
		select colname
		from cindex_temp
		order by lineno;
	cursor c2 (coln in char) is
		select data_type, data_length
		from dba_tab_columns
		where owner = '&ownr' and
		table_name = '&&2' and
		column_name = coln;
begin
	total_size := 2 + 6;
	for ee in c1 loop
		for ff in c2 (ee.colname) loop
			if ff.data_type = 'NUMBER' then
				if ff.data_length > 128 then
					total_size := total_size +
						ff.data_length + 3;
				else
					total_size := total_size +
						ff.data_length + 1;
				end if;
			elsif ff.data_type = 'FLOAT' then
				if ff.data_length > 128 then
					total_size := total_size +
						ff.data_length + 3;
				else
					total_size := total_size +
						ff.data_length + 1;
				end if;
			elsif ff.data_type = 'VARCHAR2' then
				if ff.data_length > 128 then
					total_size := total_size +
						ff.data_length + 3;
				else
					total_size := total_size +
						ff.data_length + 1;
				end if;
			elsif ff.data_type = 'VARCHAR' then
				if ff.data_length > 128 then
					total_size := total_size +
						ff.data_length + 3;
				else
					total_size := total_size +
						ff.data_length + 1;
				end if;
			elsif ff.data_type = 'LONG' then
				if ff.data_length > 128 then
					total_size := total_size +
						ff.data_length + 3;
				else
					total_size := total_size +
						ff.data_length + 1;
				end if;
			elsif ff.data_type = 'DATE' then
				if ff.data_length > 128 then
					total_size := total_size +
						ff.data_length + 3;
				else
					total_size := total_size +
						ff.data_length + 1;
				end if;
			elsif ff.data_type = 'ROWID' then
				if ff.data_length > 128 then
					total_size := total_size +
						ff.data_length + 3;
				else
					total_size := total_size +
						ff.data_length + 1;
				end if;
			elsif ff.data_type = 'CHAR' then
				if ff.data_length > 128 then
					total_size := total_size +
						ff.data_length + 3;
				else
					total_size := total_size +
						ff.data_length + 1;
				end if;
			end if;
		end loop;
	end loop;
	insert into create_index_tmp values (total_size);
	commit work;
end;
/
column aes new_value avg_entry_size
select aes from create_index_tmp;
drop table create_index_tmp;

select value dbb from v$parameter where name = 'db_block_size';
select count(*) cnt from &ownr..&tnm;

rem Calculate available data space per data block
column ads new_value avail_data_space
select floor((&&blk_size - 113 - (23 * &&initrans)) * (1-(&&pct_free/100))) ads
	from dual;

rem Calculate space for each index
column spa new_value space
select floor(&&avail_data_space / &&avg_entry_size) * &&avg_entry_size spa
	from dual;

rem Calculate number of Oracle blocks required
column blk new_value blocks_req
select ceil(1.05 * &&n_rows * &&avg_entry_size / &&space) blk
	from dual;

rem Calculate initial size in K-bytes
column ini new_value initial_extent
select ceil(&blocks_req * &blk_size / 1024) ini
	from dual;

rem Calculate next size in K-bytes
column nxt new_value next_extent
select floor(&initial_extent / 2) nxt
	from dual;

spool cr_index.sql
select 'create index &ownr'||'.'||'&&3'||' on'
	from dual;
select chr(9)||'&ownr'||'.'||lower('&&2')||'('
	from dual;
select chr(9)||decode(lineno,1,null,','), lower(colname)
	from cindex_temp
	order by lineno;
select chr(9)||') tablespace <INSERT TABLESPACE NAME HERE>'
	from dual;
select chr(9)||'storage (initial '||&initial_extent||'K next '||&next_extent||
	'K pctincrease 0) pctfree '||&pct_free||';'
	from dual;
spool off
drop table cindex_temp;
exit