[bbk5161] 第107集 第13章 表空间管理 05

/*

　　实验目的:对比压缩表和非压缩表之间的区别

　　实验步骤：

1. create table -- emp3
   1. insert data
2. create table -- emp1 -- nocompress
   1. show space
3. create table -- emp1 -- compress -- basic
   1. show space
   2. insert record -- 
4. create table -- emp2 -- compress - oltp
   1. insert record --
   2. 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.