Reference to Oracle Advanced Compression: http://www.oracle.com/us/products/database/db-advanced-compression-option-1525064.pdf
I.) Environment Setup and Compression Test:
1.) Table COMPRESSION_TEST to be used for testing. The size of the table is:
00:42:47 SQL> select segment_name,bytes/1024/1024/1024 "Size in GB" from dba_segments where owner='APUN' and segment_name='COMPRESSION_TEST';
SEGMENT_NAME Size in GB
----------------------------------------------------------------------
COMPRESSION_TEST 37.208984375
This is a typical data warehousing aggregate table. It has both varchar2 and number columns:
select COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns where table_name='COMPRESSION_TEST'
COLUMN_NAME DATA_TYPE DATA_LENGTH
------------------------------ ---------- ------------
ID1 NUMBER 22
ID2 NUMBER 22
ID3 NUMBER 22
ID4 NUMBER 22
ID5 NUMBER 22
ID6 NUMBER 22
ID7 NUMBER 22
ID8 NUMBER 22
ID9 NUMBER 22
ID10 VARCHAR2 1
ID11 NUMBER 22
ID12 NUMBER 22
CODE1 VARCHAR2 1024
CODE2 VARCHAR2 1024
CODE3 VARCHAR2 1024
CODE4 VARCHAR2 1024
ID13 NUMBER 22
ID14 NUMBER 22
ID15 NUMBER 22
ID16 NUMBER 22
ID17 NUMBER 22
DETAILS VARCHAR2 4000
2.) I create a table with BASIC compression:
15:05:27 SQL> create table COMPRESSION_TEST_BASIC tablespace USERS compress as select * from COMPRESSION_TEST;
Table created.
Table Size after Compression:
16:27:19 SQL> select segment_name,bytes/1024/1024/1024 from user_segments where segment_name='COMPRESSION_TEST_BASIC';
SEGMENT_NAME BYTES/1024/1024/1024
-------------------------------------------------- --------------------
COMPRESSION_TEST_BASIC 9.724609375
3.) Now I am creating a table with "Compress for OLTP":
16:28:48 SQL> create table COMPRESSION_TEST_OLTP tablespace USERS compress for oltp as select * from COMPRESSION_TEST;
Table created.
Table Size after compression:
16:51:12 SQL> 16:51:12 SQL> select segment_name,bytes/1024/1024/1024 from user_segments where segment_name='COMPRESSION_TEST_OLTP';
SEGMENT_NAME BYTES/1024/1024/1024
-------------------------------------------------- --------------------
COMPRESSION_TEST_OLTP 10.895507813
II.) Deciding to Compress an existing tables or not
We would like to evaluate the amount of compression we would get before we decide to compress a table. We can do a "create table as select" as above, but this would not be feasible for very large table.
Oracle provides a handy procedure to estimate the compression we would get on an existing table. The procedure is called DBMS_COMPRESSION.GET_COMPRESSION_RATIO. DBMS_COMPRESSION.GET_COMPRESSION_RATIO cannot be used to find the ratio for BASIC compression.
Note: Please create a tablespace which you would use for this testing, as this procedure can use a lot of space for getting the compression ratio. We would not want to use our application or users tablespace as it would be difficult to retrieve this space back. You can create a tablespace named SCRAP and then drop it after the evaluation is done.
I have create a small wrapper procedure in SYS schema for this:
CREATE OR REPLACE PROCEDURE CHECK_COMPRESSION
(OWNNAME IN VARCHAR2,
TABNAME IN VARCHAR2,
TBSPNAME IN VARCHAR2,
COMTYPE IN NUMBER)
IS
COMPRESSION_RATIO NUMBER;
COMPRESSION_TYPE VARCHAR2(500);
BLKCNT_COMPRESSION NUMBER;
BLKCNT_NOCOMPRESSION NUMBER;
ROWS_COMPRESSED NUMBER;
ROWS_NOCOMPRESSED NUMBER;
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(
SCRATCHTBSNAME => TBSPNAME,
OWNNAME => OWNNAME,
TABNAME => TABNAME,
PARTNAME =>NULL, -- PARTITION NAME
COMPTYPE => COMTYPE,
BLKCNT_CMP =>BLKCNT_COMPRESSION, -- NUMBER OF BLOCKS COMPRESSED
BLKCNT_UNCMP =>BLKCNT_NOCOMPRESSION, -- NUMBER OF BLOCKS NON COMPRESSED
ROW_CMP =>ROWS_COMPRESSED, -- NUMBER OF ROWS COMPRESSED
ROW_UNCMP =>ROWS_NOCOMPRESSED, -- NUMBER OF ROWS NON COMPRESSED
CMP_RATIO =>COMPRESSION_RATIO, -- COMPRESSION RATIO
COMPTYPE_STR =>COMPRESSION_TYPE); -- COMPRESSION TYPE
DBMS_OUTPUT.PUT_LINE('COMPRESSION RATIO :'||COMPRESSION_RATIO);
DBMS_OUTPUT.PUT_LINE('COMPRESSION TYPE :'||COMPRESSION_TYPE);
DBMS_OUTPUT.PUT_LINE('BLOCKS COMPRESSED :'||BLKCNT_COMPRESSION);
DBMS_OUTPUT.PUT_LINE('BLOCKSUNCOMPRESSED:'||BLKCNT_NOCOMPRESSION);
DBMS_OUTPUT.PUT_LINE('ROWS COMPRESSED :'||ROWS_COMPRESSED);
DBMS_OUTPUT.PUT_LINE('ROWS UNCOMPRESSED:'||ROWS_NOCOMPRESSED);
END;
/
Usage of the above procedure is:
exec apun.check_compression('TABLE_OWNER','TABLE_NAME','TABLESPACE_NAME',Type of Compression to Test);
Value/Option of Type of Compression are:
TYPE: NO COMPRESSION
VALUE: 1
TYPE: COMPRESSION FOR OLTP
VALUE: 2
Other details can be found in the documentation at: http://docs.oracle.com/cd/E14072_01/appdev.112/e10577/d_compress.htm
17:30:08 SQL> exec check_compression('APUN','COMPRESSION_TEST','SCRAP',2);
COMPRESSION RATIO :3.4
COMPRESSION TYPE :"Compress For OLTP"
BLOCKS COMPRESSED :2745
BLOCKSUNCOMPRESSED:9344
ROWS COMPRESSED :304
ROWS UNCOMPRESSED:106
PL/SQL procedure successfully completed.
As per the output it suggests that the compression ratio is 3.4
Our uncompressed table size was: 37.2 GB
Our compressed for OLTP size was: 10.8
So if we divide 37.2 by 3.4 we get 10.9 GB, which is very close to what we achieved when we actually compressed it. DBMS_COMPRESSION.GET_COMPRESSION_RATIO output is quite reliable :).
Compression can be very useful in large databases. It save disk space as well can improve performance, as oracle has to load less block in the SGA.
