如何用一个SQL就生成创建所有索引的脚本?

jiangjinlu

如果一个索引的索引列超过了4个列，那么这个索引本身就不是一个好的索引，不是一个合格的优化的索引，所以我们在生成索引脚本的时候，根本就不需要考虑超过4列的情况：
select lower('create '||decode(x.uniqueness,'UNIQUE','unique',' ')||' index '||x.index_name||' on '||x.table_name||'('||y.fields||') tablespace '||tablespace_name||' pct_free '||to_char(pct_free)||';')
from User_indexes x,
(select a.index_name,replace(replace(replace(a.column_name||','||b.column_name||','||c.column_name||','||d.column_name||']',',,,]',''),',,]',''),',]','') fields
from User_ind_columns a,
   (select index_name,column_name from User_ind_columns where column_position=2) b,
   (select index_name,column_name from User_ind_columns where column_position=3) c,
   (select index_name,column_name from User_ind_columns where column_position=4) d
where a.column_position=1
and a.index_name=b.index_name(+)
and a.index_name=c.index_name(+)
and a.index_name=d.index_name(+)
) y
where x.index_name=y.index_name
order by x.table_name;

结果：
1        create unique index pk_attachment on attachment(attachmentid) tablespace dmsindex pct_free 10;
2        create   index authority_entityid on authority(entityid) tablespace dmsindex pct_free 10;
3        create unique index pk_authority on authority(authorityid) tablespace dmsindex pct_free 10;
4        create   index authority_idx1 on authority(privlistid) tablespace dmsindex pct_free 10;
5        create unique index authority_idx2 on authority(entitytype,entityid,privlistid) tablespace dmsindex pct_free 10;
6        create   index authority_idx3 on authority(sourceauthorityid) tablespace dmsindex pct_free 10;
7        create unique index pk_batchmaster on batchmaster(batchmasterid) tablespace users pct_free 10;
8        create   index billing_idx2 on billing(partnerid) tablespace dmsindex pct_free 10;
9        create   index billing_idx3 on billing(relatedtrantype) tablespace dmsindex pct_free 10;