Oracle如何预估将要创建的索引和表的大小

1. 对于表和索引空间的预估，可以使用DBMS_SPACE包的CREATE_TABLE_COST和CREATE_INDEX_COST存储过程，虽然没有看这两个存储过程的实现，但猜测平均行长算法，会根据预计行数，做一些计算，字段定义算法，则会根据每个字段的长度，和预计行数，做一些计算，其实和我们手工根据这些算法，计算的方式类似，只是封装起来，便于调用。

2. CREATE_TABLE_COST根据列字段定义预估，是比较准确的，根据平均行长，并不很准确。需要注意的是，这里计算的是字段极限值，不会超过此值，但有可能实际用不了这些。

https://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247484190&idx=1&sn=6135f4254ed6e84ac3ea161e3e5066a6&chksm=ea8146edddf6cffb7e089a36594321d4b5bbe28118f9f5d7a00b629b141ab27b45d35124b3ae&mpshare=1&scene=22&srcid=0705zFvXGOGQb52Sp8TaefxO#rd

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

① 利用包 Dbms_space.create_index_cost 直接得到

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

下边分别举例说明。

1 环境说明

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.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.

2 第一种 Dbms_space.create_index_cost

脚本：

declare

l_index_ddl varchar2(1000);

l_used_bytes number;

l_allocated_bytes number;

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 on
SQL> declare

2 l_index_ddl varchar2(1000);

3 l_used_bytes number;

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) ',

7 used_bytes => l_used_bytes,

8 alloc_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 是当实际创建索引时在表空间中实际占用的字节数。

3 第二种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

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

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

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

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

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

Note

-----

- estimated index size: 2097K bytes

14 rows selected.

4 创建真实索引

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种办法给出的索引评估大小与实际索引占用空间差别不大，但这里有个前提条件就是预估索引大小之前必须对表进行分析过。

How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database? (文档 ID 1585326.1)

类型:
	状态:
	上次主更新:
	上次更新:


	HOWTO
	PUBLISHED
	2016-5-23
	2016-5-23

In this Document

Goal

Solution

Estimate The Size Of Tables

Example for using Version 1:

Example for using Version 2:

Estimate Table Size Using Enterprise Manager

Estimate The Size Of Indexes

Estimate Index Size example :

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.3 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 23-May-2016***

GOAL

How TO Estimate The Size Of Tables and Indexes Before They Actually Gets Created And Populated In The Database , to Prepare Sufficient storage available .

SOLUTION

Estimate The Size Of Tables

The CREATE_TABLE_COST procedure of the DBMS_SPACE package helps to estimate the size of the table segment ,

This procedure is used in capacity planning to determine the size of the table given various attributes , The CREATE_TABLE_COST procedure has two versions :

The first version takes the average row size of the table as argument and outputs the table size.
The second version takes the column information of the table as argument and outputs the table size.

Both Versions also Requires the following input :

the expected number of rows
pct_free setting for the table
the tablespace name where the table would be created.

Version 1
=========
DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   avg_row_size       IN NUMBER,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

Version 2
=========
DBMS_SPACE.CREATE_TABLE_COST (
   tablespace_name    IN VARCHAR2,
   colinfos           IN CREATE_TABLE_COST_COLUMNS,
   row_count          IN NUMBER,
   pct_free           IN NUMBER,
   used_bytes         OUT NUMBER,
   alloc_bytes        OUT NUMBER);

The output of the procedure contains used_bytes and alloc_bytes :

The used_bytes : represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc.
The alloc_bytes : represent the size of the table segment when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.

Example for using Version 1:

This methode requires creating the table first with some sample data to calculate the average row size of the table ,

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (9999999,'aaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaa',sysdate);

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');

select AVG_ROW_LEN from user_tables where TABLE_NAME='TEST';

AVG_ROW_LEN
-----------
68

Drop table test;

set serveroutput on

DECLARE
ub NUMBER;
ab NUMBER;
BEGIN
DBMS_SPACE.CREATE_TABLE_COST('USERS',68,100000,10,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/

Used Bytes = 8036352
Allocated Bytes = 8388608

which is around 8 MB , now lets create and populate the actual table.

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';

BYTES SEGMENT_TYPE
---------- ------------------
9437184 TABLE

which is around 9 MB

Example for using Version 2:

This example estimate a Table size with one column NUMBER (10) , two columns VARCHAR2 (30) and one column date , with pct_free=10 and row_count=100000 :

set serveroutput on

DECLARE
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;
BEGIN
cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10),
        sys.create_table_cost_colinfo('CHAR',30),
        sys.create_table_cost_colinfo('CHAR',30),
        sys.create_table_cost_colinfo('DATE',NULL));

DBMS_SPACE.CREATE_TABLE_COST('USERS',cl,100000,10,ub,ab);

DBMS_OUTPUT.PUT_LINE('Used Bytes = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/

Used Bytes = 9314304
Allocated Bytes = 9437184

which is around 9 MB , now lets create and populate the actual table.

Note : we changed VARCHAR2 to CHAR to get the maximum possible estimation .

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';

BYTES SEGMENT_TYPE
---------- ------------------
9437184 TABLE

which is around 9 MB

You may notice that version 2 of The CREATE_TABLE_COST procedure is more accurate than version 1 , which is expected because version 2 estimates the size based on the table structure which includes the column data types and length , plus the storage attributes .

Estimate Table Size Using Enterprise Manager

During table creation with Enterprise Manager, you can estimate the size of the table. This action enables you to determine whether you have sufficient room in your database or on the available disks to store the table. If you do not have room, then you can still create the table but not populate it until you have obtained the necessary storage.

To estimate the table size:

1. In the Database Objects section of the Administration page, click Tables.

The Tables page appears.

2. Click Create.

The Create Table: Table Organization page appears.

3. Select Standard, Heap Organized and click Continue.

The Create Table page appears.

4. Enter the table and schema names as well as your column names and data types, then click Estimate Table Size.

The Estimate Table Size page appears.

5. In Projected Row Count, enter the projected number of rows in the table and click Estimate Table Size

Enterprise Manager returns its estimate in MB.

Estimate The Size Of Indexes

The CREATE_INDEX_COST Procedure of the DBMS_SPACE package helps to estimate the size of creating an index on an existing table.

The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.

Usage Notes :

The table on which the index is created must already exist.
The computation of the index size depends on statistics gathered on the segment.
It is imperative that the table must have been analyzed recently.
In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.

The output of the procedure contains used_bytes and alloc_bytes :

The used_bytes : how much space is for the index data
The alloc_bytes : how much space is allocated within the tablespace for the index segment.

Estimate Index Size example :

create table test (a NUMBER (10) , b VARCHAR2 (30) ,c VARCHAR2 (30), d date ) tablespace USERS pctfree 10;

BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/

exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');

set serveroutput on

declare
   l_used_bytes number;
   l_alloc_bytes number;
begin
   dbms_space.create_index_cost (
      ddl => 'create index test_indx on test (a,b) tablespace users',
      used_bytes => l_used_bytes,
      alloc_bytes => l_alloc_bytes
   );
   dbms_output.put_line ('Used Bytes      = '||l_used_bytes);
   dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/

Used Bytes = 3800000
Allocated Bytes = 6291456

which is around 6 MB , now lets create the actual index.

create index test_indx on test (a,b) tablespace users

select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST_INDX';

BYTES SEGMENT_TYPE
---------- ------------------
6291456 INDEX

which is around 6 MB

Refer To :
==========
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#sthref8857
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#sthref8850

REFERENCES

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#sthref8850
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#sthref8857

About Me

...............................................................................................................................

● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 联系我请加QQ好友(646634621)，注明添加缘由

● 于 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。