index有時候可能會比表大！

之前沒有想過這個問題，第一次看到這樣的說法是在piner的面試題中。當時覺得有點意思，這幾天細讀doc，上面也提到了這樣的說法：

Consider an index on a table on which a lot of DML has been performed. Because of the DML, the size of the index can increase to the point where each block is only 50% full, or even less. If the index refers to most of the columns in the table, then the index could actually be larger than the table.

[@more@]

doc：

Re-creating Indexes

You might want to re-create an index to compact it and minimize fragmented space, or to change the index's storage characteristics. When creating a new index that is a subset of an existing index or when rebuilding an existing index with new storage characteristics, Oracle might use the existing index instead of the base table to improve the performance of the index build.

However, there are cases where it can be beneficial to use the base table instead of the existing index. Consider an index on a table on which a lot of DML has been performed. Because of the DML, the size of the index can increase to the point where each block is only 50% full, or even less. If the index refers to most of the columns in the table, then the index could actually be larger than the table. In this case, it is faster to use the base table rather than the index to re-create the index.

Use the ALTER INDEX ... REBUILD statement to reorganize or compact an existing index or to change its storage characteristics. The REBUILD statement uses the existing index as the basis for the new one. All index storage statements are supported, such as STORAGE (for extent allocation), TABLESPACE (to move the index to a new tablespace), and INITRANS (to change the initial number of entries).

Usually, ALTER INDEX ... REBUILD is faster than dropping and re-creating an index, because this statement uses the fast full scan feature. It reads all the index blocks using multiblock I/O, then discards the branch blocks. A further advantage of this approach is that the old index is still available for queries while the rebuild is in progress

--===================================

SQL> create table t(id ,name) as select object_id , object_name from dba_objects
;

表已建立。

SQL> desc t
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

ID NUMBER
NAME VARCHAR2(128)

SQL> create index idx_t on t(id , name);

索引已建立。

SQL> analyze table t compute statistics;

表已分析。

SQL> select blocks from user_tables where table_name='T';

BLOCKS
----------
70

SQL> select leaf_blocks from user_indexes where index_name='IDX_T';

LEAF_BLOCKS
-----------
77

已經看出了index比表大，為什麼這樣？這裡主要是t只有id和name，而index也包含了id和name，但是別忘了index還包含了rowid！

SQL> delete from t where id<=5000;

已刪除4928行。

SQL> commit;

提交完成。

SQL> alter table t move ;

表已更改。

SQL> analyze table t compute statistics;
analyze table t compute statistics
*
第 1 行出現錯誤:
ORA-01502: 索引 'XYS.IDX_T' 或這類索引的分割槽處於不可用狀態

SQL> alter index idx_t rebuild;

索引已更改。

SQL> analyze table t compute statistics for all indexes;

表已分析。

SQL> select blocks from user_tables where table_name='T';

BLOCKS
----------
70

SQL> select leaf_blocks from user_indexes where index_name='IDX_T';

LEAF_BLOCKS
-----------
55

SQL> insert into t select object_id , object_name from dba_objects;

已建立15810行。

SQL> commit;

提交完成。

SQL> analyze table t compute statistics for all indexes;

表已分析。

SQL> select blocks from user_tables where table_name='T';

BLOCKS
----------
70

SQL> select leaf_blocks from user_indexes where index_name='IDX_T';

LEAF_BLOCKS
-----------
148

SQL>

透過執行dml操作，這次效果更加明顯，index顯然比表t大！