oracle索引大小暴增_oracle 如何预估将要创建的索引的大小

一.1oracle 如何预估将要创建的索引的大小

oracle 提供了2种可以预估将要创建的索引大小的办法：

①  利用包 Dbms_space.create_index_cost 直接得到

②  利用11g新特性 Note raised when explain plan for create index

下边分别举例说明。

一.2环境说明

[[email protected]_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 26 15:58:06 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release11.2.0.3.0- 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL>create table test_index_size as select * from dba_objects;

Table created.

SQL>EXEC DBMS_STATS.gather_table_stats(ownname => ‘SYS‘,tabname => ‘TEST_INDEX_SIZE‘);

PL/SQL procedure successfully completed.

一.3第一种Dbms_space.create_index_cost

脚本：

declare

l_index_ddlvarchar2(1000);

l_used_bytesnumber;

l_allocated_bytesnumber;

begin

dbms_space.create_index_cost(ddl=>‘create index idx_t on sys.test_index_size(object_id) ‘,

used_bytes=>l_used_bytes,

alloc_bytes=>l_allocated_bytes);

dbms_output.put_line(‘used= ‘||l_used_bytes||‘bytes‘||

‘     allocated= ‘||l_allocated_bytes||‘bytes‘);

end;

/

实验：

SQL> set serveroutput onSQL> declare

2    l_index_ddlvarchar2(1000);

3    l_used_bytesnumber;

4    l_allocated_bytes number;

5  begin

6    dbms_space.create_index_cost(ddl=> ‘create index idx_t on sys.test_index_size(object_id) ‘,

7used_bytes=> l_used_bytes,

8alloc_bytes => l_allocated_bytes);

9    dbms_output.put_line(‘used= ‘ || l_used_bytes || ‘bytes‘ ||

10‘allocated= ‘ || l_allocated_bytes || ‘bytes‘);

11  end;

12  /

used= 383105bytes     allocated= 2097152bytes

PL/SQL procedure successfully completed.

SQL>

PL/SQL 过程已成功完成。说明:  used_bytes给出索引数据实际表现的字节数。allocated 是当实际创建索引时在表空间中实际占用的字节数。

一.4第二种11g新特性:Note raised when explain plan for create index

这是一个挺实用的小特性，在11g r2中使用explain plan for create index时Oracle会提示评估的索引大小(estimated index size)了：

SQL> set linesize 200 pagesize 1400;

SQL>  explain plan for create index idx_t on sys.test_index_size(object_id) ;

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

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

Plan hash value: 32582980

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

| Id  | Operation| Name  | Rows  | Bytes | Cost (%CPU)| Time    |

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

|   0 | CREATE INDEX STATEMENT |       | 76621 |   374K|   350(1)| 00:00:05|

|   1 |  INDEX BUILD NON UNIQUE| IDX_T |       |       |||

|   2 |   SORT CREATE INDEX    |       | 76621 |   374K|||

|   3 |    INDEX FAST FULL SCAN| IDX_T |       |       |||

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

Note

-----

- estimated index size: 2097K bytes

14 rows selected.

一.5创建真实索引

SQL> create index idx_t on sys.test_index_size(object_id) ;

Index created.

SQL>  analyze index IDX_T validate structure;

Index analyzed.

SQL> select bytes from dba_segments where segment_name=‘IDX_T‘;

BYTES

----------

2097152

可以看到2种办法给出的索引评估大小与实际索引占用空间差别不大，但这里有个前提条件就是预估索引大小之前必须对表进行分析过。

原文：http://www.cnblogs.com/lhrbest/p/4548632.html