预估表和索引空间容量的方法

最近有一个业务功能要上线，生产数据库环境之前已经到位，目前要做的是估算下，业务数据量对数据库空间，有何影响。开发同学根据表字段定义，分别统计出了最大占用空间，以及预计占用空间量，计算得很细致。

今儿碰巧看见了一篇MOS文章，《How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database? (文档 ID 1585326.1)》，其中介绍了一些存储过程，可以用来估算表和索引空间，用起来比较方便，所以做一些实验说明下，会有用武之地，当然之前就说过，工具是用来方便执行一些功能，但前提是需要从原理上理解，融会贯通，将他变为自己的知识，这才是王道。

首先对于表的估算，用到的是DBMS_SPACE包中的CREATE_TABLE_COST这个存储过程，这个存储过程有两个版本，第一个版本是根据平均行长，估算表的容量，第二个版本是根据列的信息，估算表的容量，究竟有何区别？

这是对这两个版本的介绍，

这是这两个版本的定义，

总结下可以看出，这两个版本的相同点，就是需要预计行数、pct_free参数以及表空间名称，这几个参数，输出均有使用量(used_bytes)，以及分配量(alloc_bytes)这两个维度。不同点就是，版本一需要平均行长(avg_row_size)，版本二需要CREATE_TABLE_COST_COLUMNS类型的变量(colinfos)。

这是对于这两个存储过程，输出信息说明，

• 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.

创建测试表，手工收集表的统计信息，

可以看出，测试表平均行长是68，

用版本一，输入参数为表空间名称，平均行长，预计数据量(10万行)，pct_free，输出参数为ub和ab，显示使用的字节数为8036352，分配的字节数为8388608，

上面则是根据平均行长，预估的表容量，下面实际测试10万条数据，检索user_segments视图，其占用空间为9437184字节，

可知，根据版本一存储过程，计算出表预计空间量为8M，实际表占用空间量为9M，

使用版本二的存储过程，其中CREATE_TABLE_COST_COLUMNS类型要求写出所有字段，包括类型和长度，计算出使用的空间容量为9314304，分配的字节数为9437184，这和实际分配的表空间一致，

版本二比版本一计算准确，原因是因为版本二用了字段定义，包括类型和长度，外加存储属性，版本一则是根据平均行长，来预计空间，会相对不准。但需要注意，这是计算的字段极限值，比如VARCHAR2(30)并未用满，则可能实际占用空间，并不是此值，只能说明版本二计算的容量，任何情况下不会超过，但有可能用不了这些。

版本二这块，MOS中对于两个字符串类型，用的是CHAR，并未直接用VARCHAR2，有如下解释，

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

这点有些迷惑，而且使用CHAR和VARCHAR2计算出来的一样，不知道如何解释，有理解的朋友，还请指教！

这是CREATE_TABLE_COST_COLINFO的定义，

这是存储过程其他字段描述，

其次，对于索引空间的预算，使用的是CREATE_INDEX_COST存储过程，

他的使用有几点注意，

• 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.

其中参数DDL为需要创建的索引，此处为a和b字段建索引，显示使用的字节数为3800000，分配的字节数为6291456，

创建实际索引，检索user_segments视图，其占用索引空间容量为6291456，说明上面的存储过程，预计的结果是准确的，

总结：

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

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

如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)