Oracle基础：oracle compression table internals

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

本文转自朋友的真实案例分享。

Oracle基础：oracle compression table internals

压缩表介绍

Oracle可在表空间、表、分区级进行压缩，如果表空间设置了压缩方式，则所有存储在该表空间的表都将默认采用相同的压缩方式，在表级也可设置的压缩方式。Oracle还支持在分区级定义不同的压缩方式，以满足不同的数据管理需求。其压缩方式分为Basic Compression、OLTP compression、Warehouse compression(HCC)和Online archival compression (HCC)。

压缩的实现

当数据写入数据块时，对数据进行压缩操作
当从数据块中读取数据时，对数据块的数据进行解压操作
压缩的正面影响
压缩数据能让数据块存储更多的数据，从而达到节约存储空间的目的
能有效的提升buffer cache命中率，从而达到减少物理和逻辑I/O请求次数的目的
压缩的负面影响
压缩和解压带来的额外CPU开销

压缩方式

Basic Compression COMPRESS[BASIC]
OLTP compression COMPRESS for OLTP
Warehouse compression(HCC) COMPRESS for QUERY[LOW|HIGH]
Online archival compression (HCC) COMPRESS for ARCHIVE[LOW|HIGH]
Basic Compress

Basic Compress是9ir2版本推出的压缩技术，是基于数据块级别的，对数据块内重复的行或者重复的字段值进行压缩，但是并不是所有字段类型都是可以压缩的。

Basic Compress可压缩的类型

VARCHAR2
NUMBER
LONG
DATE
TIMESTAMP
RAW
LENGTH BYTE(存储在数据块行里的字段长度)
Basic Compress不可压缩的类型(LOB压缩可以使用SecureFiles)
CHAR
CLOB
BLOB

Basic Compress的特点：

必须使用Direct Path Load批量加载的数据才能被压缩(所以只适用于DSS，而不适用于OLTP)，或者通过alter table … move compresss 以及Table Redefinination将非压缩表迁移到压缩表

+SQL*Laoder
+Direct path Insert
+并行Insert
+CTAS

IOT表不支持压缩

字段数量超过255个不支持压缩
当表被设置为Basic Compress，表的PCTFREE参数自动设置为0，当Direct Path Load批量加载的数据无法填满一个块时，该块也不会被压缩
Basic Compress的表的数据块只能被压缩一次，并且无法再插入数据。因为使用Direct Path Load会将写入的块的位图信息设置为full，所以被压缩的块即使还有空间也无法以常规路径写入，而Direct Path Load只会选取HWM以上的块。
Basic Compress的表可以add column，但是不能设置add column default
Basic Compress的表不能drop column，但是可以将字段set unused
Update对Basic Compress的影响
因为当表设置为Basic Compress时，表的PCTFREE参数自动设置为0，所以当执行大量update时会产生大量的行链接，而且ORACLE并不会压缩链接块的数据，这样就会造成额外的存储空间开销和额外的性能开销，使得压缩毫无意义。

如果表在业务上是只读表，那么自然不用担心
如果表在业务上还存在大量的update操作，那么设置Basic Compress后可以根据需要适当调整pctfree
OLTP Compress
OLTP Compress是11gr1版本推出的压缩技术，是基于数据块级别的，对数据块内重复的行或者重复的字段值进行压缩，与Basic Compress相比的最大改进是支持常规路径的DML操作的数据也可以被压缩

OLTP Compress可压缩的类型
VARCHAR2
NUMBER
DATE
TIMESTAMP
RAW
LENGTH BYTE(存储在数据块行里的字段长度)
OLTP Compress不可压缩的类型
CHAR
CLOB
BLOB
LONG(不仅仅是LONG字段不能压缩，只要表里有LONG字段，该表就无法使用OLTP Compress)

OLTP Compress的特点：

支持常规路径的DML操作，当表被设置为OLTP Compress时，表的PCTFREE参数不会发生改变，并且当块里的数据达到PCTFREE上限时才会被压缩
支持Direct Path Load插入，但对于Direct Path Load插入的数据必须要达到块的PCTFREE上限才会被压缩
OLTP Compress的表的数据块可被压缩多次，当达到pctfree上限时，块里的记录被压缩，如果还有可用空间，将可以被常规路径写入，当再次达到pctfree上限时，继续被压缩，周而复始，直到block填满为止
IOT表不支持压缩
字段数量超过255个不支持压缩
OLTP Compress的表可以add column，也能设置add column default
OLTP Compress的表可以drop column，但是实质上也只是做了set unused操作
压缩比分析
11g之前，通常是写PL/SQL计算出压缩前的blocks和压缩后的blocks来估算压缩比，11g中ORACLE提供了DBMS_COMPRESSION包，可以较准确的评估压缩比，并且可以查出某行数据的压缩方式。

压缩比计算：

declare
blkcnt_cmp pls_integer;
blkcnt_uncmp pls_integer;
row_cmp pls_integer;
row_uncmp pls_integer;
cmp_ratio pls_integer;
comptype_str varchar2(100);
begin
dbms_compression.get_compression_ratio (‘users’, ‘scott’, ‘emp’, ”,
dbms_compression.comp_for_oltp,
blkcnt_cmp, blkcnt_uncmp, row_cmp, row_uncmp, cmp_ratio, comptype_str);
dbms_output.put_line(‘block count compressed = ‘ || blkcnt_cmp);
dbms_output.put_line(‘block count uncompressed = ‘ || blkcnt_uncmp);
dbms_output.put_line(‘row count per block compressed = ‘ || row_cmp);
dbms_output.put_line(‘row count per block uncompressed = ‘ || row_uncmp);
dbms_output.put_line(‘compression type = ‘ || comptype_str);
dbms_output.put_line(‘compression ratio = ‘||blkcnt_uncmp/blkcnt_cmp||’ to 1′);
dbms_output.put_line(‘compression ratio org= ‘||cmp_ratio);
end;
/

查看某行压缩方式:

select rowid,decode(dbms_compression.get_compression_type(
ownname => 'test',
tabname => 'compress_table_basic',
row_id => rowid),
1, 'no compression',
2, 'basic or oltp compression',
4, 'hybrid columnar compression for query high',
8, 'hybrid columnar compression for query low',
16, 'hybrid columnar compression for archive high',
32, 'hybrid columnar compression for archive low',
'unknown compression type') compression_type,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id
from test.compress_table_basic;

------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)