最近有朋友问到了DBMS_SPACE包的使用,也看了一下,大部分是关于dbms_space.space_usage的使用,space_usage这个过程的例子已经很多了,我也就不再多说了,除了这个过程外,另外还有两个过程也有着特殊的用处,但使用的人不多,我们也来看看这两个过程有什么用。
这两个过程为:CREATE_INDEX_COST和CREATE_TABLE_COST,分别用户评估创建索引和创建表的存储开销(空间占用情况)。
CREATE_INDEX_COST的语法如下:
DBMS_SPACE.CREATE_INDEX_COST (
ddl IN VARCHAR2,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER,
plan_table IN VARCHAR2 DEFAULT NULL);
下面是相关的测试代码:
1、准备相关表和数据
SQL> set serveroutput on
SQL> create table t(c char(100),d varchar2(200));
表已创建。
SQL> begin
2 for i in 1..5000 loop
3 insert into t values(i,i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
2、分析表,注意:没有统计信息,CREATE_INDEX_COST将无法计算索引的存储开销
SQL> analyze table t compute statistics;
表已分析。
SQL> declare
2 v1 number;
3 v2 number;
4 begin
5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2);
6 dbms_output.put_line(v1/1024||' '||v2/1024);
7 end;
8 /
488.28125 640 --计算出的索引将占用488K字节空间,为该索引需要分配640k存储空间
PL/SQL 过程已成功完成。
3、创建实际索引,确定索引存储空间是否与计算的结果相符
SQL> create index i on t(c);
索引已创建。
SQL> select count(*) from user_extents where segment_name='I';
COUNT(*)
----------
11
已选择1行。 --11个64k的区,比计算出的大1个区
4、再次装载数据
SQL> begin
2 for i in 1..5000 loop
3 insert into t values(i,i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> declare
2 v1 number;
3 v2 number;
4 begin
5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2);
6 dbms_output.put_line(v1/1024||' '||v2/1024);
7 end;
8 /
488.28125 640 --没有分析之前,获得得仍然是根据以前分析结果计算的值
PL/SQL 过程已成功完成。
SQL> analyze table t compute statistics;
表已分析。
SQL> declare
2 v1 number;
3 v2 number;
4 begin
5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(c)',v1,v2);
6 dbms_output.put_line(v1/1024||' '||v2/1024);
7 end;
8 /
976.5625 2048 --分析之后,得到新的结果
PL/SQL 过程已成功完成。
5、再次验证,16个64k的区和1个1024k的区,2048k,与估计值相同
SQL> select count(*) from user_extents where segment_name='I';
COUNT(*)
----------
17
-------------------------------------------------------------
6、换了一个字段进行测试
SQL> declare
2 v1 number;
3 v2 number;
4 begin
5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2);
6 dbms_output.put_line(v1/1024||' '||v2/1024);
7 end;
8 /
39.0625 192 --计算出的索引将占用39K字节空间,为该索引需要分配192k存储空间
PL/SQL 过程已成功完成。
7、创建索引,新建的索引比估算的值大1个区
SQL> create index i on t(d);
索引已创建。
SQL> select count(*) from user_extents where segment_name='I';
COUNT(*)
----------
4
SQL> drop index i;
8、再次装载数据并分析表
SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i,i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> analyze table t compute statistics;
表已分析。
9、重新计算,得到新的估算值
SQL> declare
2 v1 number;
3 v2 number;
4 begin
5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2);
6 dbms_output.put_line(v1/1024||' '||v2/1024);
7 end;
8 /
78.125 320
PL/SQL 过程已成功完成。
10、创建索引,新建的索引比估计的大2个区
SQL> create index i on t(d);
索引已创建。
SQL> select count(*) from user_extents where segment_name='I';
COUNT(*)
----------
7
11、顺便测试shink space的效果
SQL> select count(*) from t;
COUNT(*)
----------
20000
SQL> delete t where rownum<=15000;
已删除15000行。
SQL> commit;
提交完成。
SQL> alter table t enable row movement;
表已更改。
12、在删掉15000行数据后,没有整理空间之前进行统计信息收集
SQL> analyze table t compute statistics;
表已分析。
SQL> declare
2 v1 number;
3 v2 number;
4 begin
5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2);
6 dbms_output.put_line(v1/1024||' '||v2/1024);
7 end;
8 /
24.4140625 128 --基于新收集的统计信息计算,估算的索引需要分配128k存储空间
PL/SQL 过程已成功完成。
13、收缩表,释放占用的存储空间
SQL> alter table t shrink space;
表已更改。
SQL> analyze table t compute statistics;
表已分析。
SQL> declare
2 v1 number;
3 v2 number;
4 begin
5 DBMS_SPACE.CREATE_INDEX_COST('create index i on t(d)',v1,v2);
6 dbms_output.put_line(v1/1024||' '||v2/1024);
7 end;
8 /
24.4140625 128 --收缩后重新收集统计信息,与原统计信息一样,因此计算出的大小一样
PL/SQL 过程已成功完成。
SQL> select count(*) from user_extents where segment_name='I';
COUNT(*)
----------
7
--现有索引并没有收缩,仅仅是表空间进行了收缩,因此现有索引仍保持原大小
14、重建索引,对比新的索引大小与计算出的索引大小一样大
SQL> alter index i rebuild;
索引已更改。
SQL> select count(*) from user_extents where segment_name='I';
COUNT(*)
----------
2
--重建索引后新的索引占用空间与计算出的空间一样大
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20034375/viewspace-1021372/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20034375/viewspace-1021372/