Skip to content

Oracle Table and Index Compression – Everything an Oracle DBA needs to know

 


10g Table compression: Basics, Examples, Advantages,Uses and Restrictions [ID 753213.1]  

 
  Modified 02-APR-2010     Type BULLETIN     Status PUBLISHED  
       

In this Document
  Purpose
  Scope and Application
  10g Table compression: Basics, Examples, Advantages,Uses and Restrictions
     OVERVIEW OF TABLE COMPRESSION: 
  References


Applies to:

Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.4
Information in this document applies to any platform.
Oracle Server Enterprise Edition – Version: 10.1.0.2 to 10.2.0.4

Purpose

This note provides basic understanding of Table compression with appropriate examples.

Scope and Application

DBA’s interested  in understanding and using Table Compression feature

10g Table compression: Basics, Examples, Advantages,Uses and Restrictions

OVERVIEW OF TABLE COMPRESSION: 

The Oracle Database table compression feature compresses data by eliminating duplicate values in a database block. Compressed data stored in a database block (also known as disk page) is self-contained. That is, all the information needed to re-create the uncompressed data in a block is available within that block. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table.

With the exception of a symbol table at the beginning, compressed database blocks look very much like regular database blocks. All database features and functions that work on regular database blocks also work on compressed database blocks.

Database objects that can be compressed include tables,materialized views,partitioned tables( some or all partitions can be compressed) .

Compression clause can be specified with following options:

1) NOCOMPRESS – The table,partition or materialized view is not compressed. This is the default action when no compression clause is specified.

2) COMPRESS – This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.

3) COMPRESS FOR DIRECT_LOAD OPERATIONS – This option has the same affect as the simple COMPRESS keyword.

EXAMPLES:

Table compression:

These two examples both enable compression for direct-path insert only on the ‘sales_history’ table:

SQL> CREATE TABLE sales_history ( ... ) COMPRESS FOR DIRECT_LOAD OPERATIONS;

SQL> CREATE TABLE sales_history ( ... ) COMPRESS;
Partition-level compression:

Compression can be enabled or disabled at the partition level
In the following example, all partitions except the northeast partition are compressed.

SQL> CREATE TABLE sales
(saleskey number,
quarter number,
product number,
salesperson number,
amount number(12, 2),
region varchar2(10)) COMPRESS
PARTITION BY LIST (region)
(PARTITION northwest VALUES ('NORTHWEST'),
PARTITION southwest VALUES ('SOUTHWEST'),
PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,
PARTITION southeast VALUES ('SOUTHEAST'),
PARTITION western VALUES ('WESTERN'));
–Materialized View compression:

Following example shows Materialized View compression

SQL> create materialized view mv compress as select * from tab1;
SQL> create materialized view mv1 as select * from dba_objects;
Compression attribute for a table can be altered(or for a partition or tablespace), and the change only applies to new data going into that table. As a result, a single table or partition may contain some compressed blocks and some regular blocks. This guarantees that data size will not increase as a result of compression.

DETERMINE IF TABLE IS COMPRESSED:

In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column. For partitioned tables, this column is null, and the COMPRESSION column of the *_TAB_PARTITIONS data dictionary view indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates whether the table is compressed FOR ALL OPERATIONS or for DIRECT LOAD ONLY.

SQL> SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME COMPRESS COMPRESS_FOR
---------------- -------- ------------------
T1 DISABLED
T2 ENABLED DIRECT LOAD ONLY
T3 ENABLED FOR ALL OPERATIONS

SQL> select compression from user_tables where table_name = 'MV1';

COMPRESS
--------
DISABLED----------------(check compression for materialized view)
WHEN TO USE COMPRESSION:

Table compression should be used with highly redundant data, such as tables with many foreign keys. You should avoid compressing tables with much update or other DML activity. Although compressed tables or partitions are updatable, there is some overhead in updating these tables, and high update activity may work against compression by causing some space to be wasted.

Advanced Compression Advisor can be used to estimate compression ratio.
Please refer to the following note for Advanced Compression Advisor:

Document 950293.1 Advanced Compression Advisor

OPERATIONS FOR WHICH COMPRESSION OCCURS:

Compression can occur while data is being inserted, updated, bulk inserted, or bulk loaded into a compressed table. These operations include:

1) Direct path SQL*Loader

2) CREATE TABLE and AS SELECT statements

3) Parallel INSERT (or serial INSERT with an APPEND hint) statements

4) Single-row or array inserts

5) Single-row or array updates

Existing data in the database can also be compressed by moving it into compressed form through ALTER TABLE and MOVE statements. This operation takes an exclusive lock on the table, and therefore prevents any updates and loads until it completes. If this is not acceptable, the Oracle Database online redefinition utility (the DBMS_REDEFINITION PL/SQL package) can be used

There are few methods to partition a non-partitioned table.
Please refer following metalink notes:

Document 472449.1 How To Partition Existing Table Using DBMS_Redefinition

Document 1070693.6 How to partition a non-partitioned table

RESTRICTIONS ASSOCIATED WITH COMPRESSION:

1) Compressed tables cannot have columns added or dropped.
2) Compressed tables must not have more than 255 columns.
3) Compression is not applied to lob segments.
4) Table compression is only valid for heap organized tables, not index organized tables.
5) The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the tablespace, table or partition settings.
6) Table compression cannot be specified for external or clustered tables.

For compression features in 11g, please refer to following notes and documentation:

Document 466362.1 11g Table compression : Overview
Document 882712.1 11g New feature:All About Advanced Table Compression (Overview,Use,Examples,Restrictions)
Document 785787.1 11g new features:Advanced Compression Overview and Advantages

Oracle Database Licensing Information 11g Release 1 (11.1)
Oracle Database Concepts 11g Release 1 (11.1) Part Number B28318-05

Oracle documentation is available from the Oracle Technology Network (OTN): http://www.oracle.com/technology/index.html

References

http://www.oracle.com/technology/products/database/oracle11g/pdf/advanced-compression-whitepaper.pdf

Related


Products


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition

Keywords


COMPRESSION

 

Back to top

Rate this document 

Article Rating

Rate this document
Excellent
Good
Poor
 
Did this document help you?
Yes
No
Just browsing
 
How easy was it to find this document?
Very easy
Somewhat easy
Not easy

 

  Comments

 
Cancel    

 

 

What does 10g Compression actually do? [ID 561447.1]  

 
  Modified 04-MAR-2010     Type HOWTO     Status PUBLISHED  
       

In this Document
  Goal
  Solution


Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.4 – Release: to 10.2

Goal

What is the resultant affects on the segments and space utilization when runnning the
ALTER TABLE MOVE COMPRESS command.

The ALTER TABLE…MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE…MOVE statement with a COMPRESS clause to store the new segment using table compression.

Solution

Alter table move compress is compressing the data by creating new extents for the compressed data in the tablespace being miove to, so the size of the table is decreased but all the extents of the table are now  at the end of the datafile.
I have worked on the TESTT table to investigate this issue,  (tests are made in 10g):

SQL> select segment_name,blocks,bytes/1024/1024 from user_Segments where segment_name = 'TESTT';

SEGMENT_NAME BLOCKS BYTES/1024/1024
------------------------------------------------------------------------------
TESTT          1408    11
the table is has 1408 blocks which consumes about 11MB.

The highwater mark is at

select a.tablespace_name, a.file_name,(b.maximum+c.blocks-1)*d.db_block_size highwater
from dba_data_files a ,(select file_id,max(block_id) maximum
from dba_extents group by file_id) b
,dba_extents c
,(select value db_block_size from v$parameter
where name='db_block_size') d
where a.file_id = b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
and a.tablespace_name='TEST'
/

TABLESPACE_NAME      FILE_NAME                       HIGHWATER
----------------------------------------------------------------
TEST       /oracle/oradata/d1v10203/test01.dbf       11599872
and after the size of 11 MB there is allocated one extent:

SQL> SELECT owner, segment_name, segment_type, tablespace_name, file_id,
2 ((block_id+1)*(SELECT value FROM v$parameter WHERE UPPER(name)='DB_BLOCK_SIZE')+ BYTES)
end_of_extent_is_at_this_byte
3 FROM dba_extents WHERE ((block_id+1)*
4 (SELECT value FROM v$parameter WHERE UPPER(name)='DB_BLOCK_SIZE')+BYTES) > (11*1024*1024)
5 AND tablespace_name='TEST' ORDER BY file_id, end_of_extent_is_at_this_byte;

OWNER   SEGMENT_NAME  SEGMENT_TYPE TABLESPACE_NAME FILE_ID END_OF_EXTENT_IS_AT_THIS_BYTE
------------------------------
SCOTT    TESTT        TABLE              TEST        9
11616256

Now we are doing the

alter table testt move compress;
The table has now 4 MB in size about 512 blocks

SQL> select sum(bytes/1024/1024) total_bytes from user_Extents where segment_name = 'TESTT';

TOTAL_BYTES
-----------
4

SQL> select segment_name,blocks,bytes/1024/1024 from user_Segments where segment_name = 'TESTT';

SEGMENT_NAME BLOCKS BYTES/1024/1024
----------------------------------------------------------------------
TESTT          512    4

As can be seen the table size is smaller after the compress.  However, this does not  mean that the datafile can be resized as the new blocks allocated to the table are at the end of the datafile and the blocks which are free are in fact the first 11 MB from the old file before we moved the table from the initial location, as demonstrated below:

SQL> select a.tablespace_name
2 ,a.file_name
3 ,(b.maximum+c.blocks-1)*d.db_block_size highwater
4 from dba_data_files a
5 ,(select file_id,max(block_id) maximum
6 from dba_extents
7 group by file_id) b
8 ,dba_extents c
9 ,(select value db_block_size
10 from v$parameter
11 where name='db_block_size') d
12 where a.file_id = b.file_id
13 and c.file_id = b.file_id
14 and c.block_id = b.maximum
15 and a.tablespace_name='TEST'
16 /

TABLESPACE_NAME   FILE_NAME                            HIGHWATER
------------------------------
TEST       /oracle/oradata/d1v10203/test01.dbf          15794176

The file has now about 15 MB with around 11 MB free

SQL> select tablespace_name,file_id,block_id,bytes,blocks from dba_free_space where
tablespace_name = 'TEST';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES     BLOCKS
------------------------------ ---------- ----------
TEST              9        9     11534336 1408

We can show that the first 11 extents are now at the end of the new datafile:

SQL> SELECT owner, segment_name, segment_type, tablespace_name, file_id,
2 ((block_id+1)*(SELECT value FROM v$parameter WHERE UPPER(name)='DB_BLOCK_SIZE')+ BYTES)
end_of_extent_is_at_this_byte
3 FROM dba_extents WHERE ((block_id+1)*
4 (SELECT value FROM v$parameter WHERE UPPER(name)='DB_BLOCK_SIZE')+BYTES) > (11*1024*1024)  <<<<<-------------
5 AND tablespace_name='TEST' ORDER BY file_id, end_of_extent_is_at_this_byte;

OWNER SEGMENT_NAME SEGMENT_TYPE
TABLESPACE_NAME FILE_ID END_OF_EXTENT_IS_AT_THIS_BYTE
------------------------------
--------------------------------------------------------------------
------------------ ------------------------------ ---------- -----------------------------
SCOTT
TESTT
TABLE TEST 9 11681792
SCOTT
TESTT
TABLE TEST 9 11747328
SCOTT
TESTT
TABLE TEST 9 11812864
SCOTT TESTT
...
SCOTT
TESTT
TABLE TEST 9 14761984
SCOTT
TESTT
TABLE TEST 9 15810560

19 rows selected.

and no extents are allocated in the beginning of the datafile:

SQL> SELECT owner, segment_name, segment_type, tablespace_name, file_id,
2 ((block_id+1)*(SELECT value FROM v$parameter WHERE UPPER(name)='DB_BLOCK_SIZE')+ BYTES)
end_of_extent_is_at_this_byte
3 FROM dba_extents WHERE ((block_id+1)*
4 (SELECT value FROM v$parameter WHERE UPPER(name)='DB_BLOCK_SIZE')+BYTES) < (11*1024*1024)
5 AND tablespace_name='TEST' ORDER BY file_id, end_of_extent_is_at_this_byte;

no rows selected


<<End_of_Article>> FOLDER:ST.Server.DBWarehouse.Compression TOPIC:Compression DOCUMENT-ID:561447.1 ALIAS: SOURCE:AWIZ 6800739.994 DOCUMENT-TYPE:HOWTO ZCXTECH TITLE:10g Compression IMPACT:LOW SKILL-LEVEL:NOVICE STATUS:REVIEW_READY DISTRIBUTION:EXTERNAL ZCXCURRENT AUTHOR:ATINDECH.RO PRODID-5 COMPONENT:COMPRESSION MINVER:10.2.0.1 MAXVER:10.2.0.1 PORTID-0 FDRSEG-1218 FDRSEG-1216 FDRSEG-773 FDRSEG-465

Related


Products


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition

 

Back to top

Rate this document 

Article Rating

Rate this document
Excellent
Good
Poor
 
Did this document help you?
Yes
No
Just browsing
 
How easy was it to find this document?
Very easy
Somewhat easy
Not easy

 

  Comments

 
Cancel    

 

 

How To Use Compress Indexes [ID 131747.1]  

 
  Modified 29-OCT-2007     Type BULLETIN     Status PUBLISHED  
       
 
PURPOSE
  This note will present details about the COMPRESS option for indexes.

SCOPE & APPLICATION
  Oracle database users.
 
 
How To Use Compress Indexes:
============================
 
Beginning with Oracle8i, there is an index COMPRESS option to enable key
compression which eliminates repeated occurence of key column values and may
substantially reduce storage.
 
You can use this COMPRESS option for btree indexes and IOT.
 
The compression is realized by splitting the index key in two parts: the prefix
and suffix part.  Use integer to specify the prefix length (number of prefix
columns to compress).  If you indicate the COMPRESS option without range,
Oracle will take all the columns minus the last one for compression.
 
The maximum columns which may be choosen for prefix may be:
       
        - all the columns if the index is non unique.
        - all the columns minus one if the index is unique.
 
The prefix part is choosen as a common part, whereas the suffix is considered
as a unique key.  Each prefix part will then shared between all the suffix
parts.  It offers the means to load more keys in each block, thus increases
performance with access by index by limiting the number of accessed blocks.
The key compression is made each block by each block, and only at the leaf
level.  On the contrary, the performance by index scan will decrease because
Oracle must translate the <prefix, suffix> part in corresponding key.
 
Oracle compresses only single-column indexes that are non unique or unique
indexes of at least two columns.
 
 
Restriction:  You cannot specifiy COMPRESS for a bitmap index.
============
 
 
If you consider the following example with a unique key:
 
    CREATE TABLE EMP
       (EMPNO NUMBER UNIQUE,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER);
 
    CREATE UNIQUE INDEX COMPRESS_UNIQUE_EMP ON EMP(ENAME, EMPNO) COMPRESS 1;
 
In the COMPRESS_UNIQUE_EMP index, the prefix part is choosen as the (ENAME)
key, whereas the suffix part is (EMPNO).  In each block, each occurence common
part (ENAME) will be shared between the EMPNO entry.
 
You can also use this possibility on a non-unique key.  In this case, Oracle
adds automatically a new rowid column to the index created.  This rowid column
will then be choosen as the suffix part of the compressed key, and the prefix
key will be only the non-unique key.
 
With the previous table example, you can now create a non-unique compress
Index:
 
    CREATE INDEX COMPRESS_NON_UNIQUE_EMP on EMP (DEPTNO) COMPRESS;
 
In this example, the DEPTNO will be the PREFIX part.
 
The following request will display information on these indexes:
 
    SQL> select index_name, uniqueness, compression from user_indexes;
 
    INDEX_NAME                     UNIQUENES COMPRESS
    ------------------------------ --------- --------
    COMPRESS_NON_UNIQUE_EMP        NONUNIQUE ENABLED
    COMPRESS_UNIQUE_EMP            UNIQUE    ENABLED
    SYS_C001259                    UNIQUE    DISABLED
 
If you compare the content of INDEX_STATS on the COMPRESS_NON_UNIQUE_EMP with
compress or without compress option, you can obtain the following results:
 
    SQL> analyze index COMPRESS_NON_UNIQUE_EMP validate structure;
 
    SQL> select * from index_stats
      2  where name like '%NON_UNIQUE_EMP';
 
with compress option:
 
       HEIGHT    BLOCKS NAME                           PARTITION_NAME                   LF_ROWS
    --------- --------- ------------------------------ ------------------------------ ---------
      LF_BLKS LF_ROWS_LEN LF_BLK_LEN   BR_ROWS   BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS
    --------- ----------- ---------- --------- --------- ----------- ---------- -----------
    DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE  PCT_USED ROWS_PER_KEY
    --------------- ------------- ----------------- ----------- ---------- --------- ------------
    BLKS_GETS_PER_ACCESS  PRE_ROWS PRE_ROWS_LEN
    -------------------- --------- ------------
            1         5 COMPRESS_NON_UNIQUE_EMP                                              39
            1         429       1852         0         0           0          0           0
            0            43                21        1852        429        24    ,90697674
               1,9534884         4           36
 
without compress option:
 
       HEIGHT    BLOCKS NAME                           PARTITION_NAME                   LF_ROWS
    --------- --------- ------------------------------ ------------------------------ ---------
      LF_BLKS LF_ROWS_LEN LF_BLK_LEN   BR_ROWS   BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS
    --------- ----------- ---------- --------- --------- ----------- ---------- -----------
    DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE  PCT_USED ROWS_PER_KEY
    --------------- ------------- ----------------- ----------- ---------- --------- ------------
    BLKS_GETS_PER_ACCESS  PRE_ROWS PRE_ROWS_LEN
    -------------------- --------- ------------
            1         5 NON_UNIQUE_EMP                                                       39
            1         546       1856         0         0           0          0           0
            0             4                21        1856        546        30         9,75
            6,375         0            0
 
 
You can see that the USED_SPACE in compress mode is 6% less than without
compress mode.  Furthermore, you can see the number of prefixed rows
(PRE_ROWS), and the size used by prefix rows (PRE_ROWS_LEN) are also displayed.
 
You can revert the compress option with:
 
    SQL> ALTER INDEX NON_UNIQUE_EMP REBUILD NOCOMPRESS;
 
The compress option may also be used on Index Only Table.
 
    CREATE TABLE EMP_IOT
    (EMPNO NUMBER UNIQUE,
     ENAME VARCHAR2(10),
     JOB VARCHAR2(9),
     MGR NUMBER(4),
     HIREDATE DATE,
     SAL NUMBER(7,2),
     COMM NUMBER(7,2),
     DEPTNO NUMBER,
     PRIMARY KEY (ENAME, EMPNO))
    ORGANIZATION INDEX COMPRESS;
 
By default, Oracle will choose the first column, ENAME, for the prefix part.
 
    INDEX_NAME                     INDEX_TYPE                  COMPRESS PREFIX_LENGTH
    ------------------------------ --------------------------- -------- -------------
    SYS_IOT_TOP_12299              IOT - TOP                   ENABLED              1
 
 
You can also uncompress the IOT created previously:
 
    SQL> ALTER TABLE EMP_IOT MOVE NOCOMPRESS;
 
    INDEX_NAME                     INDEX_TYPE                  COMPRESS PREFIX_LENGTH
    ------------------------------ --------------------------- -------- -------------
    SYS_IOT_TOP_12299              IOT - TOP                   DISABLED
 
 
References
----------
 
Bug:1494342 Fixed in Oracle9i. Analyze of compressed non-unique index gives
              incorrect results on distinct keys
 
 
 
 
RELATED DOCUMENTS
-----------------
Oracle® Database Concepts 10g Release 2 (10.2) Part Number B14220-02
Chapter 5 Schema Objects
Key Compression
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#CNCPT811
 
Oracle9i Database Concepts Release 2 (9.2) Part Number A96524-01
Chapter 10 Schema Objects
Key Compression
http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#4602
 
Oracle8i Concepts Release 2 (8.1.6) Part Number A76965-01
Chapter 10 Schema Objects
Key Compression
http://download.oracle.com/docs/cd/A87860_01/doc/index.htm

 

Related


Products


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition

Keywords


IOT

 

Back to top

Rate this document 

Article Rating

Rate this document
Excellent
Good
Poor
 
Did this document help you?
Yes
No
Just browsing
 
How easy was it to find this document?
Very easy
Somewhat easy
Not easy

 

  Comments

 
Cancel    

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.