index有时候可能会比表大！

最新推荐文章于 2024-09-14 19:55:29 发布

clt3617

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量98

点赞数

文章标签：数据库面试

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