The database can use table compression to reduce the amount of storage required for the table.

Compression saves disk space, reduces memory use in the database buffer cache, and in some cases speeds query execution. Table compression is transparent to database applications.

citrix certification malaysia

Basic Table Compression and Advanced Row Compression

Dictionary-based table compression provides good compression ratios for heap-organized tables.

cisco certification training courses malaysia

Oracle Database supports the following types of dictionary-based table compression:

• Basic table compressionThis type of compression is intended for bulk load operations. The database does not compress data modified using conventional DML. You must use direct path INSERT operations, ALTER TABLE . . . MOVE operations, or online table redefinition to achieve basic table compression.
• Advanced row compressionThis type of compression is intended for OLTP applications and compresses data manipulated by any SQL operation. The database achieves a competitive compression ratio while enabling the application to perform DML in approximately the same amount of time as DML on an uncompressed table.

For the preceding types of compression, the database stores compressed rows in row major format. All columns of one row are stored together, followed by all columns of the next row, and so on. The database replaces duplicate values with a short reference to a symbol table stored at the beginning of the block. Thus, information that the database needs to re-create the uncompressed data is stored in the data block itself.

ccnp certification training courses malaysia

Compressed data blocks look much like normal data blocks. Most database features and functions that work on regular data blocks also work on compressed blocks.

You can declare compression at the tablespace, table, partition, or subpartition level. If specified at the tablespace level, then all tables created in the tablespace are compressed by default.

Example 2-4 Table-Level Compression

The following statement applies advanced row compression to the orders table:

CopyALTER TABLE oe.orders ROW STORE COMPRESS ADVANCED;

Example 2-5 Partition-Level Compression

The following example of a partial CREATE TABLE statement specifies advanced row compression for one partition and basic table compression for the other partition:

CopyCREATE TABLE sales (
    prod_id     NUMBER     NOT NULL,
    cust_id     NUMBER     NOT NULL, ... )
 PCTFREE 5 NOLOGGING NOCOMPRESS
 PARTITION BY RANGE (time_id)
 ( partition sales_2013 VALUES LESS THAN(TO_DATE(...)) ROW STORE COMPRESS BASIC,
   partition sales_2014 VALUES LESS THAN (MAXVALUE) ROW STORE COMPRESS ADVANCED );

See also

• “Row Format” to learn how values are stored in a row
• “Data Block Compression” to learn about the format of compressed data blocks
• “SQL*Loader” to learn about using SQL*Loader for direct path loads
• Oracle Database Administrator’s Guide and Oracle Database Performance Tuning Guide to learn about table compression

ccie certification training courses malaysia