如何为新索引初始大小做适当评估(1)

本系列有两部分，我们将在第一部分中讨论，如何为一个新的索引的初始大小做一个适当的评估。已存在的索引如何? 它们的大小是否合适?在现有系统中，正如我们最常见的：索引被依据某些评估制作出来，现在，我们需要来计算它的大小是否合适。 Jijy2{Km  
k@w'krho  
Validate Structure xBV]  
3W`o)+Z)  
首先，我们用“analyze index … validate structure”这个命令去校验索引的大小。此命令会将结果放入 index_stats 表中，并生成有关索引的详细视图。使用这个方法，我遇到的问题是：一，它运行缓慢；二，“analyze”这个命令会丢失客户；三，结果会因分区索引或子分区索引而不同。因此我打算找寻一个新的方法。 ,^Kfgzc  
5Xr3 &  
dba_indexes b9)eIY.  
=@AsSz?W  
通常情况下，我们至少一周分析一次表，一般都在晚上。因此，我们所需要的统计都已经可用。 ~1 )v+}#(  
}`[}O  
从8.0开始，ORACLE把 num_rows 加入 dba_indexes 表. 之前，我们需要将dba_indexes 和 dba_tables 做一个联合查询才能得到一个索引的num_rows 。在执行这个操作时，我们没有考虑到索引值可能为空而没有被包括在内的情况。索引的行数有可能会小于或等于表的行数。因此，我们所添加的区域所占用的空间大小就最终决定了索引的空间大小。 P `I v5~  
9vEn
<s  
运行下边的语句，可以查看当前表TASK的索引用到的空间： S6F|-;a  
'V$lFh^  
select a.owner,a.index_name,a.leaf_blocks,a.num_rows GmkiA{v  
from dba_indexes a 0*'F.8 '  
where a.owner not in ('SYS','SYSTEM') U$lJ+{P  
and a.index_type!='BITMAP' H| D[D~  
and a.last_analyzed is not null uzc-CFt  
and a.table_name = 'TASK'; xU(kW Q_  
\L/ Sy  
OWNER INDEX_NAME LEAF_BLOCKS NUM_ROWS xsQVA5l  
ORADBA TASK_APPT_FK 65 23771 Bl`Ky0+.  
ORADBA TASK_CENTER_FK 99 45975 ^^T XjV$  
ORADBA TASK_CLIENT_FK 101 45975 #+1N<elMS  
ORADBA TASK_ENC_FK 117 45975 m2lgV1  
ORADBA TASK_TYPE_FK 74 45975 tbX {x ?   
ORADBA TASK_DT_REQ 84 45975 lrB'.nijAt  
^ai&{kD  
看这里，我们查出当前索引 task_appt_ft 的23771行 都在65 个块中，可能有不用的空间在这些 65个块中，但是我们稍后将会处理。这意味着，我们在每个块中大约拥有366行 (23,771/65) 。使用在上一章--B-Tree 大小中我们提到的逻辑，如果我们有超过 366个块，那么我们会有一个branch blocks。 但是这个索引只有这个root block。而索引的真正需求是65 leaf_blocks+1 root block =66个块，正像它目前被定义的一样。 YQyw
_Doq  
-</Qb0ZR  
那么索引到底需要多大空间呢？我们可以从dba_segments 找到答案，让我们把它加入到我们的查询中； ,! 6% !Dq  
T}+}wWUI  
select a.owner,a.index_name,a.leaf_blocks,a.num_rows,b.blocks `z> mMyzc  
from dba_indexes a C5d~J
q  
,dba_segments b B5,>{  
where a.owner not in ('SYS','SYSTEM') b |6s2R  
and a.index_type!='BITMAP' Qx (QF_ $  
and a.last_analyzed is not null &u4xON ij  
and a.table_name = 'TASK' E -[ G  
and a.owner = b.owner bpp$GX  
and a.index_name = b.segment_name; <4b <,,  
&<01;n{  
OWNER INDEX_NAME LEAF_BLOCKS NUM_ROWS BLOCKS R)v "R]c  
ORADBA TASK_APPT_FK 65 23771 175 ; GM<7n  
ORADBA TASK_CENTER_FK 99 45975 145 t7i} /  
ORADBA TASK_CLIENT_FK 101 45975 205 ?47/tp/t  
ORADBA TASK_ENC_FK 117 45975 215 Nt%H.l}H  
ORADBA TASK_TYPE_FK 74 45975 195 Yu[@mY  
ORADBA TASK_DT_REQ 84 45975 86 B4>a?*oO  
~L]c}bI  
我们会发现 task_appt_fk 索引需要 66个块，但是现在却占有了175个块。因此，如果我们按照正确的大小重新建立索引，我们可以释放109个块。而所有的这些则是建立于先前运行的没有丢失任何用户的统计的基础之上的。我可以选择一天中的任何时间。 #[{}ks{DB  
jK;!l!jB  
重新指定大小和碎片整理的逻辑步骤： ]I}0ce'[  
38Ky#LcIb  
那么，我们使用这些新的发现以做得更好？这里有一个最好的案例：我们制作一个完整的表空间时，可以使重新设定大小和整理表空间碎片同时进行。具体步骤如下： Wn&qJGP  
t,36
eu  
• 根据dba_indexes索引的大小，在不同得表空间中重新创建索引。这会去掉索引中任何被删除的空间。 r uc[jJ  
• Coalesce表空间 r`2Rcn?  
• 通过分析这些索引得到基于分钟的统计 o% Wp> ;~  
• 把索引重新创建回index tablespace Q7u H'G0d=  
B,^"F^h<g  
我们需要一个简单的方法来详细说明刚刚进行过的每一步。我通常只是把名字放在这个仅仅为了练习而存在的表中。当你完成时，确认这个临时表已删除。稍后，我们也pull out 块的大小，以使语句能够快一些：
.>6<pG  
create table t_names &`G>Q D  
storage (initial 64K next 64K pctincrease 0) *Gwo0 +  
pctfree 0 pctused 80 q"B*o` J^  
as select owner,index_name GrzL:FV4  
from dba_indexes g'XwoBX  
where tablespace_name = 'HRX'; p# FftV}  
R>kE Kp$  
variable block_size number jm:#uz%*  
begin 9R`Xm='7  
select to_number(value) into :block_size ^CIi /1  
from v$parameter where name = 'db_block_size'; \%9dQLO =  
end; SXX Q;  
/ 0~$PdL!  
i%*9i?8