Oracle表压缩

表压缩

• 随着数据的增长，请考虑使用表压缩。压缩可以节省磁盘空间，减少 database buffer cache 中的内存使用量，并且可以加快查询执行速度。对压缩过后的表进行数据导入和 DML 需要消耗过多的 cpu 资源，可是压缩的使用降低了 I/O 成本，能够抵消额外的 cpu 成本
• 表压缩对应用程序是完全透明的。它在决策支持系统（DSS）、在线事务处理系统（OLTP）和 archival systems 很有用
• 可以为表空间、表或分区指定压缩。如果在表空间级别指定，那么在该表空间中创建的所有表都将默认进行压缩

basic 和 OLTP 压缩

• 使用 basic 压缩时，压缩仅在批量加载数据到表中时才会发生（仅直接路径加载的数据才会被压缩）
• 使用 OLTP 压缩时，数据被插入，更新或批量加载到表中时发生压缩。允许压缩的操作包括：
• 单行或数组插入和更新
• 插入和更新不会立即压缩
• 更新一个已经压缩的块时，未被更新的列仍然保持压缩状态，被更新的列以未压缩的格式存储。当块达到数据库控制的阈值时，更新的值将被重新压缩
• 当数据块中的数据达到数据库控制的阈值时，插入的数据也被压缩
• 直接路径INSERT方法：

Direct path SQL*Loader
CREATE TABLE AS SELECT statements
Parallel INSERT statements
INSERT statements with an APPEND or APPEND_VALUES hint

• basic 仅压缩通过直接路径加载插入的数据，并支持有限的数据类型和 SQL 操作；OLTP 压缩旨在用于 OLTP应用程序，可以压缩由任何 SQL 操作操纵的数据
• 对于 basic 和 OLTP 压缩来说，压缩块上的 DELETE 操作都与非压缩块上的 DELETE 操作相同。由 DELETE 操作所获得的任何空间都会被后续的 INSERT 操作重用
• 使用 CREATE TABLE 语句的 COMPRESS 子句指定表压缩，也可以通过在 ALTER TABLE 语句中使用这些子句来为现有表启用压缩，在这种情况下，只有启用压缩后插入或更新的数据才被压缩。同样，也可以使用 ALTER TABLE... NOCOMPRESS 语句禁用表压缩，在这种情况下，所有已压缩的数据都将保持压缩状态，并且新数据将被无压缩地插入

表压缩示例

• 1）在 orders 表上启用 OLTP 表压缩，表中的数据在直接路径INSERT和 常规 DML 期间都会被压缩

CREATE TABLE orders  ...  COMPRESS FOR OLTP;

• 2）在 sales_history 表上启用基本表压缩

CREATE TABLE sales_history ... COMPRESS BASIC;

CREATE TABLE sales_history ... COMPRESS;

• 3）使用直接路径插入将行插入到表中

INSERT /*+ APPEND */ INTO sales_history SELECT * FROM sales WHERE cust_id=8890;
COMMIT;

Compression and Partitioned Tables

• 一个表可以有压缩和未压缩的分区，不同的分区可以使用不同的压缩方法
• 如果表和某个分区的压缩设置不匹配，则分区上指定的压缩级别的优先级高
• 要更改分区的压缩方法，请执行以下操作之一：
• 若仅对新数据更改压缩方法，使用 ALTER TABLE ... MODIFY PARTITION. .. COMPRESS ...
• 若对新数据和现有数据都更改压缩方法，使用 ALTER TABLE ... MOVE PARTITION ... COMPRESS ... 或 在线表重定义

Determining If a Table Is Compressed

• 在 *_TABLES 数据字典视图中，压缩表在 COMPRESSION 列上是 ENABLED；对于分区表，该列为空，* _TAB_PARTITIONS 视图的 COMPRESSION 列指示分区的压缩
• 另外，COMPRESS_FOR 列指示表或分区使用的压缩方法

Adding and Dropping Columns in Compressed Tables

• 向压缩表添加列时，以下限制适用：
• basic 压缩：不能为新加列指定默认值
• OLTP 压缩：如果为新加列指定了默认值，则该列必须为 NOT NULL；不支持添加有默认值的可空列
• 在压缩表中删除列时，下列限制适用：
• basic 压缩：不支持删除列
• OLTP压缩：支持 DROP COLUMN，但在内部，数据库将列设置为 UNUSED 以避免长时间运行的解压缩和重压缩操作

GET_COMPRESSION_TYPE

• 该函数返回指定行的压缩类型。如果行被链接，则函数只返回 head piece 的压缩类型，并且不检查 intermediate or the tail piece，因为 head piece 可以被压缩为不同类型
• 语法

DBMS_COMPRESSION.GET_COMPRESSION_TYPE (
  ownname    IN    VARCHAR2, 
  tabname    IN    VARCHAR2, 
  row_id    IN    ROWID)
  RETURN NUMBER;

• 参数
  
• 返回值：用于指示压缩类型的标志
  

操作

• 使用场景：使用 exp/imp 操作的表，在新库会变成非压缩，需要手动压缩
• 1）查看数据库中被锁定的对象

select p.spid,
      c.object_name,
      b.session_id,
      b.oracle_username,
      b.os_user_name
  from v$process p, v$session a, v$locked_object b, all_objects c
where p.addr = a.paddr
  and a.process = b.process
  and c.object_id = b.object_id;

• 2）查看某个表的分区情况、压缩情况（10g 库没有 COMPRESS_FOR 列）

-- 是不是分区表
select s.PARTITIONED,s.COMPRESSION,s.COMPRESS_FOR from user_tables s where s.TABLE_NAME='ORDERES';

-- 是否有分区
select distinct s.composite,s.subpartition_count,s.tablespace_name,s.compression,s.compress_for from dba_tab_partitions s where s.table_name='ORDERES';

-- 是否有子分区
select distinct s.tablespace_name,s.compression,s.compress_for from dba_tab_subpartitions s where s.table_name='ORDERES';

• 3）11g 中查看表是否压缩

SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(
                ownname => 'U1',
                tabname => 'ORDERES',
                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
FROM U1.ORDERES
-- SUBPARTITION(PART201711_SUBPART_181)   -- 也可以是 PARTITION
where rownum <= 10;

• 4）使用下面的语句进行压缩：

-- 无分区
alter table ORDERES move compress;

-- 无子分区
DECLARE
  CURSOR V_PARTITION IS
    SELECT DISTINCT PARTITION_NAME FROM USER_TAB_PARTITIONS T WHERE T.TABLE_NAME = 'ORDERES_M';
BEGIN
  FOR C1 IN V_PARTITION LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE ORDERES_M MOVE PARTITION ' ||
                        C1.PARTITION_NAME ||
                        'COMPRESS';
    END LOOP;
END;

DECLARE
  CURSOR V_PARTITION IS
    SELECT DISTINCT PARTITION_NAME FROM DBA_TAB_PARTITIONS T WHERE T.TABLE_NAME = 'ORDERS' AND T.TABLE_OWNER='U1' 
    AND T.COMPRESSION='DISABLED';
BEGIN
  FOR C1 IN V_PARTITION LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE U1.ORDERES MOVE PARTITION ' ||
                        C1.PARTITION_NAME ||
                        'COMPRESS';
    END LOOP;
END;

-- 有子分区
DECLARE
  CURSOR V_SUBPARTITION IS
    SELECT DISTINCT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS T WHERE T.TABLE_NAME = 'ORDERES_M';
BEGIN
  FOR C2 IN V_SUBPARTITION LOOP
      EXECUTE IMMEDIATE 'ALTER TABLE ORDERES_M MOVE SUBPARTITION ' ||
                        C2.SUBPARTITION_NAME ||
                        ' compress ';    
    END LOOP;
END;