标题: 如何用一个SQL就生成创建所有索引的脚本? [打印本页] 作者: jiangjinlu 时间: 2006-11-09 09:49 标题: 如何用一个SQL就生成创建所有索引的脚本? 如果一个索引的索引列超过了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;