--################################################################
--Description: 重建索引过程
--Author: zzq
--Date: 2006-12-6
--Version: 0.1
--################################################################
--建立表analyze_index,用来存储index分析后的信息
create table analyze_index
(name VARCHAR2(30),
btree_space number,
height number,
pct_used number,
rate number)
--这个过程首先对索引进行分析,然后将临时表index_status的数据存储到analyze_index
中,然后对有问题索引进行重建,问题索引指的是btree_space > 8192
and (height > 3 or pct_used < 75
or (del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100) > 20)具体请查询相关文档.重建后的索引要重新分析才可以插入到index_status表中.
create or replace procedure p_rebuild_index
as
cursor m_cur is
select distinct index_name from user_ind_columns;
m_ind_name user_ind_columns.index_name%type;
cursor m_cur1 is
select * from analyze_index;
i_sql varchar2(1000);
begin
i_sql:='truncate table analyze_index';
EXECUTE immediate i_sql;
open m_cur;
loop
fetch m_cur into m_ind_name;
exit when m_cur%NOTFOUND;
i_sql:='ANALYZE INDEX '||m_ind_name||' VALIDATE STRUCTURE';
EXECUTE immediate i_sql;
insert into analyze_index(name,btree_space,height,pct_used,rate)
select name,btree_space,height,pct_used,del_lf_rows/(decode(lf_rows,0,1,lf_rows))
from INDEX_STATS
where btree_space > 8192
and (height > 3 or pct_used < 75
or (del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100) > 20)
;
commit;
end loop;
close m_cur;
for m_analyze_ind in m_cur1
loop
if true then
i_sql:='alter index '||m_analyze_ind.name||' rebuild';
EXECUTE immediate i_sql;
end if;
end loop;
i_sql:='truncate table analyze_index';
EXECUTE immediate i_sql;
open m_cur;
loop
fetch m_cur into m_ind_name;
exit when m_cur%NOTFOUND;
i_sql:='ANALYZE INDEX '||m_ind_name||' VALIDATE STRUCTURE';
EXECUTE immediate i_sql;
insert into analyze_index(name,btree_space,height,pct_used,rate)
select name,btree_space,height,pct_used,del_lf_rows/(decode(lf_rows,0,1,lf_rows))
from INDEX_STATS
where btree_space > 8192
and (height > 3 or pct_used < 75
or (del_lf_rows/(decode(lf_rows,0,1,lf_rows)) *100) > 20)
;
commit;
end loop;
close m_cur;
exception
when others then
if m_cur%isopen then
close m_cur;
end if;
if m_cur1%isopen then
close m_cur1;
end if;
rollback;
DBMS_OUTPUT.PUT_LINE('[p_rebuild_index] '||sqlerrm);
end;
/
show errors;