generate index script

[pando]

Hi

I am doing a DWH production - development db syncronization/reorganization and I am facing a small problem, when I want to generate the indexes I am having a really tough time due to some indexes is generated by PK and some are not, it gets worse because some indexes are named PK_BLAH_BLAH but they are not really PK constraints so basically I am trying to write a index generation script based on two conditions

1. IF the index_name = constraint_name then generate:
ALTER TABLE XXXXX ADD CONSTRAINT INDEX_NAME PRIMARY KEY (COLUMN_NAME) TABLESPACE YYYYY STORAGE (------------------------)

2. ELSE generate
CREATE UNIQUE/NONUNIQUE INDEX INDEX_NAME
TABLESPACE YYYYY
STORAGE (-----------------------)

I am trying to do with SQL but it seems impossible, has anyone got experience with this kind of logic in SQL?

Basically I have to do a correlated query and compare index_name from dba_indexes and constraint_name from dba_constraints and if they both match then I would generate the script using option 1 else option 2


Cheers

[pipo]

why don't you look at constraint_type since it's what is important to you ??

[pando]

Hm I dont see what you mean, I have to distinguish between indexes generated by PK and normal btree indexes

[pando]

Oh yes I think this works


select a.index_name,, b.constraint_name, c.column_name,
decodev(a.index_name, b.constraint_name, ' alter blah blah ', 'create '||index_name||blah blah)
from user_indexes a, user_constraints b, user_cons_columns c
where a.table_name=b.table_name
and b.constraint_name=c.constraint_name
and b.constraint_type='P'
and tablespace_name='IDX_128K_1'
/

[APylypenko]

I had the same problem and here's my solution:

you'll have to write few sql scripts and run them in sqlplus
(they might need just a bit of cleaning thou)

first one:
set def on

set term on
set feedback on
set heading on
set echo off


accept owner_name char prompt 'Enter schema name :'

set timing off
set time off

set term off
set feedback off
set heading off
set echo off

spool db_def.sql

select 'define db =' ||''''||name||'''' from v$database;

spool off

@db_def.sql


set pagesize 30000
set linesize 200


set term off
set feedback off
set heading off
set echo off

set verify off

SET SERVEROUTPUT ON SIZE 1000000
set term off
set feedback off
set heading off
set echo off

spool slave_ind.sql
prompt set term on
prompt set feedback on
prompt set heading on
prompt set echo on
prompt spool ind.lst

select 'define spool = '||''''||'../Db_Objects_Current_Version/'||
'&db'||'/'||owner||'/indexes/'||index_name||'.sql'||''''||chr(10)||
'define Table = '||''''||table_name||''''||chr(10)||
'define Index = '||''''||index_name||''''||chr(10)||
'define Owner = '||''''||owner||''''||chr(10)||
'start INDgenerator.sql'||chr(10)||
'spool off'
from dba_indexes i
where table_owner=upper('&owner_name')
and PARTITIONED ='NO'
and index_name NOT IN (select constraint_name
FROM dba_constraints c
WHERE constraint_type IN ('P')
AND c.table_name = i.table_name
and c.owner = i.table_owner)
order by index_name
;


prompt spool off
SPOOL OFF
set term on
set feedback on
set heading on
set echo on

@slave_ind


and the second one called INDgenerator.sql :

CLEAR COLUMN;
COLUMN index_name NOPRINT;
COLUMN seq NOPRINT;
BREAK ON table_name SKIP 1;

@off
spool &spool
-----------------------
SELECT index_name, 0 seq, 'CREATE ' || DECODE(uniqueness,
'UNIQUE', uniqueness,null ) || ' INDEX '
|| table_owner||'.'|| index_name FROM dba_indexes
WHERE index_name = '&index'
union
SELECT index_name, 1 seq,
' ON '|| table_owner||'.'|| table_name
FROM dba_indexes
WHERE index_name = '&index'
UNION
SELECT a.index_name,10+ column_position seq,
DECODE (column_position, 1,' (' ,' ') ||
LOWER( column_name ) ||
decode ( column_position, total_columns, ')', ',' )
FROM dba_ind_columns a, dba_indexes b,
( SELECT count(1) total_columns
FROM dba_ind_columns
where index_name = '&index'
) c
WHERE a.index_name = b.index_name
and b.index_name = '&index'
UNION
SELECT a.index_name, 9000 seq,
' PCTFREE '||pct_free||'
INITRANS '||ini_trans||'
MAXTRANS '||max_trans||'
TABLESPACE '||TABLESPACE_NAME||'
STORAGE ( INITIAL '||INITIAL_EXTENT/1024||'K
NEXT '||NEXT_EXTENT/1024||'K
MAXEXTENTS '||MAX_EXTENTS||'
MINEXTENTS '||MIN_EXTENTS||'
PCTINCREASE '||PCT_INCREASE||'
);'
from dba_indexes a
where index_name = '&index'
ORDER BY index_name, seq;

spool off
@on



the @on and @off scripts have this settings set ot on/off
set term off
set feedback off
set heading off
set echo off

These are the scripts the way I used them, you may want to modify the spool, path, etc

this will generate a index creation script for nonPK indexes.

I also have a script that generates the alter table add pk constraint using index, you can use it if you want, here it is:

first script:
set def on

set term on
set feedback on
set heading on
set echo off


accept owner_name char prompt 'Enter schema name :'

set timing off
set time off

set term off
set feedback off
set heading off
set echo off

spool db_def.sql

select 'define db =' ||''''||name||'''' from v$database;

spool off

@db_def.sql


set pagesize 30000
set linesize 200




SET SERVEROUTPUT ON SIZE 1000000

set term off
set feedback off
set heading off
set echo off

spool slave_pk.sql
prompt set term on
prompt set feedback on
prompt set heading on
prompt set echo on
prompt spool pk.lst

select 'define spool = '||''''||'../Db_Objects_Current_Version/'||
'&db'||'/'||owner||'/PKeys/'||table_name||'.sql'||''''||chr(10)||
'define Table = '||''''||table_name||''''||chr(10)||
'define Owner = '||''''||owner||''''||chr(10)||
'start PKgenerator.sql'||chr(10)||
'spool off'
from dba_tables where owner=upper('&owner_name')
and PARTITIONED ='NO'
;

prompt spool off
SPOOL OFF
set term on
set feedback on
set heading on
set echo on

@slave_pk


and the second one called PKgenerator.sql:

CLEAR COLUMN;
COLUMN table_name NOPRINT;
COLUMN seq NOPRINT;
BREAK ON table_name SKIP 1;

@off
spool &spool
SELECT table_name,
7000 seq,
'ALTER TABLE '||owner||'.'||TABLE_NAME
FROM dba_constraints
WHERE constraint_type IN ('P')
AND table_name = '&table'
and owner = '&owner'
union
SELECT table_name,
7001 seq,
' ADD CONSTRAINT '||constraint_name
FROM dba_constraints
WHERE constraint_type IN ('P')
AND table_name = '&table'
and owner = '&owner'
union
SELECT table_name,
8000 seq,
' PRIMARY KEY ( '
FROM dba_constraints
WHERE constraint_type IN ('P')
AND table_name = '&table'
and owner = '&owner'
UNION
SELECT a.table_name, (8000 + POSITION) seq, ' ' ||
lower (column_name) || DECODE (position, total_cons, ' )', ',')
FROM dba_cons_columns a, dba_constraints b,
( SELECT a.constraint_name, count(a.constraint_name) total_cons
FROM dba_cons_columns a, dba_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type IN ('P')
AND b.table_name = '&table'
GROUP by a.constraint_name ) c
WHERE a.constraint_name = b.constraint_name
AND a.constraint_name = c.constraint_name
AND constraint_type IN ('P')
AND b.table_name = '&table'
and b.owner = '&owner'
union
SELECT a.table_name, 9000 seq,
' USING INDEX
PCTFREE '||pct_free||'
INITRANS '||ini_trans||'
MAXTRANS '||max_trans||'
TABLESPACE '||TABLESPACE_NAME||'
STORAGE ( INITIAL '||INITIAL_EXTENT/1024||'K
NEXT '||NEXT_EXTENT/1024||'K
MAXEXTENTS '||MAX_EXTENTS||'
MINEXTENTS '||MIN_EXTENTS||'
PCTINCREASE '||PCT_INCREASE||'
);'
from dba_indexes a
where a.table_name = '&table'
and a.owner = '&owner'
and index_name = (select constraint_name
FROM dba_constraints
WHERE constraint_type IN ('P')
AND table_name = '&table'
and owner = '&owner')
ORDER BY table_name, seq
;
spool off
@on




and BTW I have these kind of scripts for tables also, and for partitioned tables,pk, indexes, if anybody interested I can be reached at mist139@hotmail.com