PT - Oracle Compression Basic and advanced table example

 PT - Oracle Compression Basic and advanced

• Query to check table compression 

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES;

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

ADDRESSES         DISABLED

PRODUCT_INFORMATI DISABLED

ON

LOGON             DISABLED

ORDERS            DISABLED

CARD_DETAILS      DISABLED

INVENTORIES       DISABLED

WAREHOUSES        DISABLED

JOBS              DISABLED

CUSTOMERS         DISABLED

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

PRODUCT_DESCRIPTI DISABLED

ONS

ORDER_ITEMS       DISABLED

V                 DISABLED

JOB_HISTORY       DISABLED

EMP               DISABLED

DEPT              DISABLED

ORDERENTRY_METADA DISABLED

TA

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE       DISABLED

CUST_BCOMPRESSED  ENABLED  BASIC

CUST_ACOMPRESSED  ENABLED  ADVANCED

19 rows selected.

SQL>

• Create table with no compression basic and advanced.

No compression : CUST_SOURCE

Basic Compression : CUST_BCOMPRESSED

Advanced Compression : CUST_ACOMPRESSED

SQL> conn soe/soe

Connected.

SQL> CREATE TABLE CUST_SOURCE

  2  ( CUSTOMER_NO NUMBER(6),

  3  FIRST_NAME VARCHAR2(20),

  4  LAST_NAME VARCHAR2(20),

  5  NOTE1 VARCHAR2(100),

  6  NOTE2 VARCHAR2(100)

  7  ) NOLOGGING PCTFREE 0 TABLESPACE SOETBS;

Table created.

SQL> CREATE TABLE CUST_BCOMPRESSED

  2  ( CUSTOMER_NO NUMBER(6),

  3  FIRST_NAME VARCHAR2(20),

  4  LAST_NAME VARCHAR2(20),

  5  NOTE1 VARCHAR2(100),

  6  NOTE2 VARCHAR2(100)

  7  ) ROW STORE COMPRESS BASIC NOLOGGING TABLESPACE SOETBS;

Table created.

SQL> CREATE TABLE CUST_ACOMPRESSED

  2  ( CUSTOMER_NO NUMBER(6),

  3  FIRST_NAME VARCHAR2(20),

  4  LAST_NAME VARCHAR2(20),

  5  NOTE1 VARCHAR2(100),

  6  NOTE2 VARCHAR2(100)

  7  ) ROW STORE COMPRESS ADVANCED NOLOGGING PCTFREE 0 TABLESPACE SOETBS;

Table created.

• Insert value on all tables.

SQL> INSERT INTO CUST_SOURCE

  2  SELECT LEVEL AS CUSTOMER_NO,

  3  DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) FIRST_NAME,

  4  DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) LAST_NAME,

  5  DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE1,

  6  DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE2

  7  FROM DUAL

  8  CONNECT BY LEVEL <= 100000;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO CUST_BCOMPRESSED SELECT * FROM CUST_SOURCE;

100000 rows created.

SQL> COMMIT;

Commit complete.

• Gather stats on the all tables.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_SOURCE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_BCOMPRESSED');

PL/SQL procedure successfully completed.

SQL>

• Check block size compression on the all tables

SQL> col TABLE_NAME format a17

SQL> SELECT TABLE_NAME, BLOCKS*8 SIZE_KB, COMPRESSION, PCT_FREE

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME IN ('CUST_SOURCE','CUST_BCOMPRESSED','CUST_ACOMPRESSED');

TABLE_NAME           SIZE_KB COMPRESS   PCT_FREE

----------------- ---------- -------- ----------

CUST_ACOMPRESSED             ENABLED           0

CUST_BCOMPRESSED       15056 ENABLED           0

CUST_SOURCE            15056 DISABLED          0

• Truncate and insert value on compressed table using direct path loading hints . As compare to basic compression there is small difference in advanced. check the status below.

SQL> TRUNCATE TABLE CUST_ACOMPRESSED;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO CUST_ACOMPRESSED SELECT * FROM CUST_SOURCE;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_ACOMPRESSED');

PL/SQL procedure successfully completed.

SQL> col TABLE_NAME format a17

SQL> SELECT TABLE_NAME, BLOCKS*8 SIZE_KB, COMPRESSION, PCT_FREE

  2  FROM USER_TABLES

  3  WHERE TABLE_NAME IN ('CUST_SOURCE','CUST_BCOMPRESSED','CUST_ACOMPRESSED');

TABLE_NAME           SIZE_KB COMPRESS   PCT_FREE

----------------- ---------- -------- ----------

CUST_ACOMPRESSED        4360 ENABLED           0

CUST_BCOMPRESSED        4360 ENABLED           0

CUST_SOURCE             5984 DISABLED          0

• Create Basic level of compression from no compressed table

SQL> CREATE TABLE CUST_BCOMPRESSED1 ROW STORE COMPRESS BASIC as select * from CUST_SOURCE ;

Table created.

• Change existing table to compression

SQL> ALTER TABLE CUST_SOURCE ROW STORE COMPRESS BASIC;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE       ENABLED  BASIC

• Change existing table from basic to compression

SQL> ALTER TABLE CUST_SOURCE ROW STORE COMPRESS advanced;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE       ENABLED  ADVANCED

SQL>

SQL> ALTER TABLE CUST_SOURCE noCOMPRESS;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME        COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE       DISABLED

• Create tablespace with advanced level of compression

SQL> create tablespace adv_comp datafile 'C:\APP\VARUNYADAV\ORADATA\ORCL2\advcomp01.DMP' size 10m DEFAULT ROW STORE COMPRESS ADVANCED;

Tablespace created.

• Determining Which Rows Are Compressed

SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(OWNNAME => 'SOE',TABNAME => 'SALES_HISTORY',SUBOBJNAME => '',ROW_ID => 'AAAKEIEEGBBADBTDEDD'),

1, 'No Compression',

2, 'Advanced Row Compression',

4, 'Hybrid Columnar Compression for Query High',

8, 'Hybrid Columnar Compression for Query Low',

16, 'Hybrid Columnar Compression for Archive High',

32, 'Hybrid Columnar Compression for Archive Low',

4096, 'Basic Table Compression',

'Unknown Compression Type') COMPRESSION_TYPE

FROM DUAL;

• Compression Related Statistics

SQL> SELECT N.NAME, S.VALUE FROM V$MYSTAT S, V$STATNAME N WHERE S.STATISTIC# = N.STATISTIC# AND N.NAME LIKE '%HSC%';

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

HSC OLTP Space Saving                                               1177689

HSC OLTP Compressed Blocks                                              510

HSC IDL Compressed Blocks                                              3379

HSC Compressed Segment Block Changes                                   6691

HSC Heap Segment Block Changes                                        11782

HSC OLTP Non Compressible Blocks                                        508

HSC OLTP positive compression                                          1018

HSC OLTP negative compression                                             0

HSC OLTP recursive compression                                            0

HSC OLTP inline compression                                            1018

HSC OLTP partial compression                                              0

NAME                                                                  VALUE

---------------------------------------------------------------- ----------

HSC OLTP Drop Column                                                      0

HSC OLTP Compression skipped rows                                         0

HSC OLTP compression block checked                                        0

HSC OLTP Compression wide compressed row pieces                           0

15 rows selected.