【Oracle OCP】Oracle 19c之表压缩

作者：炎燚小寶

发布时间：2023-12-19 09:23

浏览量：1070

本文为云贝教育刘峰原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

一、普通压缩

Oracle 19c允许压缩整个表或单个表分区以减少磁盘空间需求。这里描述的基本压缩是数据库企业版中的免费功能。它专为压缩静态数据而设计，因为它仅适用于直接路径插入，不适用于 OLTP 系统中典型的单行插入、更新和删除操作。

1、创建表

通过将 COMPRESS 关键字添加到表定义的末尾来启用表压缩，如下所示。

DROP TABLE test_tab;
CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
COMPRESS BASIC;

默认压缩状态为 NOCOMPRESS。

表的压缩状态可以使用 [DBA|ALL|USER]_TABLES 视图中的 COMPRESSION 列来显示。 Oracle 11g 中引入的替代压缩模式（此处描述）附带了一个名为 COMPRESS_FOR 的附加列，用于指示压缩类型。

SELECT compression, compress_for
FROM   user_tables
WHERE  table_name = 'TEST_TAB';
COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  BASIC

2、修改表

可以使用 ALTER TABLE 语句更改现有表的压缩状态。

ALTER TABLE test_tab NOCOMPRESS;
SELECT compression, compress_for
FROM   user_tables
WHERE  table_name = 'TEST_TAB';

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED

SQL>

ALTER TABLE test_tab COMPRESS;

SELECT compression, compress_for
FROM   user_tables

WHERE  table_name = 'TEST_TAB';
COMPRESS COMPRESS_FOR

-------- ------------------------------

ENABLED  BASIC

这不会影响现有数据的压缩，但会影响直接路径加载加载的新数据的压缩。如果要压缩现有数据，则必须执行移动操作，以便数据在复制过程中被压缩。

ALTER TABLE test_tab MOVE NOCOMPRESS;

ALTER TABLE test_tab MOVE COMPRESS;

请记住，当您执行移动操作时，您将在一段时间内拥有该表的两个副本。确保您有足够的存储空间来应对这种情况。
可以对分区表的单个分区执行类似的操作。

ALTER TABLE test_tab MOVE PARTITION test_tab_q2 COMPRESS;

3、分区表

在表级别使用 COMPRESS 关键字使其成为所有分区的默认值。

DROP TABLE test_tab;
CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
COMPRESS
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/04/2023', 'DD/MM/YYYY')),
  PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE)
);

[DBA|ALL|USER]_TAB_PARTITIONS 视图显示各个分区的压缩状态。
 
COLUMN partition_name FORMAT A30

SELECT partition_name, compression, compress_for
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TAB'
ORDER BY 1;

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
TEST_TAB_Q1                    ENABLED  BASIC
TEST_TAB_Q2                    ENABLED  BASIC
SQL>

可以控制分区表中各个分区的压缩状态。可以为所有分区显式指定压缩状态，也可以使用表默认值，但在分区级别指定的默认值除外。在以下示例中，默认的 NOCOMPRESS 选项在表级别显式设置（这不是必需的），第一个分区使用 COMPRESS 关键字覆盖默认值。

DROP TABLE test_tab;
CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
NOCOMPRESS
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/04/2003', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE)
);

COLUMN partition_name FORMAT A30

SELECT partition_name, compression, compress_for
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TAB'
ORDER BY 1;

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
TEST_TAB_Q1                    ENABLED  BASIC
TEST_TAB_Q2                    DISABLED

如前所述，我们可以使用 ALTER TABLE 命令更改分区的压缩状态并压缩其内容。

ALTER TABLE test_tab MOVE PARTITION test_tab_q2 COMPRESS;

COLUMN partition_name FORMAT A30

SELECT partition_name, compression, compress_for
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TAB'
ORDER BY 1;

PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
TEST_TAB_Q1                    ENABLED  BASIC
TEST_TAB_Q2                    ENABLED  BASIC

4、检查表的压缩级别

使用一个压缩分区和一个非压缩的partiton创建下表。

DROP TABLE test_tab;

CREATE TABLE test_tab (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(100) NOT NULL,
  created_date  DATE          NOT NULL,
  created_by    VARCHAR2(50)  NOT NULL,
  updated_date  DATE,
  updated_by    VARCHAR2(50)
)
NOCOMPRESS
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('06/08/2023', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (MAXVALUE)
);

使用直接路径插入将一些行插入每个分区。我们将单独执行此操作并捕获每个操作所用的时间和 CPU 时间。

-- TEST_TAB : Direct path insert into non-compressed partition.

SET SERVEROUTPUT ON

DECLARE
  v_date        test_tab.created_date%TYPE := SYSDATE-1;
  v_user        test_tab.created_by%TYPE   := USER;
  l_start_time  NUMBER;
  l_start_cpu   NUMBER;
BEGIN
  l_start_time := DBMS_UTILITY.get_time;
  l_start_cpu  := DBMS_UTILITY.get_cpu_time;
  INSERT /*+ APPEND */ INTO test_tab (id, description, created_date, created_by)
  SELECT level,
         'A very repetitive, and therefore very compressible column value',
         v_date,
         v_user
  FROM   dual
  CONNECT BY level <= 1000000; COMMIT; DBMS_OUTPUT.put_line('CPU Time (hsecs) : ' || (DBMS_UTILITY.get_cpu_time - l_start_cpu)); DBMS_OUTPUT.put_line('Elapsed Time (hsecs): ' || (DBMS_UTILITY.get_time - l_start_time)); END; / CPU Time (hsecs) : 348 Elapsed Time (hsecs): 600 PL/SQL procedure successfully completed. SQL>

-- TEST_TAB : Direct path insert into compressed partition.

SET SERVEROUTPUT ON

DECLARE
  v_date        test_tab.created_date%TYPE := TO_DATE('31/08/2023', 'DD/MM/YYYY');
  v_user        test_tab.created_by%TYPE   := USER;
  l_start_time  NUMBER;
  l_start_cpu   NUMBER;
BEGIN
  l_start_time := DBMS_UTILITY.get_time;
  l_start_cpu  := DBMS_UTILITY.get_cpu_time;
  INSERT /*+ APPEND */ INTO test_tab (id, description, created_date, created_by)
  SELECT level,
         'A very repetitive, and therefore very compressible column value',
         v_date,
         v_user
  FROM   dual
  CONNECT BY level <= 1000000; COMMIT; DBMS_OUTPUT.put_line('CPU Time (hsecs) : ' || (DBMS_UTILITY.get_cpu_time - l_start_cpu)); DBMS_OUTPUT.put_line('Elapsed Time (hsecs): ' || (DBMS_UTILITY.get_time - l_start_time)); END; / CPU Time (hsecs) : 117 Elapsed Time (hsecs): 236 PL/SQL procedure successfully completed.

从重复运行中我们发现，与未压缩的表或分区相比，直接路径插入压缩表或分区需要更多的 CPU，并且需要更长的时间。

收集统计信息后，我们可以使用 [DBA|ALL|USER]_TAB_PARTITIONS 视图检查压缩情况。

EXEC DBMS_STATS.gather_schema_stats('U2', cascade => TRUE);

set linesize 1000
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
SELECT table_name,
       partition_name,
       compression,
       num_rows,
       blocks,
       empty_blocks
FROM   user_tab_partitions
WHERE  table_name = 'TEST_TAB'
ORDER BY 1;

TABLE_NAME           PARTITION_NAME       COMPRESS   NUM_ROWS     BLOCKS EMPTY_BLOCKS
-------------------- -------------------- -------- ---------- ---------- ------------
TEST_TAB             TEST_TAB_Q1          ENABLED     1000000       1420            0
TEST_TAB             TEST_TAB_Q2          DISABLED    1000000      11898            0
SQL>

我们在这里看到的是，根据用于保存数据的块数，对于相同行数，压缩分区几乎要小一个数量级，因此，如果存储大小是一个问题，则 CPU 开销和直接运行时间路径插入可能是值得的。

请记住，压缩级别将根据数据而变化。在这种情况下，我们作弊并为每一行使用相同的文本数据、用户名和创建日期，使数据在块中高度可压缩。不同的数据集可能不会产生如此引人注目的结果。

5、注意事项

使用此功能之前需要考虑的一些事项。

基本压缩是企业版数据库的免费选项。

基本压缩不是为 OLTP 操作而设计的。您不会从传统的路径插入、更新或删除中获得任何压缩优势。它仅设计用于直接路径加载。如果您希望表压缩适合 OLTP 操作，则需要使用从 11g 开始提供的高级压缩选项（此处描述）。

基本压缩可与使用分区的 OLTP 系统一起使用。例如，如果您有一个按日期列分区的表，则您的分区可能包含较旧的静态数据，这些数据可以被压缩，而将较新的活动数据保留在未压缩的分区中。

根据查询的性质，基本压缩可以通过减少从磁盘读取的块数来提高查询性能。这在很大程度上取决于工作量。

如前所述，您可以实现的压缩级别将根据压缩数据的性质而有所不同。对于基本压缩，压缩是在块级别执行的，因此单个块必须包含重复数据才能使压缩产生影响。

二、高级压缩

Oracle 9i 中引入了表压缩，作为数据仓库项目的空间节省功能。在11g中，它现在被认为是OLTP数据库可以接受的主流功能。除了节省存储空间之外，压缩还可以提高 I/O 性能并减少缓冲区高速缓存中的内存使用。这些优势确实是有代价的，因为压缩会产生 CPU 开销，所以它不会对每个人都有利。

基本表压缩是企业版数据库的免费功能，但 OLTP 压缩需要高级压缩选项。

可以使用以下选项在表空间、表或分区级别指定压缩子句：

NOCOMPRESS -不压缩表或分区。这是未指定压缩子句时的默认操作。

COMPRESS——这个选项被认为适合于数据仓库系统。只在直接路径插入期间对表或分区启用压缩。

COMPRESS FOR DIRECT_LOAD OPERATIONS -这个选项与简单的COMPRESS关键字具有相同的作用。
COMPRESS FOR ALL OPERATIONS——这个选项被认为适用于OLTP系统。顾名思义，此选项支持对所有操作(包括常规DML语句)进行压缩。该选项要求将COMPATIBLE初始化参数设置为11.1.0或更高。在11gR2中，该选项已被重命名为COMPRESS FOR OLTP，而原来的名称已被弃用。

以下示例显示了在表和分区级别应用的各种压缩选项。

- Table compression.
--11g
CREATE TABLE test_tab_1 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
COMPRESS FOR ALL OPERATIONS;

DROP TABLE test_tab_1;

--19c
CREATE TABLE test_tab_1 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
ROW STORE COMPRESS ADVANCED;

-- Partition-level compression.
CREATE TABLE test_tab_2 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2008', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
  PARTITION test_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2008', 'DD/MM/YYYY')) ROW STORE COMPRESS ADVANCED,
  PARTITION test_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);

表级压缩设置反映在 [DBA|ALL|USER]_TABLES 视图的 COMPRESSION 和 COMPRESS_FOR 列中。

SELECT table_name, compression, compress_for FROM user_tables;
 
TABLE_NAME           COMPRESS COMPRESS_FOR
-------------------- -------- ------------------------------
T1                   DISABLED
TEST_TAB
TEST_TAB_1           ENABLED  ADVANCED
TEST_TAB_2

使用分区级压缩定义的表和未定义表级压缩的表在这些列中显示 NULL 值。

分区级压缩设置反映在 [DBA|ALL|USER]_TAB_PARTITIONS 视图的 COMPRESSION 和 COMPRESS_FOR 列中。

SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions where table_name='TEST_TAB_2';

TABLE_NAME           PARTITION_NAME       COMPRESS COMPRESS_FOR
-------------------- -------------------- -------- ------------------------------
TEST_TAB_2           TEST_TAB_Q1          ENABLED  BASIC
TEST_TAB_2           TEST_TAB_Q2          ENABLED  BASIC
TEST_TAB_2           TEST_TAB_Q3          ENABLED  ADVANCED
TEST_TAB_2           TEST_TAB_Q4          DISABLED

可以使用 ALTER TABLE 命令修改表和分区的压缩设置。这些更改不会影响现有数据，只会影响应用于表的新操作。

ALTER TABLE test_tab_1 NOCOMPRESS;
ALTER TABLE test_tab_2 MODIFY PARTITION test_tab_q4 COMPRESS FOR ALL OPERATIONS;

可以使用 CREATE TABLESPACE 和 ALTER TABLESPACE 命令在表空间级别指定默认压缩设置。当前设置显示在 DBA_TABLESPACES 视图的 DEF_TAB_COMPRESSION 和 COMPRESS_FOR 列中。

CREATE TABLESPACE test_ts
  DATAFILE
  SIZE 1M
  DEFAULT ROW STORE COMPRESS ADVANCED;
  
SELECT def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_TS';

DEF_TAB_ COMPRESS_FOR
-------- ------------------------------
ENABLED  ADVANCED

1 row selected.

SQL>

ALTER TABLESPACE test_ts DEFAULT NOCOMPRESS;

SELECT def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_TS';

DEF_TAB_ COMPRESS_FOR
-------- ------------------
DISABLED

1 row selected.

SQL>

DROP TABLESPACE test_ts INCLUDING CONTENTS AND DATAFILES;

当在多个级别指定压缩时，始终使用最具体的设置。因此，分区设置始终覆盖表设置，而表设置始终覆盖表空间设置。

与表压缩相关的限制包括：

压缩表只能在使用ROW STORE COMPRESS ADVANCED选项时添加或删除列。
压缩表的列不能超过255列。
压缩不应用于LOB段。
表压缩仅对堆组织的表有效，对索引组织的表无效。
压缩子句不能应用于哈希或哈希列表分区。相反，它们必须从表空间、表或分区设置中继承它们的压缩设置。
不能为外部表或集群表指定表压缩。

登录
手机号
密码
	注册忘记密码

找回密码
手机号
图形验证码
短信验证码
新密码
确认新密码

注册
手机号
图形验证码
短信验证码
密码
确认密码

操作系统

数据库

云计算

大数据

人工智能

网络安全

管理

软考