---
title: "分析索引的碎片"
author: "winzenghua"
date: "2009-12-30T17:29:00Z"
tags: []
slug: "untitled"
draft: false
lastmod: "2009-12-30T17:29:00Z"
id: 987824
url: "https://java4gis.iteye.com/blog/987824"
---

oracle 10g

先建立个统计表

create table T\_ANALYZ\_MONITOR\_INDEX  
(  
F\_INDEX\_NAME VARCHAR2(50),  
F\_DEL\_LF\_ROWS NUMBER,  
F\_LF\_ROWS NUMBER,  
F\_RATE NUMBER(4,2),  
F\_MONITOR\_DATE DATE default sysdate not null  
);

再建个历史表

create table t\_analyz\_index\_stats as select * from index\_stats

做个分析过程 查出表并且 分析 插入历史表 统计删除比率到 统计表

create or replace procedure P\_ANALYZ\_DAY\_INDEX\_SATAS is  
v\_sql varchar2(100);  
Begin

for a in (Select INDEX\_NAME From User\_Indexes Where index\_type&lt;&gt;'LOB') loop  
v\_sql := ' analyze index ' || a.index\_name || ' validate structure';  
execute immediate v\_sql;

Insert Into T\_ANALYZ\_INDEX\_STATS  
Select * From Index\_Stats;

insert into T\_ANALYZ\_MONITOR\_INDEX(F\_INDEX\_NAME, F\_DEL\_LF\_ROWS, F\_LF\_ROWS, F\_RATE)  
select name,del\_lf\_rows,lf\_rows, round(del\_lf\_rows * 100 / decode((lf\_rows + del\_lf\_rows),0,1), 2)  
from index\_stats;

End loop;

end;

注意所查到的索引是在同个用户名下的