How to Advance Compression 12c
1. What is Advance Compression:
As you already know in oracle compression started playing major role these days on saving cost and improving performance.
Oracle Advanced Compression option help to improve performance while reducing storage costs. It allows significantly reduce overall database storage footprint by enabling compression for all types of data –relational (table), unstructured (file), index, network and backup data.
Oracle Advanced Compression option are designed to improve performance for all components of your IT infrastructure, including memory, network bandwidth and storage.
2. Benefits of Advanced Row Compression
1. Reduce the storage space consumption by a factor of 2x to 4x by using Advanced Row Compression.
2. 2.5x faster on table scan compare with uncompressed data
3. Significant advantage is to read compressed blocks (data and indexes) directly, in memory, without uncompressing the blocks. This helps improve performance due to the reduction in I/O, and the reduction in system calls related to the I/O operations. Further, the buffer cache becomes more
efficient by storing more data without having to add memory.
3. Minimal Performance Overhead
1. A newly initialized block remains uncompressed until data in the block reaches an internally controlled threshold. When a transaction causes the data in the block to reach this threshold, all contents of the block are compressed. Subsequently, as more data is added to the block and the threshold is again reached, the entire block is recompressed to achieve the highest level of compression. This process repeats until Oracle determines that the block can no longer benefit from further compression. Only the transaction that performs the compression of the block will experience the slight compression overhead – the majority of DML transactions on compressed blocks will have the exact same performance as they would with uncompressed blocks.
2. Less than 3% cpu overhead on compressed data
4. Advanced Row Compression Implementation:
4.1 For new tables and partitions, enabling Advanced Row Compression is easy: simply CREATE the table or partition and specify “ROW
STORE COMPRESS ADVANCED”
Example:
CREATE TABLE emp (emp_id NUMBER, first_name VARCHAR2(128), last_name VARCHAR2(128)) ROW STORE COMPRESS ADVANCED;
4.2 For existing tables and partitions, there are a number of recommended approaches to enabling Advanced Row Compression:
4.2.1. ALTER TABLE … ROW STORE COMPRESS ADVANCED
Enable Advanced Row Compression for all future DML — however, the existing data in
the table will remain uncompressed.
4.2.2. Online Redefinition (DBMS_REDEFINITION)
Enable Advanced Row Compression for future DML and also compress existing data.
DBMS_REDEFINITION keeps the table online for both read/write activity during the migration. Run DBMS_REDEFINITION in parallel for best performance.
There is no interruption in the use of the indexes during, or after.
The only exception is when online redefinition is used for redefining a partition — any global indexes are invalidated and need to be rebuilt
4.2.3. ALTER TABLE … MOVE ROW STORE COMPRESS ADVANCED
Enable Advanced Row Compression for future DML and also compress existing data.
While the table is being moved it is online for read activity but has an exclusive (X)
lock – so all DML will be blocked until the move command completes. Run ALTER
TABLE…MOVE in parallel for best performance.
ALTER TABLE… MOVE will invalidate any indexes on the partition or table; those indexes will
need to be rebuilt after the ALTER TABLE… MOVE.
4.2.4. ALTER TABLE … MOVE TABLE/PARTITION/SUBPARTITION … ONLINE
Enable Advanced Row Compression for future DML and also compress existing data. ALTER TABLE … MOVE TABLE/PARTITION/SUBPARTITION … ONLINE allows
DML operations to continue to run uninterrupted on the table/partition/subpartition that is being
moved. Indexes are maintained during the move operation, so a manual index rebuild is not
required. New in 12.2 on Oracle Cloud, move tables online as well as partitions/subpartitions.
5. Advanced Index Compression
OLTP Databases will have more indexes to support the multitude of access paths for OLTP applications, this can cause indexes to contribute a greater share to overall storage of a database.
New in 12.2 on Oracle Cloud, the “HIGH” level of Advanced Index Compression provides significant space savings while also improving performance for queries that are executed using indexes. High compression offers the following advantages over LOW compression:
Gives higher compression ratios in most cases.
Employs more complex compression algorithms than advanced low compression.
Stores data in a compression unit, which is a special on-disk format.
The following is an example of enabling Advanced Index Compression (high level):
CREATE INDEX hr.emp_mndp_ix ON hr.employees(manager_id, department_id) COMPRESS ADVANCED HIGH;
Restriction on Index Compression:
Bitmap indexes can’t be compressed.
Partitioned indexes can’t be compressed. From 11g you can do compress partition index .
6. Here I tried to implement Index compression using Oracle Lab:
6.1 Create a Table with 6 columns type of varchar
CREATE TABLE test (
ENAME VARCHAR2(75),
EADD1 VARCHAR2(75),
EADD2 VARCHAR2(75),
EADD3 VARCHAR2(75),
EADD4 VARCHAR2(75),
CITY VARCHAR2(75)
);
Table created.
INSERT /*+ APPEND */ INTO test
SELECT RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(‘X’,75, ‘X’),
RPAD(TO_CHAR(level),75, ‘X’)
FROM dual
CONNECT BY level <= 10000;
COMMIT;
10000 rows created.
col owner format a10
col segment_name format a15
col segment_type format a15
select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST TABLE 6291456
6.2 Created Non-Unique Index without compression and checked the size of an Index
SQL> CREATE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY);
Index created.
SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);
PL/SQL procedure successfully completed.
SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 6291456
MUDHALVAN TEST TABLE 6291456
SQL>
6.3 Created Non-Unique Index with compression and checked the size of an Index
SQL> CREATE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS 5;
Index created.
SQL>EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);
PL/SQL procedure successfully completed.
SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456
SQL>
Note : You can see the size of an index is reduced more than 2.5x
6.4 Created Unique Index without compression and checked the size of an Index
SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY);
Index created.
SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);
PL/SQL procedure successfully completed.
SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 6291456
MUDHALVAN TEST TABLE 6291456
SQL>
6.5 Created Unique Index with compression and checked the size of an Index
SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS 5;
Index created.
SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);
PL/SQL procedure successfully completed.
SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456
SQL>
6.6 Created Unique Index with compression advanced and checked the size of an Index
SQL> CREATE UNIQUE INDEX test_idx ON test(ENAME, EADD1, EADD2, EADD3, EADD4, CITY) COMPRESS ADVANCED LOW;
Index created.
SQL> EXEC DBMS_STATS.gather_index_stats(USER, ‘test_idx’);
PL/SQL procedure successfully completed.
SQL> select owner,segment_name,segment_type,bytes from dba_segments where owner=’MUDHALVAN’;
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES
———- ————— ————— ———-
MUDHALVAN TEST_IDX INDEX 2097152
MUDHALVAN TEST TABLE 6291456
SQL>