【Oracle OCP】Oracle 19c之表压缩

作者：炎燚小寶

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

浏览量：562

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

一、普通压缩

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段。
表压缩仅对堆组织的表有效，对索引组织的表无效。
压缩子句不能应用于哈希或哈希列表分区。相反，它们必须从表空间、表或分区设置中继承它们的压缩设置。
不能为外部表或集群表指定表压缩。

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

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

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

云计算

数据库

大数据

人工智能