index为什么可能会比table大很多

因为index pctused其实为0[@more@]

SQL> create table t1(id int,name char(2000)) tablespace users pctfree 50;

Table created.
--指定pctfree=50目的是想让2条数据使用一个data block
SQL> insert into t1 values(1,rpad('a',2000,'+'));

1 row created.

SQL> insert into t1 values(2,rpad('b',2000,'+'));

1 row created.

SQL> insert into t1 values(3,rpad('c',2000,'+'));

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1 values(4,rpad('d',2000,'+'));

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1 values(5,rpad('e',2000,'+'));

1 row created.

SQL> commit;

Commit complete.

SQL> select id,substr(name,1,2),rowid from t1;

ID SUBSTR(NAME,1,2) ROWID
---------- ---------------- ------------------
1 a+ AAAEqDAAEAAAACtAAA
2 b+ AAAEqDAAEAAAACtAAB
3 c+ AAAEqDAAEAAAACuAAA
4 d+ AAAEqDAAEAAAACuAAB
5 e+ AAAEqDAAEAAAACvAAA
--上面的查询结果中rowid的结果显示2条数据使用一个data block，也就说5条数据使用3个block就够了，我的这个例子里面index leaf block需要5个。
SQL> create index idx_t1 on t1(name) tablespace users pctfree 50;

Index created.

SQL> delete from t1 where id in (2,4);

2 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into t1 values(6,rpad('f',2000,'+'));

1 row created.

SQL> commit;

Commit complete.

SQL> insert into t1 values(7,rpad('g',2000,'+'));

1 row created.

SQL> commit;

Commit complete.

SQL> select id,substr(name,1,2),rowid from t1;

ID SUBSTR(NAME,1,2) ROWID
---------- ---------------- ------------------
1 a+ AAAEqDAAEAAAACtAAA
6 f+ AAAEqDAAEAAAACtAAB
3 c+ AAAEqDAAEAAAACuAAA
7 g+ AAAEqDAAEAAAACuAAB
5 e+ AAAEqDAAEAAAACvAAA
--很显然新插入的id=6,7的数据可以重复使用id=2,4删除以后的空间，也就是说不需要再为id=6,7的数据重新分批新的block，但是index就不一样了。
SQL> select object_id from dba_objects where object_name='IDX_T1';

OBJECT_ID
----------
19077

SQL> alter session set events 'immediate trace name treedump level 19077';

Session altered.

SQL>
--====================
branch: 0x10000b3 16777395 (0: nrow: 5, level: 1)
leaf: 0x10000b4 16777396 (-1: nrow: 1 rrow: 1)
leaf: 0x10000b5 16777397 (0: nrow: 1 rrow: 0)
leaf: 0x10000b6 16777398 (1: nrow: 1 rrow: 1)
leaf: 0x10000b7 16777399 (2: nrow: 1 rrow: 0)
leaf: 0x10000b8 16777400 (3: nrow: 3 rrow: 3)
----- end tree dump
--=======================
dba_indexes里面没有pctused，创建索引也不能指定pctused，但是index的pctused其实是0，
也就是说index leaf block里面只有为空的时侯这个leaf block才有可能被重复使用，否则的
话index的leaf block只能增加，也就是说当表里面的数据被删除之后，表使用的block里的空间
随时可能被重复使用，但是index里面的leaf block记录的数据只是被标记一下数据被删除了，真真
空间没有被释放，其实释放了也不能被使用，只有整个leaf block空了之后才能被重用。
这就是index比表大的真实原因，而且index可能会比表大很多，这都是有可能的，我在给联想
优化系统的时侯，他们其中有一个表是200m左右，当时上面的3个index都是4g多。

写了这么多其实一句话：对index而言没有update操作，只有delete和insert，准确的说是只有insert，delete数据之后index的空间只有index被重建之后才能释放。因为对于index只有insert，所以对update和delete频繁的表上重建index是必须的，否则index可能会非常大，这样index的的效率就会很差了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1060085/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1060085/