Re: creating indexes script (LONG)

From: Brett Neumeier <bogus_at_address.for.spam>
Date: 1997/05/09
Message-ID: <337395BB.1778@address.for.spam>#1/1

Eric W. Worden wrote:
>
> anyone have any experience rebuilding their indexes into other
> tablespaces? i'm working on a script, but i'm stuck when it comes to
> composite indexes and how to populate the columns' field in the create
> index statement. this is going to come in handy for 800 indexes.
>
> appreciate the help.
>

Not a problem -- you need to select from DBA_IND_COLUMNS as well. I have one which generates DDL to rebuild all the indexes in the database except those which support unique or PK constraints; modify to taste.

This thing works by union'ing together all the various lines; it orders everything by some extra columns which get filtered out by sql*plus because of the "noprint" commands. You'll have to modify the "where" clause in each query to limit the number of indexes which are generated, and the "tablespace" clause of course.

-bn

---

column dummy1 noprint
column dummy2 noprint
column dummy3 noprint
column dummy4 noprint
spool c_index.sql
prompt REM File: c_index.sql
prompt REM This temporary SQL script was generated by g_index. select TO_CHAR(sysdate,'"REM Generated on" MM/DD/YYYY "at" HH24:MI')

        from DUAL
/
prompt REM
prompt

select owner dummy1, index_name dummy2, 10 dummy3, 1 dummy4,

	'create ' ||
	decode (uniqueness, 'UNIQUE', 'unique ') ||
	'index ' || owner || '.' || index_name 
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 20, 1,

	'     on ' || table_owner || '.' || table_name || ' ('
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select a.owner, a.index_name, 30, b.column_position,

	'           ' ||
	decode (b.column_position, 1, '  ', ', ') ||
	b.column_name
	from dba_indexes a, dba_ind_columns b
	where a.owner = b.index_owner 
		and a.index_name = b.index_name
		and a.table_type = 'TABLE'
		and a.owner NOT IN ('SYS','SYSTEM')
		and a.index_name NOT IN (
			SELECT constraint_name from DBA_CONSTRAINTS inner
				where a.owner = inner.owner 
				and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 40, 1,

	' )'
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 50, 1,

	'    initrans ' || ini_trans
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 60, 1,

	'    maxtrans ' || max_trans
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 70, 1,

	'    tablespace ' || tablespace_name
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 80, 1,

	'    pctfree ' || pct_free
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 90, 1,

	'  storage ( initial ' || initial_extent || ' next ' ||
	next_extent || ' minextents ' || min_extents 
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 100, 1,

	'      maxextents ' || max_extents || ' pctincrease ' ||
	pct_increase || ' )'
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

UNION
select owner, index_name, 110, 1,

	'/'
	from dba_indexes
	where owner NOT IN ('SYS','SYSTEM')
	and table_type = 'TABLE'
	and index_name NOT IN (
		SELECT constraint_name from DBA_CONSTRAINTS inner
			where dba_indexes.owner = inner.owner 
			and inner.constraint_type in ('P','U'))

/

---

Received on Fri May 09 1997 - 00:00:00 CDT