Oracle的学习心得和知识总结（四）|Oracle数据库表压缩技术详解-阿里云开发者社区

目录结构

注：提前言明本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、Oracle表压缩官方文档，点击前往

2、Oracle11g新特性之：数据压缩技术，点击前往

3、参考书籍：《涂抹Oracle 三思笔记之一步一步学Oracle》

4、参考书籍：《Oracle Database 11g数据库管理艺术》

5、oracle 11g压缩性能，点击前往

6、Oracle的表压缩，点击前往

7、oracle压缩表/表空间----转，点击前往

8、参考书籍：《Oracle Database 9i10g11g编程艺术深入数据库体系结构(第2版)》

9、TOM大神的show_space（）初体验，点击前往

Oracle数据库表压缩技术

文章快速说明索引

Oracle表压缩概要

Oracle表压缩示例

Oracle表压缩性能

文章快速说明索引

学习目标：

前言：Oracle数据库素以强悍的功能和优异的性能闻名于世，作为世界上最优秀的数据库 (没有之一) 堪称里程碑式的传奇大作，其每一次改动每一次迭代每一次发行都将引领数据库行业一个一个潮流和地震！而PostgreSQL作为一款基于宽泛开源协议BSD的数据库管理系统，它在去年（2020）的DB-Engines排名中获得了比任何其他 360 个受监控数据库系统更多的受欢迎程度。

Oracle作为行业标杆和龙头大哥般的存在，PostgreSQL的前进之路路漫漫其修远兮，必将上下而求索！战胜对手的好方法就是向对手学习：要尊重对手学习对手分析对手，才能了解对手掌控对手战胜对手！而对于当今去O大势，PostgreSQL数据库凭借着不隶属于任何组织任何国家的开源特性强悍的自身性能和最广大开源社区的鼎力支持，成了(很多机构公司和部门)为数不多的一根救命稻草和狂风暴雨中的一叶孤舟！但是 PostgreSQL永远值得信赖 …

学习内容：（详见目录）

1、Oracle数据库的表压缩技术

学习时间：

2021年01月03日 04:40:48

学习产出：

1、Oracle数据库表压缩技术学习

2、CSDN 技术博客 1篇

3、PostgreSQL数据库开源插件cstore的感悟心得

注：下面我们所有的学习环境是Centos7+PostgreSQL13.0+Oracle11g+MySQL5.7

postgres=# select version();
                                   version                                   
-----------------------------------------------------------------------------
 PostgreSQL 13.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)
postgres=#
#-----------------------------------------------------------------------------#
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE  11.2.0.1.0  Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL>
#-----------------------------------------------------------------------------#
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.06 sec)
mysql>

Oracle表压缩概要

表压缩概述

官方文档：

1、随着数据库规模的增长，可以考虑使用表压缩。压缩节省了磁盘空间，减少了数据库缓冲区缓存中的内存使用，并可以显著提高读取期间的查询执行速度

2、压缩在数据加载和DML的CPU开销上有代价。然而，这一成本可能会被减少的I/O需求所抵消

3、表压缩对应用程序是完全透明的。它在决策支持系统(DSS)、在线事务处理(OLTP)系统和归档系统中很有用

4、您可以为表空间、表或分区指定压缩。如果在表空间级别指定，那么在该表空间中创建的所有表默认都是压缩的

作为一项从Oracle9iR2 开始的重要亮点，ORACLE提供了针对于表/表空间的压缩技术：以减少磁盘开销，节省空间，并在某些情况下提高查询性能。

表压缩是如何工作的？在Orcle9i第2版中，表压缩特性通过删除在数据库表中发现的重复数据值来节省空间。压缩是在数据库的数据块级别上进行的。当确定一个表要被压缩后，数据库便在每一个数据库数据块中保留空间，以便储存在该数据块中的多个位置上出现的数据的单一拷贝。这一被保留的空间被称作符号表（symbol table）。被标识为要进行压缩的数据只存储在该符号表中，而不是在数据库行本身内。当在一个数据库行中出现被标识为要压缩的数据时，该行在该符号表中存储一个指向相关数据的指针，而不是数据本身。也即：节约空间是通过删除表中数据值的冗余拷贝而实现的

在将数据插入、更新或批量加载到表中时，可能会发生压缩。这里允许压缩的操作包括：

单行或数组插入和更新

以下直接路径INSERT方法：

直接路径SQL * Loader

CREATE TABLE AS SELECT 语句

平行INSERT语句

带有APPEND或APPEND_VALUES提示的INSERT语句

Oracle数据库支持多种表压缩方法，其特点和特性如下表所示：

Table Compression Method Compression Level CPU Overhead Applications Notes1 CREATE/ALTER TABLE Syntax Direct-Path INSERT Notes2

Basic compression 高低 DSS 无 COMPRESS [BASIC] 使用基本压缩来压缩行 COMPRESS和COMPRESS BASIC等价

在不使用直接路径插入的情况下插入的行和更新的行均未压缩，并且支持有限的数据类型和SQL操作

OLTP compression 高低 OLTP，DSS 无 COMPRESS FOR OLTP 行使用OLTP压缩进行压缩不使用直接路径插入而插入的行和使用OLTP压缩来压缩更新的行

Warehouse compression (Hybrid Columnar Compression) 更高更高 DSS 压缩级别和CPU开销取决于指定的压缩级别（低或高） COMPRESS FOR QUERY [LOW|HIGH] 行通过仓库压缩进行压缩此压缩方法可能导致较高的CPU开销

更新的行和未使用直接路径插入插入的行以行格式而不是列格式存储，因此压缩级别较低

Archive compression (Hybrid Columnar Compression) 最高最高 Archiving 同上 COMPRESS FOR ARCHIVE [LOW|HIGH] 使用归档压缩来压缩行同上

对于上述四种表压缩方法，需要注意的点：

1、当使用基本压缩，仓库压缩或归档压缩时，仅在将数据批量加载到表中时才发生压缩

2、使用OLTP压缩时，压缩是在将数据插入，更新或批量加载到表中时发生的。允许压缩的操作包括：

①、单行或数组插入和更新：插入和更新不会立即压缩。更新已压缩的块时，任何未更新的列通常保持压缩状态。更新的列以类似于任何未压缩块的未压缩格式存储。当块达到数据库控制的阈值时，将重新压缩更新的值。当块中的数据达到数据库控制的阈值时，插入的数据也会被压缩

②、以下直接路径INSERT方法：

直接路径SQL * Loader

CREATE TABLE AS SELECT语句

平行INSERT语句

带有APPEND或APPEND_VALUES提示的INSERT语句

3、基本压缩仅压缩直接路径加载插入的数据，并支持有限的数据类型和SQL操作

4、OLTP压缩旨在用于OLTP应用程序，并压缩任何SQL操作操纵的数据

5、仓库压缩和存档压缩达到最高压缩级别，因为它们使用了混合列压缩技术。混合列压缩技术使用改良形式的列存储而不是行主存储。这使数据库可以将相似的数据存储在一起，从而提高了压缩算法的效率。对于被更新的数据，混合列压缩使用更多的CPU，并将更新的行移动为行格式，以便以后的更新会更快。由于进行了这种优化，因此仅应将其用于不经常更新的数据

6、只有使用直接路径插入的数据才能实现混合列压缩的更高压缩级别。支持常规的插入和更新，但是会导致行从列格式移动到行格式，并降低了压缩级别

7、不管压缩方法如何，DELETE对压缩块的DELETE操作都与对非压缩块的操作相同。由SQL DELETE操作引起的在数据块上获得的任何空间都将被后续的SQL INSERT操作重用。使用混合列压缩技术，当删除压缩单元中的所有行时，压缩单元中的空间可供重用

下面是语法相关的(涉及表的压缩与否和使用)注意事项(参照上面表格)：

使用CREATE TABLE 语句的COMPRESS子句指定表压缩

通过在ALTER TABLE语句中使用这些子句，可以对现有表启用压缩。在这种情况下，只有在启用压缩后插入或更新的数据才会被压缩

类似地，您可以使用ALTER TABLE…NOCOMPRESS声明，所有已经压缩的数据仍然保持压缩状态，而插入的新数据未压缩

COMPRESS FOR QUERY HIGH选项是默认的数据仓库压缩模式。当在Exadata storage上使用混合列压缩时，它提供了良好的压缩和性能

COMPRESS FOR QUERY LOW选项应该在负载性能至关重要的环境中使用。它加载速度比使用COMPRESS FOR QUERY HIGH选项压缩的数据要快

COMPRESS FOR ARCHIVE LOW选项是默认的存档压缩模式。它提供了高压缩级别，是不常访问的数据的理想选择。对于很少访问的数据，应该使用COMPRESS FOR ARCHIVE HIGH选项

DBMS_COMPRESSION包提供的一个压缩顾问，可以帮助您确定使用特定压缩方法的特定表的预期压缩级别

Oracle表压缩示例

表压缩示例

实例一：基本压缩的体验和使用

只有当数据是直接路径插入或更新记录（direct-path insert and updated ）时才会发生压缩

并且支持有限的数据类型和SQL操作

SQL> desc t2 
 Name   Null?    Type
 ------------------------------------------------------
 ID         NUMBER(38)
 NAME       VARCHAR2(20)
SQL> select count(*) from t2;
  COUNT(*)
----------
       50
SQL>
# 创建压缩表
SQL> CREATE TABLE emp_comp compress as select * from t2 where 0 = 1;
Table created.
# 通过数据字典查看压缩表状态
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='EMP_COMP';
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
EMP_COMP  ENABLED  BASIC
# 通过非直接路径插入数据
SQL> INSERT INTO emp_comp SELECT * FROM t2;
50 rows created.
SQL> commit;
Commit complete.
SQL> set serveroutput on;
SQL> exec show_space('T2');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         0
FS2 Blocks (25-50) .....................         0
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         5
Full Blocks ............................         0
Total Blocks............................         8
Total Bytes.............................    65,536
Total MBytes............................         0
Unused Blocks...........................         0
Unused Bytes............................         0
Last Used Ext FileId....................         4
Last Used Ext BlockId...................       528
Last Used Block.........................         8
PL/SQL procedure successfully completed.
SQL> exec show_space('EMP_COMP');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         0
FS2 Blocks (25-50) .....................         0
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         5
Full Blocks ............................         0
Total Blocks............................         8
Total Bytes.............................    65,536
Total MBytes............................         0
Unused Blocks...........................         0
Unused Bytes............................         0
Last Used Ext FileId....................         4
Last Used Ext BlockId...................       560
Last Used Block.........................         8
PL/SQL procedure successfully completed.
SQL>
# 对比与原t2表的占用情况，emp_comp表并未压缩

# 通过直接路径插入数据
SQL> drop table emp_comp purge;
Table dropped.
SQL> CREATE TABLE emp_comp compress as select * from t2 where 0 = 1;
Table created.
SQL> insert /*+ append */ into emp_comp select * from t2;
50 rows created.
SQL> exec show_space('EMP_COMP');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         0
FS2 Blocks (25-50) .....................         0
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         0
Full Blocks ............................         0
Total Blocks............................         8
Total Bytes.............................    65,536
Total MBytes............................         0
Unused Blocks...........................         5
Unused Bytes............................    40,960
Last Used Ext FileId....................         4
Last Used Ext BlockId...................       560
Last Used Block.........................         3
PL/SQL procedure successfully completed.
SQL>
# 很明显少占用5个数据块

# 用于查看Oracle数据库对象空间使用情况
create or replace procedure show_space(p_segname in varchar2,
                                         p_owner in varchar2 default user,
                                         p_type in varchar2 default 'TABLE',
                                         p_partition in varchar2 default NULL)
  -- this procedure uses authid current user so it can query DBA_*
  -- views using privileges from a ROLE and so it can be installed
  -- once per database, instead of once per user that wanted to use it
  authid current_user as
  l_free_blks number;
  l_total_blocks number;
  l_total_bytes number;
  l_unused_blocks number;
  l_unused_bytes number;
  l_LastUsedExtFileId number;
  l_LastUsedExtBlockId number;
  l_LAST_USED_BLOCK number;
  l_segment_space_mgmt varchar2(255);
  l_unformatted_blocks number;
  l_unformatted_bytes number;
  l_fs1_blocks number;
  l_fs1_bytes number;
  l_fs2_blocks number;
  l_fs2_bytes number;
  l_fs3_blocks number;
  l_fs3_bytes number;
  l_fs4_blocks number;
  l_fs4_bytes number;
  l_full_blocks number;
  l_full_bytes number;
  -- inline procedure to print out numbers nicely formatted
  -- with a simple label
  procedure p(p_label in varchar2, p_num in number) is
  begin
    dbms_output.put_line(rpad(p_label, 40, '.') ||
                         to_char(p_num, '999,999,999,999'));
  end;
begin
  -- this query is executed dynamically in order to allow this procedure
  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
  -- via a role as is customary.
  -- NOTE: at runtime, the invoker MUST have access to these two
  -- views!
  -- this query determines if the object is a ASSM object or not
  begin
    execute immediate 'select ts.segment_space_management
            from dba_segments seg, dba_tablespaces ts
           where seg.segment_name = :p_segname
             and (:p_partition is null or
                seg.partition_name = :p_partition)
             and seg.owner = :p_owner
             and seg.segment_type = :p_type
             and seg.tablespace_name = ts.tablespace_name'
      into l_segment_space_mgmt
      using p_segname, p_partition, p_partition, p_owner, p_type;
  exception
    when too_many_rows then
      dbms_output.put_line('This must be a partitioned table, use p_partition => ');
      return;
  end;
  -- if the object is in an ASSM tablespace, we must use this API
  -- call to get space information, else we use the FREE_BLOCKS
  -- API for the user managed segments
  if l_segment_space_mgmt = 'AUTO' then
    dbms_space.space_usage(p_owner,
                           p_segname,
                           p_type,
                           l_unformatted_blocks,
                           l_unformatted_bytes,
                           l_fs1_blocks,
                           l_fs1_bytes,
                           l_fs2_blocks,
                           l_fs2_bytes,
                           l_fs3_blocks,
                           l_fs3_bytes,
                           l_fs4_blocks,
                           l_fs4_bytes,
                           l_full_blocks,
                           l_full_bytes,
                           p_partition);
    p('Unformatted Blocks ', l_unformatted_blocks);
    p('FS1 Blocks (0-25) ', l_fs1_blocks);
    p('FS2 Blocks (25-50) ', l_fs2_blocks);
    p('FS3 Blocks (50-75) ', l_fs3_blocks);
    p('FS4 Blocks (75-100)', l_fs4_blocks);
    p('Full Blocks ', l_full_blocks);
  else
    dbms_space.free_blocks(segment_owner => p_owner,
                           segment_name => p_segname,
                           segment_type => p_type,
                           freelist_group_id => 0,
                           free_blks => l_free_blks);
    p('Free Blocks', l_free_blks);
  end if;
  -- and then the unused space API call to get the rest of the
  -- information
  dbms_space.unused_space(segment_owner => p_owner,
                          segment_name => p_segname,
                          segment_type => p_type,
                          partition_name => p_partition,
                          total_blocks => l_total_blocks,
                          total_bytes => l_total_bytes,
                          unused_blocks => l_unused_blocks,
                          unused_bytes => l_unused_bytes,
                          LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
                          LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
                          LAST_USED_BLOCK => l_LAST_USED_BLOCK);
  p('Total Blocks', l_total_blocks);
  p('Total Bytes', l_total_bytes);
  p('Total MBytes', trunc(l_total_bytes / 1024 / 1024));
  p('Unused Blocks', l_unused_blocks);
  p('Unused Bytes', l_unused_bytes);
  p('Last Used Ext FileId', l_LastUsedExtFileId);
  p('Last Used Ext BlockId', l_LastUsedExtBlockId);
  p('Last Used Block', l_LAST_USED_BLOCK);
end;

实例二 ：表压缩的禁用和启用

SQL> create table emp_comp2 (id int,name character varying(20));
Table created.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='EMP_COMP2';
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
EMP_COMP2 DISABLED
SQL> alter table emp_comp2 COMPRESS;
Table altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='EMP_COMP2';
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
EMP_COMP2 ENABLED  BASIC
SQL> alter table emp_comp2 NOCOMPRESS;
Table altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='EMP_COMP2';
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
EMP_COMP2 DISABLED
SQL>

实例三 ：无法删除压缩表上的列

不支持对压缩表执行添加/删除列操作

必须要字段变更的话：ALTER TABLE TABLE_NAME SET UNUSED COLUMN COLUMN_NAME;

SQL> alter table emp_comp2 COMPRESS;
Table altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='EMP_COMP2';
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
EMP_COMP2 ENABLED  BASIC
SQL>  alter table emp_comp2 drop column name;
 alter table emp_comp2 drop column name
                                   *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
# 同样也不允许在压缩表上增加一个带默认值的列
SQL> alter table emp_comp2  add remark varchar2(200) default 'null';
alter table emp_comp2  add remark varchar2(200) default 'null'
                           *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SQL> alter table emp_comp2 SET UNUSED COLUMN name;
Table altered.
SQL> alter table emp_comp2 drop column name;
alter table emp_comp2 drop column name
                                  *
ERROR at line 1:
ORA-00904: "NAME": invalid identifier
SQL> desc emp_comp2;
 Name            Null?    Type
----------------------------------------------------------
 ID                     NUMBER(38)
SQL>

以上三个实例均为表级别的操作，下面是表空间级别的实例：

实例四：表空间级别的实例

与其他存储参数类似，COMPRESS属性也具有一些继承特性。当在一个表空间中创建一个表时，它从该表空间继承COMPRESS属性

确定是否已经利用compress对一个表空间进行了定义，可查询user_tablespaces数据字典视图并查看def_tab_compression列

# 创建表压缩空间
SQL> create tablespace TEST datafile '/home/oracle/mydata/test.dbf' size 10M default compress;
Tablespace created.
SQL> select tablespace_name,def_tab_compression from user_tablespaces;
TABLESPACE_NAME     DEF_TAB_
------------------------- --------
SYSTEM        DISABLED
SYSAUX        DISABLED
UNDOTBS1      DISABLED
TEMP        DISABLED
USERS       DISABLED
EXAMPLE       DISABLED
ANOTHER_TEST1     DISABLED
TEST        ENABLED
8 rows selected.
SQL> select tablespace_name,def_tab_compression from user_tablespaces where tablespace_name = 'TEST';
TABLESPACE_NAME     DEF_TAB_
------------------------- --------
TEST        ENABLED
SQL>
# 用户 song 使用 test 这个表空间
SQL> alter user song  default tablespace test;
User altered.
SQL> create table t2(id int,name character varying(20));
Table created.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T2' ;         
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T2    ENABLED  BASIC
SQL>

实例五 ：现有表空间转换为压缩表空间或停用

在其中创建的压缩表，在表空间被停掉压缩后依旧是压缩状态
若是被停，则继承压缩性质将没有

SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T2' ;         
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T2    ENABLED  BASIC
SQL> alter tablespace test default nocompress;
Tablespace altered.
SQL> select tablespace_name,def_tab_compression from user_tablespaces where tablespace_name = 'TEST';
TABLESPACE_NAME     DEF_TAB_
------------------------- --------
TEST        DISABLED
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T2' ;
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T2    ENABLED  BASIC
SQL> create table t1(id int,name character varying(20));
Table created.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T1' ;
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T1    DISABLED
SQL> alter tablespace test default compress;
Tablespace altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T1' ;
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T1    DISABLED
SQL>

注：这里需要再次强调一点，即使像上面那样指定compress时，其它表中(表空间)的数据并没有压缩，它只是修改了数据字典的设置。只有在向一个表中加载插入数据时，才会压缩数据。只有在使用下面4种方法时，表中的数据才会被压缩存放：

直接路径的 sql * load

带有 /*+ append*/ 的 insert语句

create table ... as select...

并行insert

实例六：已经存在的表压缩或解压

可以参考实例二

SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T1' ;
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T1    DISABLED
SQL> alter table t1  move compress;
Table altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T1' ;
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T1    ENABLED  BASIC
SQL> alter table t1 NOCOMPRESS;
Table altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T1' ;
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T1    DISABLED
SQL> alter table t1 COMPRESS;
Table altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T1' ;
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T1    ENABLED  BASIC
SQL> alter table t1  move nocompress;
Table altered.
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='T1' ;
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
T1    DISABLED
SQL>

实例七：压缩一个物化视图

使用用于压缩表的类似方式来压缩物化视图

基于多个表的联接生成的物化视图通常很适于压缩，因为它们通常拥有大量的重复数据项

可以使用ALTER MATERIALIZED VIEW命令来改变一个物化视图的压缩属性，当你使用此命令时，请注意通常是在下一次刷新该物化视图时才会进行实际的压缩

SQL> create materialized view mv_tmp_test compress as select a.name , b.sex from t1 a, t2 b where a.id=b.id;
Materialized view created.
SQL> select * from mv_tmp_test;
NAME     SEX
---------- ----------------
post     f
redi     m
SQL> select * from t1;
  ID NAME
---------- ----------
   1 post
   2 orac
   3 redi
   4 mysq
SQL> select * from t2;
  ID SEX
---------- ----------------
   1 f
   3 m
SQL>
SQL> alter materialized view mv_tmp_test nocompress;
Materialized view altered.
SQL> 
SQL>  alter materialized view mv_tmp_test compress;
Materialized view altered.
SQL>

Oracle表压缩性能

测试背景说明：压缩性能测试，test表为导入数据表

cm表为普通表未设置compress，普通insert插入

cm_cmp表为设置compress，普通insert插入

cm_app表为设置compress，insert append插入

cm_for_all表设置compress for all operations，普通insert插入

SQL>  create table test (a char(20),b char(20));
Table created.
SQL>

declare
begin
  for i in 1..1000 loop
    insert into test values('a','b');
  end loop;
  for i in 1..1000 loop
    insert into test values('a'||i,'b'||i);
  end loop;
  commit;
end;/
SQL> select count(*) from test;
  COUNT(*)
----------
      2000
SQL>
SQL> SELECT table_name, compression, compress_for FROM user_tables WHERE table_name='TEST';
TABLE_NAME  COMPRESS COMPRESS_FOR
--------------- -------- ------------
TEST    DISABLED
SQL>
# test表就是一个普通表
SQL>  execute dbms_stats.gather_table_stats('SONG','TEST');
PL/SQL procedure successfully completed.
SQL> select COLUMN_NAME ,num_distinct,num_nulls from user_tab_col_statistics where table_name='TEST';
COLUMN_NAME      NUM_DISTINCT  NUM_NULLS
-------------------- ------------ ----------
A          1001    0
B          1001    0
SQL>
SQL> exec show_space('TEST');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         1
FS2 Blocks (25-50) .....................         0
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         0
Full Blocks ............................        12
Total Blocks............................        16
Total Bytes.............................   131,072
Total MBytes............................         0
Unused Blocks...........................         0
Unused Bytes............................         0
Last Used Ext FileId....................         6
Last Used Ext BlockId...................       184
Last Used Block.........................         8
PL/SQL procedure successfully completed.
SQL>

在上面表插入数据的时候，我们也看到了：Test表有a,b字段共2000行，其中重复字段1000行。按压缩表性质数据字段原位留指针，指向块头的符号表，此表理想压率应接近40% 使用测试表方便直观估计最佳压缩比率了解压缩性质，压缩算法未变。

下面是测试表的创建：

SQL>  create table cm as select * from test where 0=1;
Table created.
SQL> create table cm_cmp compress as select * from test where 0=1;
Table created.
SQL> create table cm_app compress as select * from test where 0=1;
Table created.
SQL> create table cm_for_all compress for all operations as select * from test where 0=1;
Table created.
SQL> select table_name,num_rows,compression,compress_for from user_tables where table_name like '%CM%';
TABLE_NAME    NUM_ROWS COMPRESS COMPRESS_FOR
--------------- ---------- -------- ------------
CM         DISABLED
CM_CMP         ENABLED  BASIC
CM_APP         ENABLED  BASIC
CM_FOR_ALL       ENABLED  OLTP
SQL>

第一种 ：cm表为普通表未设置compress，普通insert插入

SQL>  insert into cm  select * from test;
2000 rows created.
SQL> commit;
Commit complete.
SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM';
SEGMENT_NAME                    FILE_ID   BLOCK_ID     BLOCKS
---------------------------------------------------------- ---------- ---------- ----------
CM                         6      192    8
CM                         6      200    8
# 如下：2个区 实际使用13个块
SQL>  select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM);
COUNT(DISTINCTBLOCK#)
---------------------
       13
SQL>  alter table cm move compress;
Table altered.
# 2个区 压缩之后实际使用8个block，可以看到最佳可以压缩到8个block
SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM);
COUNT(DISTINCTBLOCK#)
---------------------
        8
SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name= 'CM' ;
SEGMENT_NAME                 FILE_ID   BLOCK_ID     BLOCKS
------------------------------------------------------------ ---------- ---------- ----------
CM                         6      208    8
CM                         6      216    8
SQL>
SQL> set serveroutput on;
SQL> exec show_space('CM');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         0
FS2 Blocks (25-50) .....................         0
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         0
Full Blocks ............................         8
Total Blocks............................        16
Total Bytes.............................   131,072
Total MBytes............................         0
Unused Blocks...........................         5
Unused Bytes............................    40,960
Last Used Ext FileId....................         6
Last Used Ext BlockId...................       216
Last Used Block.........................         3
PL/SQL procedure successfully completed.
SQL>

结果：普通表普通insert，为13个block，move compress得到最佳压缩为8个block，压缩比为38%

第二种 ：cm_cmp表为设置compress，普通insert插入

SQL> insert into cm_cmp  select * from test;
2000 rows created.
SQL> exec show_space('CM_CMP');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         1
FS2 Blocks (25-50) .....................         1
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         1
Full Blocks ............................        10
Total Blocks............................        16
Total Bytes.............................   131,072
Total MBytes............................         0
Unused Blocks...........................         0
Unused Bytes............................         0
Last Used Ext FileId....................         6
Last Used Ext BlockId...................       200
Last Used Block.........................         8
PL/SQL procedure successfully completed.
SQL>  select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_CMP';
SEGMENT_NAME                       FILE_ID   BLOCK_ID     BLOCKS
--------------------------------------------------------------------------------- ---------- ---------- ----------
CM_CMP                         6      192    8
CM_CMP                         6      200    8
SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_CMP);
COUNT(DISTINCTBLOCK#)
---------------------
       12
SQL>
# 2个区 实际占用12个block，针对表设置compress后普通insert只压缩1个block

结果：表设置compress后普通insert为12个block，压缩比为7%

第三种 ：cm_app表为设置compress，insert append插入

SQL> insert /*+append*/into cm_app select * from test;
2000 rows created.
SQL> col segment_name for a30
SQL>  select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_APP';
SEGMENT_NAME        FILE_ID   BLOCK_ID   BLOCKS
------------------------------ ---------- ---------- ----------
CM_APP          6  224        8
CM_APP          6  232        8
SQL> commit;
Commit complete.
SQL> exec show_space('CM_APP');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         0
FS2 Blocks (25-50) .....................         0
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         0
Full Blocks ............................         8
Total Blocks............................        16
Total Bytes.............................   131,072
Total MBytes............................         0
Unused Blocks...........................         5
Unused Bytes............................    40,960
Last Used Ext FileId....................         6
Last Used Ext BlockId...................       232
Last Used Block.........................         3
PL/SQL procedure successfully completed.
SQL>
SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_APP);
COUNT(DISTINCTBLOCK#)
---------------------
        8
SQL>
# 2个区 实际使用8个block

结果：表设置compress后append ,为8个block，压缩比为38%

第四种 ：cm_for_all表设置compress for all operations，普通insert插入

SQL> insert into cm_for_all select * from test;
2000 rows created.
SQL>  commit;
Commit complete.
SQL>  select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM_FOR_ALL';
SEGMENT_NAME        FILE_ID   BLOCK_ID   BLOCKS
------------------------------ ---------- ---------- ----------
CM_FOR_ALL        6  240        8
CM_FOR_ALL        6  248        8
SQL> exec show_space('CM_FOR_ALL');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         0
FS2 Blocks (25-50) .....................         1
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         6
Full Blocks ............................         6
Total Blocks............................        16
Total Bytes.............................   131,072
Total MBytes............................         0
Unused Blocks...........................         0
Unused Bytes............................         0
Last Used Ext FileId....................         6
Last Used Ext BlockId...................       248
Last Used Block.........................         8
PL/SQL procedure successfully completed.
# 针对设置成compress for all的表普通insert压缩效果也是很理想，2个区 实际占用9个block
SQL>  select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM_FOR_ALL);
COUNT(DISTINCTBLOCK#)
---------------------
        9
SQL>

结果：表设置compress for all operations后普通insert，为9个block，压缩比为30%

这里需要注意的一点：compress是否对执行UPDATE将造成row migrate表变大？

SQL> exec show_space('CM');
Unformatted Blocks .....................         0
FS1 Blocks (0-25) ......................         0
FS2 Blocks (25-50) .....................         0
FS3 Blocks (50-75) .....................         0
FS4 Blocks (75-100).....................         0
Full Blocks ............................         8
Total Blocks............................        16
Total Bytes.............................   131,072
Total MBytes............................         0
Unused Blocks...........................         5
Unused Bytes............................    40,960
Last Used Ext FileId....................         6
Last Used Ext BlockId...................       216
Last Used Block.........................         3
PL/SQL procedure successfully completed.
SQL> select count(distinct block#) from (select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# ,rowid from CM);
COUNT(DISTINCTBLOCK#)
---------------------
        8
SQL> analyze table cm compute statistics;
Table analyzed.
# 现在表 没有行迁移
SQL> select num_rows,chain_cnt from dba_tables where table_name='CM' ;
  NUM_ROWS  CHAIN_CNT
---------- ----------
      2000      0
SQL>  update cm set A='AA';
2000 rows updated.
SQL>  COMMIT;
Commit complete.
SQL> analyze table cm compute statistics;
Table analyzed.
# 表出现大量行迁移
SQL> select num_rows,chain_cnt from dba_tables where table_name='CM';
  NUM_ROWS  CHAIN_CNT
---------- ----------
      2000    992
SQL> select segment_name,file_id,block_id,blocks from dba_extents where segment_name='CM';
SEGMENT_NAME        FILE_ID   BLOCK_ID   BLOCKS
------------------------------ ---------- ---------- ----------
CM          6  208        8
CM          6  216        8
CM          6  256        8
SQL>

注：如上可以看到多了一个区。在11g上针表设置compress后默认是ENABLED DIRECT LOAD ONLY，既只有通过直接路径插入append ,sqlldr才可以压缩，压缩比率与正常move compress最佳比率一致，apped ,sqlldr无明显区别（oracle按插入类型分类，直接插入类型压缩算法一致）；当表设置compress for all operations后，普通插入既可压缩，但压缩比率没有直接插入压缩比率好，但结果接近，这个新特性比较理想，但11g仍然在解压缩时候造成row migrate。

Oracle的学习心得和知识总结（四）|Oracle数据库表压缩技术详解

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像