(1)规则
数据类型 最大预估字节数
varchar2(n) or char(n) n
date 7
number(p,s) 最低限度((p+1)/2)+1;带正负号的数字添加一个额外的字节
number 19
(2)查询SQL,输入用户名,查询拥有的所有表的估计大小
select table_name,
sum(decode(substr(data_type,1,1),'N',trunc((nvl(data_precision,38)+1)/2,0)
+1,'D',7,data_length)) max_length
from dba_tab_columns
where owner = upper('&schema_owner')
group by table_name
having sum(decode(substr(data_type,1,1),'N',trunc((nvl(data_precision,38)+1)/2,0)
+1,'D',7,data_length)) > 0
order by 2 desc,1
举例:scott
SQL> select table_name,
2 sum(decode(substr(data_type,1,1),'N',trunc((nvl(data_precision,38)+1)/2,0)
+1,'D',7,data_length)) max_length
3 from dba_tab_columns
4 where owner = upper('&schema_owner')
5 group by table_name
6 having sum(decode(substr(data_type,1,1),'N',trunc((nvl(data_precision,38)+1)/2,0)
+1,'D',7,data_length)) > 0
7 order by 2 desc,1
8 /
TABLE_NAME MAX_LENGTH
------------------------------ ----------
SALGRADE 60
BONUS 59
YU_GROUP 50
YU_GROUP3 50
EMP 44
YU_GROUP2 30
DEPT 29
YU 23
YU2 23
YU3 23
YU4 23
YU_IDX 23
12 rows selected
(3) 对每张表估算初始平均行长
SQL> desc scott.emp
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
估算:以scott.emp 表为例
SQL> desc scott.emp
Name Type 估算均长
-------- ------------ --------
EMPNO NUMBER(4) 3
ENAME VARCHAR2(10) 8
JOB VARCHAR2(9) 7
MGR NUMBER(4) 3
HIREDATE DATE 7
SAL NUMBER(7,2) 4
COMM NUMBER(7,2) 3
DEPTNO NUMBER(2) 2
所以,均长average = 37
(4) 计算平均增长量:
公式:平均增长量=(最大长 - 均长)/2
所以,scott.emp
平均增长量 = (44-37)/2 = 3.5 (注意这个小数点需要保留)
(5) 计算pctfree:
pctfree = (平均增长量/最大长)*100
= (3.5/44)*100 = 7.95
所以,最后对于scott.emp表 pctfree值设定为10%.(四舍五入了一下)
by sdusun
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/593324/viewspace-376187/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/593324/viewspace-376187/