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.
