ITPub博客

首页 > 数据库 > 数据库开发技术 > index有时候可能会比表大!

index有时候可能会比表大!

原创 数据库开发技术 作者:warehouse 时间:2008-04-23 16:50:44 0 删除 编辑

之前没有想过这个问题,第一次看到这样的说法是在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.


Note:

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.


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大!

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

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    717
  • 访问量
    5115757