为什么索引会比其所对应的表大？

biti_rainy · 发表于 2002-12-23 16:18

索引到底保存了什么东西？

键值+rowid(18bytes) + 树结构 ……

一个表是否还存在多个索引？
索引列占行的比例多大？
update索引列为全新的值很多?或者delete 后 insert 新值很多?

jjddlliang · 发表于 2002-12-23 16:31

最初由 biti_rainy 发布
[B]索引到底保存了什么东西？

键值+rowid(18bytes) + 树结构 ……

一个表是否还存在多个索引？
索引列占行的比例多大？
update索引列为全新的值很多?或者delete 后 insert 新值很多? [/B]

这只能拿一张表来作为例子了：
SQL> desc p_lszzpkc1;
Name                         Null? Type
------------------------------- -------- ----
CPDH                                  VARCHAR2(30)
GXDH                                  VARCHAR2(5)
BMDH                                  VARCHAR2(2)
ZZSL                                  NUMBER
WGSL                                  NUMBER
SCPH                                  VARCHAR2(10)
YF                                     VARCHAR2(10)
SQL> select count(*) from p_lszzpkc1;

  COUNT(*)
----------
738857
这个表只有一个唯一索引(yf,bmdh,gxdh,cpdh,scph)

我刚才将索引重建过了，这个索引就只占用31。64M，
但是REBUILD没有变化，什么原因呀？

biti_rainy · 发表于 2002-12-23 16:38

除了2个 number 类型的字段
其余的全部被包含在索引里面了

键值+rowid(18bytes) + 树结构 ……

你认为呢，是索引大还是表大？

别人给出的建议总是可能有意义的，先想想别人为什么要这么回答 ?

jjddlliang · 发表于 2002-12-23 16:52

哦！
谢谢了！批评的是，由于接触ORACLE时间短，还请多多指教，
象这种情况，我应该怎么做呢？
我删了几个索引，重建过，应该会好点吧！

biti_rainy · 发表于 2002-12-23 17:09

还是没有明白！

idler · 发表于 2002-12-23 17:21

很正常的，一个表经常INSERT和DELETE，它的索引也会进行修改的
从METALINK摘的一些关于INDEX的INSERT和DELETE

Index growth could be due to the manner in which Oracle splits the blocks as the index is populated. Indexes are created as b*trees. The only balancing occurs as a 50:50 block splits. Each leaf block is allocated a range of key values. When a value is deleted the space is not immediately available for any new key values - only for ones that fall in the existing range for the block. A block is only placed back on the free list when all the index entries for that block are deleted. "deleted" means that the space is available for reuse but the data is not zeroed out. When a new value is inserted, if the key "fits" into an existing leaf block then it will attempt to place it in that block. This algorithm is not particularly aggressive. When a block split is required it will split the data in the first block 50-50. Half of the data will be kept in the original block and the other half placed in the new (split) block. This algorithm causes the index growth observed and can double the size of the index overtime.

重建是最好的方法了。

另那个脚本试了没有什么效果，怎么不回答我