Tuesday, June 27, 2006

Table Compression in Oracle 9iR2 and beyond

Starting with Oracle database 9iR2 (Enterprise Edition!)
it is possible to create compressed heap organized tables and compressed table partitions as well as compressed materialized views.

This feature is very interesting for data warehouses because it can save a lot of disk space.

Compression has already been availble for b-tree indexes and index organized tables
since Oracle 8.1.5.

Here is an example:

LUTZ @10gR2 SQL > CREATE TABLE compress1 (col1 NUMBER, col2 VARCHAR2(30)) COMPRESS;
Table created.

LUTZ @10gR2 SQL > CREATE TABLE not_compress1
(col1 NUMBER,
col2 VARCHAR2(30));
Table created.

LUTZ @10gR2 SQL > SELECT table_name, compression
FROM user_tables;

TABLE_NAME COMPRESS
------------------------------ --------
NOT_COMPRESS1 DISABLED
COMPRESS1 ENABLED


LUTZ @10gR2 SQL > BEGIN FOR i IN 1..100 LOOP
INSERT INTO not_compress1 VALUES(i, 'value' || i);
END LOOP;
FOR i IN 1..14 LOOP
INSERT INTO not_compress1 SELECT * FROM not_compress1;
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.

LUTZ @10gR2 SQL > SELECT COUNT(*) FROM not_compress1;
COUNT(*)
----------
819200

LUTZ @10gR2 SQL > SELECT COUNT(DISTINCT col2) FROM not_compress1;
COUNT(DISTINCTCOL2)
-------------------
100

LUTZ @10gR2 SQL > INSERT /*+ APPEND*/
INTO compress1
SELECT *
FROM not_compress1;
819200 rows created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
('NOT_COMPRESS1',
'COMPRESS1');

EXTENTS BYTES SEGMENT_NAME
---------- ------- -------------------

27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1

Serial INSERT with append hint, as well as Parallel INSERT (with parallel hint) and also
Direct path insert with SQL*Loader into segments with compression enabled cause compression.
Inserting into a compressed table by using an ORDER BY CLAUSE for the column with lowest cardinality (least distinct values) incerases the compression rate!

With the COMPRESS clause is also possible to
create a table with a subselect as a compressed segment:


SYSTEM @10gR2 SQL > CREATE TABLE compress2 COMPRESS
AS SELECT * FROM lutz.not_compress1;
Table created.

SYSTEM @10gR2 SQL > CREATE TABLE to_be_compressed AS
SELECT * FROM lutz.compress1;
Table created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
('NOT_COMPRESS1',
'COMPRESS1',
'COMPRESS2',
'TO_BE_COMPRESSED');

EXTENTS BYTES SEGMENT_NAME
----------- ----------- ------------------------------

27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1
30 15728640 TO_BE_COMPRESSED
27 12582912 COMPRESS2


It is also possibel to create a tablespace with compression as default:

SYSTEM @10gR2 SQL > CREATE TABLESPACE lutz_compress
DATAFILE '/u01/app/oracle/oradata/orcl/lutz_compress.dbf'
SIZE 10m AUTOEXTEND ON
DEFAULT COMPRESS;
Tablespace created.

SYSTEM @10gR2 SQL > CREATE TABLE lutz.not_compress2
TABLESPACE lutz_compress
AS SELECT * FROM lutz.not_compress1;
Table created.

SYSTEM @10gR2 SQL > SELECT extents,bytes, segment_name
FROM dba_segments
WHERE segment_name IN
('NOT_COMPRESS1',
'COMPRESS1',
'COMPRESS2',
'TO_BE_COMPRESSED',
'NOT_COMPRESS2');

EXTENTS BYTES SEGMENT_NAME
------------- ---------------- -----------------------------
27 12582912 COMPRESS1
30 15728640 NOT_COMPRESS1
30 15728640 TO_BE_COMPRESSED
27 12582912 COMPRESS2
27 12582912 NOT_COMPRESS2

6 Comments:

At Wed Aug 30, 12:16:00 AM, Blogger Vidya Balasubramanian said...

with a tablespace with compression as default - would that mean that all new tables I create in that tablespaces will default to compressed?
are there performance advantaged/disadvantages with having tables compressed.?

 
At Wed Aug 30, 12:17:00 AM, Blogger Vidya Balasubramanian said...

with a tablespace with compression as default - would that mean that all new tables I create in that tablespaces will default to compressed?
are there performance advantaged/disadvantages with having tables compressed.?

 
At Wed Aug 30, 12:17:00 AM, Blogger Vidya Balasubramanian said...

with a tablespace with compression as default - would that mean that all new tables I create in that tablespaces will default to compressed?
are there performance advantaged/disadvantages with having tables compressed.?

 
At Wed Aug 30, 08:16:00 AM, Blogger lutz_hartmann said...

Hi Vidya,
yes, the segemtns in such a tablespace would be compressed by default then unless you specify NOCOMPRESS wioth the CREATE TABLE ¦ INDEX.
Segment compression is usefull for large databases like DWH because it refuces space usage on disk and also memory usage, especially the db buffer cache!!
This can lead to better response times for queries.
It is not advisable to use compression where a lot of DELETEs and UPDATEs are going on, which normally is not the case in a DWH.
Segment compression is optimized for direct loading.
This occurs when:
- SQL*LOADER => direct=y
- CREATE TABLE ... AS SELECT ...
- Parallel INSERT
- Serial INSERT with APPEND hint

Here is a little demo for the tablespace level usage:

SYSTEM @10gR2 SQL > CREATE TABLESPACE my_compressed DATAFILE SIZE 20m DEFAULT COMPRESS;

Tablespace created.


SYSTEM @10gR2 SQL > select TABLESPACE_NAME, STATUS, LOGGING, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT, DEF_TAB_COMPRESSION
FROM dba_tablespaces

TABLESPACE_NAME STATUS LOGGING EXTENT_MAN SEGMEN DEF_TAB_
------------------------------ --------- --------- ---------- ------ --------
SYSTEM ONLINE LOGGING LOCAL MANUAL DISABLED
UNDOTBS1 ONLINE LOGGING LOCAL MANUAL DISABLED
SYSAUX ONLINE LOGGING LOCAL AUTO DISABLED
TEMP ONLINE NOLOGGING LOCAL MANUAL DISABLED
USERS ONLINE LOGGING LOCAL AUTO DISABLED
EXAMPLE ONLINE NOLOGGING LOCAL AUTO DISABLED
MY_COMPRESSED ONLINE LOGGING LOCAL AUTO ENABLED

SYSTEM @10gR2 SQL > create table compress_default (col1 number, col2 varchar2(15)) TABLESPACE MY_COMPRESSED;

Table created.

SYSTEM @10gR2 SQL > DESC dba_tables
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
DROPPED VARCHAR2(3)

SYSTEM @10gR2 SQL > select TABLE_NAME, TABLESPACE_NAME, COMPRESSION
2 FROM dba_tables
3 WHERE owner='SYSTEM'
4 AND TABLE_NAME='COMPRESS_DEFAULT';

TABLE_NAME TABLESPACE_NAME COMPRESS
------------------------------ ------------------------------ --------
COMPRESS_DEFAULT MY_COMPRESSED ENABLED


=;-)
LUTZ

 
At Wed Aug 30, 08:20:00 AM, Blogger lutz_hartmann said...

Hi Vidya, why did you send your comment 4 times?
=8-)

 
At Wed Aug 30, 05:21:00 PM, Blogger Vidya Balasubramanian said...

Thank you for the response - I wonder why the response went out 4 times - will probably have to make a note of it the next time I publish my comments

 

Post a Comment

<< Home