Oracle 索引可以比表大

上午和DBA 1群的王海讨论了下 索引和 表的关系，他说从网上看到索引比表大。

 

一般情况下，索引应该是比表小的。google 一下，看到了warehouse大哥的一篇blog，里面有个测试，例子中的索引确实比表大。　

 

Oracle 索引的维护

http://blog.csdn.net/xujinyang/article/details/6829355

 

 

先看一段官网的说明：

14.1.7 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 Database might use the existing index instead of the base table to improve the performance of the index build.

 

However, in some cases using the base table instead of the existing index is beneficial. 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.

 

To avoid calling DBMS_STATS after the index creation or rebuild, include the COMPUTE STATISTICS statement on the CREATE or REBUILD. You can use the Oracle Enterprise Manager Reorg Wizard to identify indexes that require rebuilding. The Reorg Wizard can also be used to rebuild the indexes.

 

 

出自Oracle 联机文档：

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/data_acc.htm#PFGRF94781

 

 

 

测试：

 

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

----------

346

 

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

LEAF_BLOCKS

-----------

        410

 

这里index就比表大，表 t只有id和name，而index也包含了id和name，但是index还包含了rowid。

 

 

SQL> delete from t where id<=5000;

已删除4931行。

 

SQL> alter table t move ;

表已更改。

 

SQL> analyze table t compute statistics;

analyze table t compute statistics

*

第 1 行出现错误:

ORA-01502: 索引 'SYS.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

----------

346

 

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

LEAF_BLOCKS

-----------

388

 

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

已创建72917行。

 

SQL> commit;

提交完成。

 

SQL> analyze table t compute statistics for all indexes;

表已分析。

 

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

BLOCKS

----------

346

 

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

LEAF_BLOCKS

-----------

825

 

查看索引占用的空间大小：

SQL> select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name = 'IDX_T';

(SUM(BYTES)/1024/1024)||'MB'

------------------------------------------

7MB

 

查看表占用空间大小：

SQL> select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name = 'T';

 

(SUM(BYTES)/1024/1024)||'MB'

------------------------------------------

6MB

 

通过执行dml操作，效果更加明显，index显然比表t大。

 

 

 

 

------------------------------------------------------------------------------