[bbk5156] 第106集 -第13章 - 表空间管理 04

在oracle 9i版本中，表的压缩技术就已经引入.

oracle可以做到，直接读取压缩的数据，不用解压再读取，大大提高了读写效率.

压缩后，存储空间减少，占用的块也会减少，读取时I/O必然也会减少，自然而然地性能就提高了.关键是在读取的时候不需要解压，即可进行读取，如此更加大大提高读取的速率.

Table Compression:Overview

Reducing storage costs by compressing all data:

• Basic compression for direct-path insert operations:10x
• OLTP compression for all DML operations:2-4x

{COMPRESS [ BASIC | FOR { OLTP } ] | NOCOMPRESS}

Compression Method	Compression Ratio	CPU Overhead	CREATE and ALTER TABLE Syntax	Typical Applications
Basic table compression	High	Minimal	COMPRESS [BASIC]	DSS
OLTP table compression	High	Minimal	COMPRESS FOR OLTP	OLTP,DSS

 

 

 

 

Compression for Direct-Path Insert Operations

• Is enabled with CREATE TABLE ...COMPRESS BASIC ...;
• Is recommended for bulk loading data warehouses
• Replaces deprecated COMPRESS FOR DIRECT_LOAD_OPERATIONS
• Maximizes contiguous free space in blocks

压缩原理：用户在执行DML语句的时候，不是实时压缩的，而是当数据块中快满的时候，会触发一个压缩功能的触发器将现有数据进行压缩，压缩完之后会继续进行DML操作，直至块满；

OLTP Compression for DML Operations

• Is enabled with CREATE TABLE ... COMPRESS FOR OLTP ...;
• Is recommended for active OLTP enviroments
• Resplaces deprecated COMPRESS FOR ALL OPERATIONS

Using the Compression Advisor

The compression advisor:

• Analyzes objects to given an estimate of space savings for different compression methods
• Helps in deciding the correct compression level for an application.
• Recommends various strategies for compression
  • Picks the right compression algorithm for a particular data set.
  • Sorts on a particular column for increasing the compression ratio.
  • Presents tradeoffs between different compression algorithms 
• Works for OLTP compression(via EM);

Using the DBMS_COMPRESSION Package

To determine optimal compression ratios: