Advanced Row Compression in Oracle

Back to: Oracle DBA Tutorials

Advanced Row Compression in Oracle with Examples

In this article, I will explain Advanced Row Compression in Oracle with Examples. Please read our previous article discussing Basic Compression in Oracle. Until now, we have seen the basic compression, but here we will be looking at the advanced row compression in Oracle.

Row Compression in Oracle

Rows are inserted with or without using the direct-path insert, and updated rows are compressed using advanced row compression. This is enabled with CREATE TABLE ROW store compress advanced. It is recommended for active OLTP environments. We do this row-level compression in the OLTP databases. With advanced row compression, duplicate values in the rows and columns in a data block are stored once at the beginning of the block.

Note:

• Row store compress advanced and compress basic are not supported for tables with more than 255.
• We cannot drop a column from a table that is compressed for direct-load operations, although you can set such a column as unused.

There are two cases in this Row level compression.

Case 1:

Let’s create a table from the select statement of the table dba_objects.

create table test_06
as
select * from dba_objects where rownum <= 20000;

As the table is created. Let’s try to analyze the table with computed statistics.

Statement: ANALYZE TABLE test_06 COMPUTE statistics;

So, the table TEST_06 is analyzed. Let’s check the blocks allocated for the table TEST_06.

select blocks, pct_free, compression, compress_for from user_tables where table_name = ‘TEST_06’;

So, the blocks allocated for table TEST_06 is 368, and the PCT_FREE is 10. The compression is disabled.

Case 2: Create a table with row store compress advanced.

Let’s create a table with row store compress advanced.

create table test_07 ROW STORE COMPRESS ADVANCED
as
select * from dba_objects where rownum <= 20000;

So, the table TEST_07 is created. Now, let’s analyze the table with computed statistics.

Statement: ANALYZE TABLE test_07 COMPUTE statistics;

So, the table TEST_07 is analyzed. Let’s check the blocks allocated for the table TEST_07.

select blocks, pct_free, compression, compress_for
from user_tables
where table_name = 'TEST_07';

The blocks allocated for the compression row table are 95. The PCT_FREE is 10%, and compression is enabled. The compression mode is advanced.

Case 3: Try to create a normal insert in the empty table with row compress advanced.

Let’s create an empty table and then insert the data into the table. Previously, when we did the basic compression and inserted the data after the table creation, there were no benefits. Now, let’s look at the compression with an advanced option.

create table test_08 ROW STORE COMPRESS ADVANCED
as
select * from dba_objects where rownum = 0;

We have created an empty table. Let’s check the table details.

Statement: select * from test_08;

So, no data is present inside the table test_08. Let’s try to insert the data into the table test_08.

insert into test_08
select * from dba_objects where rownum <= 20000;
commit;

20000 rows were inserted successfully. Now, let us try to analyze the table test_08.

Statement: ANALYZE TABLE test_08 COMPUTE statistics;

The table is now analyzed. Let’s have a look at the total blocks allocated to the table.

select blocks, pct_free, compression, compress_for from user_tables where table_name = ‘TEST_08’;

So, the total number of blocks allocated was 95, the PCT_FREE was 10, the compression was enabled, and the compression mode was advanced.

So, if we check the blocks allocated for the table TEST_06, it is 368. So, the Row Store compress advanced is actively compressing the blocks in the segments. It is recommended in the OLTP environment.

In the next article, I will discuss Monitoring Tablespace Usage in Oracle with Examples. In this article, I explain Advanced Row Compression in Oracle with Examples. I hope you enjoy this article on Advanced Row Compression in Oracle.