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
Keywords
|
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
|
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
Keywords
|