Skip to content
Keep Learning Keep Living
Keep Learning Keep Living
Keep Learning Keep Living

Oracle Table Compression Part1

Mustafa, 2020-09-10

Hi,

I have been dealing with table compression a lot during my development period and I still do. I wanted to share some information about table information but I believe this will be a long explanation so I will write them in parts and this is part1.

Table compression is a very useful feature of Enterprise Edition (unfortunately cannot be used in Standard Edition). Table compression will compress your data and it will reduce space requirement for the table. Not just on disk also in memory. When you compress a table, Oracle will store it compressed always so it will consume less memory space and I think this is very important for large databases with relatively small memory amounts.

Of course compression has a disadvantage and that’s why it is not default option for a table. as you can guess it has a “cpu overhead” which means your cpu will be doing some extra job about compressing and decompressing. Many times this overhead could be acceptable because you will have extra disk and memory space. Do not think this as just a space gain. while reading a data from disk (and loading into memory) you will read much more less data from disk. this will significantly increase your query performance because of less disk IO.

Basically there are 3 compression algorithm for tables:

  1. Basic
  2. Advanced (if I am not wrong, it was called “FOR OLTP” compression at earlier relases)
  3. Hybrid Columnar (or Column Store)

there are important things about those compression methods. Most important one is ADVANCED compression requires “Advanced Compression License” so it will cost you a lot! you can find detail of advanced compression license detail from this link: https://www.oracle.com/technetwork/database/options/compression/advanced-compression-datasheet-134474.pdf

Other two, Basic and Hybrid compression does not require a license but Hybrid Columnar Compression (also called as HCC or Column Store compression) requires special storage systems. your storage must be support HCC.

Basic Compression

you can compress a table with an Alter Table command or create table with a compression option. there are few important points. I’ve seen some clients that “mark” table as a compressed table but they don’t know that previous rows are not compressed! here is an example:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
create table tmp as
  select * from dba_objects;
 
Table created.
 
select bytes/1024/1024 Table_Size_MB from dba_SEgments where segment_name ='TMP';
 
TABLE_SIZE_MB
-------------
           12
 
alter table tmp compress;
 
Table altered.
 
 
select bytes/1024/1024 Table_Size_MB from dba_SEgments where segment_name ='TMP';
 
TABLE_SIZE_MB
-------------
           12
 
select compression, compress_For from all_Tables where table_name ='TMP';
 
COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  BASIC
 
select dbms_compression.get_compression_type(user, 'TMP', rowid) from tmp where rownum = 1;
 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE(USER,'TMP',ROWID)
-------------------------------------------------------
                                                      1

I created a dummy table and “mark” it as “compress” but size didn’t change and rows are not compressed! I know that because DBMS_Compression.get_compression_type returned 1 for a random row. 1 means no compression.

marking a table as “compress” means “consequent rows” will be compressed but not the existing ones. I used ROW STORE COMPRESS BASIC method above (or just “compress”) and only some specific operations can be compressed at that operation. This is the most basic compression but it has a HIGH compression ratio!

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
insert /*+ append */ into tmp select * from dba_objects;
 
72978 rows created.
 
select bytes/1024/1024 Table_Size_MB from dba_SEgments where segment_name ='TMP';
 
TABLE_SIZE_MB
-------------
           16

I just bulk inserted same rows (basically I doubled the number of rows) but table size increased only 4 MB not 12 MB.

So, if you want to compress existing rows, you must MOVE the table:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
alter table tmp move;
 
Table altered.
 
select bytes/1024/1024 Table_Size_MB from dba_SEgments where segment_name ='TMP';
 
TABLE_SIZE_MB
-------------
            7

here it is, previous rows are also compressed. you can combine these operations with: ALTER TABLE TMP MOVE COMPRESS; it will compress currently existing rows. of course these operations will be invalidate indexes! you can use “update indexes” clause but it will increase compression time. I will talk about this at my later posts.

you can use different syntax:

Alter table tmp compress;
Alter table tmp row store compress;
Alter table tmp row store compress basic;

all commands above are the same. compress table with BASIC method. BASIC compression will compress new rows only if data is coming as BULK insert or an ARRAY insert. row by row inserts (insert into values (…) ) for example won’t be compressed. I mostly use this compression for previous months data which are not frequently accessed and when accessed, only queried not any DML operation because update also won’t be compressed.

Advanced Compression

Don’t let the naming mislead you. it is not more “advanced” than BASIC compression. actually it almost has the same compression level (HIGH) with basic compression but Advanced Table compression has some advantages. Most important one is it can be used for OLTP systems and by saying that can be used for active data not previous month data etc. performance overhead for currently DML operations are less than BASIC compression and it can even compress row by row inserts.

compression will not be done after a row processed (insert or update). when Block has reached to a threshold, Oracle will compress that block at the background. so your operations will run at fastest speed and even newly inserted / updated rows will be compressed. BUT it requires advanced compression license as I said before.

Hybrid Columnar Compression

this is the king of the compression’s. it will compress data much much much much more than first two and as you can guess it has a disadvantage. HCC is much more cpu intensive compression. it requires cpu usage far more than basic and advanced compression. Mostly used for less accessed rows. it has 2 methods which have 2 sub methods:

  1. Query Low (Lowest Compression Ratio, Lowest Cpu Usage)
  2. Query High
  3. Archive Low
  4. Archive High (Highest Compression Ratio, Highest Cpu Usage)

from 1 to 4, compression ratio increased but also cpu usage.

Basically, it uses a different logic in compression method. first two compression, compresses rows in the same blocks. writes same data once and uses a reference for actual places. Column store compression compresses columns not rows in a block and it is more logical because repetitive data mostly exists in same column, for example gender or customer id or product code etc.

So, at this post I wanted to share a real life example with compression ratios and speeds. of course I can not provide main data in here but I used a table with real data and copied it then compress the same table with different methods. Here is the results:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Create Table Tmp As select * from some_table;
 
Declare
  x_start timestamp;
 
  Procedure LP_Table_Size As
    x_size number;
  Begin
    Select bytes/1024/1024 into x_size
    from   dba_segments
    where  segment_name ='TMP';
    
    dbms_output.put_line('New Table Size: ' || To_Char(x_size));
  End;
 
  Procedure LP_Compress(P_Compress_Method Varchar2) as
  Begin
    X_Start := Systimestamp;
    Execute immediate 'Alter table tmp move ' || P_Compress_Method;
    Dbms_output.put_line('Compression Method: ' ||P_Compress_Method);
    Dbms_output.put_line('Compression Elapsed Time: ' ||(systimestamp-x_start));
    LP_Table_Size;
    Dbms_output.put_line('***************************************');
  End;
  
Begin
  LP_Compress('NOCOMPRESS');
  LP_Compress('ROW STORE COMPRESS BASIC');
  LP_Compress('ROW STORE COMPRESS ADVANCED');
  LP_Compress('COLUMN STORE COMPRESS FOR QUERY LOW');
  LP_Compress('COLUMN STORE COMPRESS FOR QUERY HIGH');
  LP_Compress('COLUMN STORE COMPRESS FOR ARCHIVE LOW');
  LP_Compress('COLUMN STORE COMPRESS FOR ARCHIVE HIGH');
  
End;
/

this small code, will compress table TMP with different methods and reports about timing and size. first one is NOCOMPRESS so we will see size of table without compression and then other methods results:

Oracle PL/SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Compression Method: NOCOMPRESS
Compression Elapsed Time: +000000000 00:00:07.644334000
New Table Size: 1408
***************************************
Compression Method: ROW STORE COMPRESS BASIC
Compression Elapsed Time: +000000000 00:00:37.552691000
New Table Size: 280
***************************************
Compression Method: ROW STORE COMPRESS ADVANCED
Compression Elapsed Time: +000000000 00:00:31.412431000
New Table Size: 280
***************************************
Compression Method: COLUMN STORE COMPRESS FOR QUERY LOW
Compression Elapsed Time: +000000000 00:00:20.815194000
New Table Size: 176
***************************************
Compression Method: COLUMN STORE COMPRESS FOR QUERY HIGH
Compression Elapsed Time: +000000000 00:00:41.371027000
New Table Size: 120
***************************************
Compression Method: COLUMN STORE COMPRESS FOR ARCHIVE LOW
Compression Elapsed Time: +000000000 00:01:05.717895000
New Table Size: 104
***************************************
Compression Method: COLUMN STORE COMPRESS FOR ARCHIVE HIGH
Compression Elapsed Time: +000000000 00:02:02.979353000
New Table Size: 72
***************************************

without compression this table’s size is 1408 MB. as you see BASIC and ADVANCED compression reduce the size to 280 MB but it takes 37-31 seconds to compress.

interestingly, Hybrid compression (column store) “query low” takes less than others, just 20 seconds and size reduced to 176 MB almost half of first two. You can also see that size is decreasing from “Query Low” to “Query High”. king is Query High, table size is 72 MB. 20x compression is very impressive but also as you can see, it takes 2 minutes to compress. Also, querying those compression methods will take significantly amount of time.

Compression is very useful if you use it accordingly. This is just a demonstration about compression level and basics of compression. I will mentioned about query performance of compression, CLOB compression, CLOB vs HCC compression etc at my later posts too.

that’s all for now. wish you all a health day.

11g 12c 18c 19c Administration Compression Development Performance advanced compressionbasic compressioncolumn storecompressioncompression elapsed timecompression methodscompression ratioHCChybrid columnarhybrid columnar compressionhybrid compressionoracletable compression

Post navigation

Previous post
Next post

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • DDL Generation Failed with ORA-65047: Object SYS.KUPUTIL is invalid
  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval

Recent Comments

  • Mustafa on How to call HTTPS Url Without SSL Wallet in 19c
  • Накрутка авито on How to call HTTPS Url Without SSL Wallet in 19c
  • Mustafa on Cloud Base Database Service
  • Raja on Cloud Base Database Service
  • Mustafa on Refreshable PDB and ORA-17627: ORA-12578: TNS:wallet open failed

Categories

  • 11g
  • 12c
  • 18c
  • 19c
  • 21c
  • 23ai
  • Administration
  • Cloud
  • Compression
  • Development
  • Materialized View
  • Multi-tenant
  • Performance
  • Security
  • SQL / PLSQL
  • Uncategorized
  • Undocumented
  • Useful Scripts

Archives

  • June 2025
  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS Follow This Blog

  • DDL Generation Failed with ORA-65047: Object SYS.KUPUTIL is invalid
  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences

Archives

  • June 2025
  • April 2025
  • November 2024
  • July 2024
  • April 2024
  • February 2024
  • January 2024
  • December 2023
  • November 2023
  • October 2023
  • September 2023
  • August 2023
  • May 2023
  • April 2023
  • March 2023
  • February 2023
  • January 2023
  • November 2022
  • October 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • October 2021
  • September 2021
  • August 2021
  • April 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • February 2020
  • January 2020
  • December 2019
  • July 2019
  • June 2019
  • May 2019
  • March 2019
  • February 2019
  • June 2018

RSS Follow This Blog

  • DDL Generation Failed with ORA-65047: Object SYS.KUPUTIL is invalid
  • How to call HTTPS Url Without SSL Wallet in 19c
  • Is Table Unnecessary for Pipelined Function
  • Password Rollover Time in Seconds
  • PDB Syncronization Issue
  • How to limit DB Link Connection Timeout
  • Cloud Base Database Service
  • 29th of February and interval
  • How to Copy Local Files to Oracle Object Storage
  • Guid vs Sequences
RSS Error: A feed could not be found at `http://www.mywebsite.com/feed/`; the status code is `200` and content-type is `text/html; charset=UTF-8`
©2025 Keep Learning Keep Living | WordPress Theme by SuperbThemes