index為什麼可能會比table大很多

warehouse發表於2013-01-13
因為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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章