/*
实验目的:对比压缩表和非压缩表之间的区别
实验步骤：
- create table -- emp3
- insert data
- create table -- emp1 -- nocompress
- show space
- create table -- emp1 -- compress -- basic
- show space
- insert record --
- create table -- emp2 -- compress - oltp
- insert record --
- show space
*/
--建表 SQL> create table emp3 select * from emp; --录入数据 insert into emp3 select * from emp; SQL> select count(*) from emp3; COUNT(*) ---------- 229376 SQL> commit; Commit complete.
创建一个非压缩表，并查看其压缩情况信息
--创建表 SQL> create table emp1 2 as 3 select * from emp 4 where 1=2; Table created. --查看数据字典，查看压缩信息 SQL> select table_name,compression,compress_for from user_tables; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ EMP3 DISABLED EMP1 DISABLED T DISABLED LIST1 COMPOSITE1 RANGE2 RANGE1 EMP 8 rows selected.
--录入数据 SQL> insert into emp1 select * from emp3; 229376 rows created. SQL> commit; Commit complete. --查看表占用空间大小情况 SQL> select table_name,blocks from user_tables; TABLE_NAME BLOCKS ------------------------------ ---------- EMP3 EMP1 T 5 LIST1 0 COMPOSITE1 0 RANGE2 1256 RANGE1 300 EMP 120 8 rows selected. --对于新创建的表，要进行统计分析之后方可看到统计数据信息 SQL> analyze table emp1 compute statistics; Table analyzed. SQL> analyze table emp3 compute statistics; Table analyzed. --再次查看表占用空间大小信息 SQL> select table_name,blocks from user_tables; TABLE_NAME BLOCKS ------------------------------ ---------- EMP3 499 EMP1 496 T 5 LIST1 0 COMPOSITE1 0 RANGE2 1256 RANGE1 300 EMP 120 8 rows selected.
--删除emp1(非压缩的)表 SQL> drop table emp1 purge; Table dropped. --创建emp1表(压缩表) SQL> create table emp1 2 compress 3 as 4 select * from emp where 1 =2; Table created. --通过数据字典，查看表的压缩属性信息 SQL> select table_name,compression,compress_for from user_tables; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ EMP3 DISABLED EMP1 ENABLED BASIC T DISABLED LIST1 COMPOSITE1 RANGE2 RANGE1 EMP
--分析压缩表 SQL> analyze table emp1 compute statistics; Table analyzed. --查看压缩表占用资源大小情况 SQL> select table_name,blocks from user_tables; TABLE_NAME BLOCKS ------------------------------ ---------- EMP3 499 EMP1 496 T 5 LIST1 0 COMPOSITE1 0 RANGE2 1256 RANGE1 300 EMP 120 8 rows selected.
上述压缩表，采用常规插入数据方式，效果基本上没有;
--创建表 SQL> create table emp1 2 compress 3 as 4 select * from emp3; Table created. SQL> commit; Commit complete. SQL> select count(*) from emp1; COUNT(*) ---------- 229376 --收集统计数据信息之前 SQL> select table_name,blocks from user_tables; TABLE_NAME BLOCKS ------------------------------ ---------- EMP3 499 EMP1 T 5 LIST1 0 COMPOSITE1 0 RANGE2 1256 RANGE1 300 EMP 120 8 rows selected. --收集统计信息数据 SQL> analyze table emp1 compute statistics; Table analyzed. --查看统计数据信息 SQL> select table_name,blocks from user_tables; TABLE_NAME BLOCKS ------------------------------ ---------- EMP3 499 EMP1 338 T 5 LIST1 0 COMPOSITE1 0 RANGE2 1256 RANGE1 300 EMP 120 8 rows selected.
上述采用的插入数据方式，压缩后的效果比较明显;
--compress for oltp SQL> create table emp2 2 compress for oltp 3 as 4 select * from emp where 1 = 2; Table created. --常规插入数据方式 SQL> insert into emp2 select * from emp3; 229376 rows created. --统计分析数据 SQL> analyze table emp2 compute statistics; Table analyzed. --查看压缩表占用资源情况 SQL> select table_name,blocks from user_tables; TABLE_NAME BLOCKS ------------------------------ ---------- EMP3 499 EMP1 338 EMP2 370 T 5 LIST1 0 COMPOSITE1 0 RANGE2 1256 RANGE1 300 EMP 120 9 rows selected. SQL> commit; Commit complete. SQL> select table_name,blocks from user_tables; TABLE_NAME BLOCKS ------------------------------ ---------- EMP3 499 EMP1 338 EMP2 370 T 5 LIST1 0 COMPOSITE1 0 RANGE2 1256 RANGE1 300 EMP 120 9 rows selected.
对于普通的非压缩表，其块中的PCTFREE参数默认为10%
当采用压缩表的时候，其块中的PCTFREE参数值为0;
对于compress for oltp,其快种的PCTFREE 参数为默认值10%，它要预留一些空间供UPDATE的操作.
--创建compress for oltp SQL> create table emp2 2 compress for oltp 3 as 4 select * from emp3; Table created. --分析统计数据 SQL> analyze table emp2 compute statistics; Table analyzed. --查看压缩表占用空间大小 SQL> select table_name,blocks from user_tables; TABLE_NAME BLOCKS ------------------------------ ---------- EMP3 499 EMP1 338 EMP2 375 T 5 LIST1 0 COMPOSITE1 0 RANGE2 1256 RANGE1 300 EMP 120 9 rows selected.