Oracle Compressed Tables

Compressed tables use fewer data blocks on disk, reducing disk space requirements.

Let see how to implement this.

SQL> CREATE TABLE COMPRESS_DATA AS SELECT * FROM ALL_OBJECTS WHERE 1=0;

SQL> SELECT TABLE_NAME, COMPRESSION, ROW_MOVEMENT FROM USER_TABLES;

TABLE_NAME COMPRESS ROW_MOVE
—————————— ——– ——–
COMPRESS_DATA ENABLED DISABLED

SQL> SET SERVEROUTPUT ON

— A small piece of code to populate the data in table
BEGIN
FOR I IN 1..10
LOOP
INSERT INTO COMPRESS_DATA SELECT * FROM ALL_OBJECTS;
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SUBSTR(SQLERRM,1,100));
END;
/

–Original size of the table

SQL> SELECT BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME = ‘COMPRESS_DATA’;

BYTES
———-
48234496

— Change the table type to compressed

SQL> ALTER TABLE COMPRESS_DATA MOVE COMPRESS;

Table altered.

— Size after table compression

SQL> SELECT BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME = ‘COMPRESS_DATA’;

BYTES
———-
14680064

So we can see that, the size of the table is reduced almost to 1/3 of the original size. So more repetitive values more is the compression ratio.

How this is implemented?

Oracle compress data by eliminating duplicate values within a data-block. Any repetitive occurrence of a value in a block is replaced by a symbol entry in a “symbol table” within the data block. So 1 value + n symbols of that value.

After compressing the table, you need to rebuild indexes because the rowid’s have changed. It will also change the table property “ROW_MOVEMENT” to enabled to facilitate this.

SQL> SELECT TABLE_NAME, COMPRESSION FROM USER_TABLES;

TABLE_NAME COMPRESS
—————————— ——–
COMPRESS_DATA ENABLED

Advertisements
This entry was posted in Oracle Table Handling and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s