Basic Compression in Oracle

Back to: Oracle DBA Tutorials

Basic Compression in Oracle with Examples

In this article, I will explain Basic Compression in Oracle with Examples. Please read our previous article discussing Space-Saving Features using unusable Indexes in Oracle. There are many types of compression in the Oracle database. To reduce the space of a database object that needs storage. There are many types of compression. Let’s look at the basic compression.

Basic Compression in Oracle:

The Oracle database server compresses data when performing bulk load using operations such as direct loads or CREATE TABLE AS SELECT. This is recommended for bulk-load data warehouses. We have some scenarios for basic compression, mostly under OLTP databases. OLTP databases are mostly used for select statements. They are not used for insert, update, or delete operations. Let’s check the database name and the username that we have connected.

Command: show con_name

Let’s look for the bulk loading and we will be using the below query.

Statement: select * from dba_objects where rownum<10000;

So, we can see there are multiple rows in the output. We have used the table dba_objects because it contains all the details of the database tables. So, we will be using these records for our exercise. We will be looking at many cases and look for the compression details.

Case 1: No Compression.

Let’s create a table with the data from the select * from dba_objects output.

create table test_01
as
select * from dba_objects where rownum <= 10000;

So, the table TEST_01 is created. Now, this table should have 10000 records inside it. Let’s get the DDL for this table TEST_01 from DBMS_METADATA.GET_DDL.

Statement: SELECT DBMS_METADATA.GET_DDL(‘TABLE’,’TEST_01′) FROM dual;

The DDL statement describes the PCTFEE is 10 and NOCOMPRESS mode. Now, let’s try to analyze the table TEST_01.

Statement: ANALYZE TABLE TEST_01 COMPUTE statistics;

This statement will collect the statistics about the table TEST_01. The dictionary will be updated about the table TEST_01. It will calculate the number of rows and number of blocks and it will calculate the accurate details.

Now, let’s check the table details for table TEST_01 using the data dictionary user_tables. We will look at the columns blocks, pct_free, compression, and compression_for.

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

We can see the block count is 186. The PCT_FREE for the blocks is 10%. The compression is DISABLED, and compress_for is null. This means the table is not compressed.

Case 2: Basic Compression Enabled.

In this case we will be doing basic compression of the data inside the table.

create table test_02 compress basic
as
select * from dba_objects where rownum <= 1000;

Here, we have mentioned compress basic. Let’s create the table and see how the data is compressed.

So, the table is created. Note that this compress basic will work only in the situations where we create the table by using select from the existing table. Now, let’s analyze the table.

Statement: ANALYZE TABLE TEST_02 COMPUTE statistics;

As we have analyzed the table, let’s look at the blocks the table used. Let’s check that using the below query.

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

We can see the output from the above query. The blocks that the table used is 43, the compression is enabled for the table, and the compression mode is basic. The PCT_FREE for the data blocks is 0.

Case 3: Insert into the table where the table uses compress basic.

In this case, we use the insert statement into the table, which is created with Compress basic mode.

create table test_03 compress basic
as
select * from dba_objects where 1=2;

This statement indicates that we will be created with no rows because the select statement where condition 1==2 will never be true will never be true. So, no records will be retrieved. The table test_03 will be an empty table with basic compression. Let’s create the table.

As the table is created let’s look at the records inside the table test_03.

Statement: select * from test_03;

There are no records inside the table test_03. Now, let’s try to insert the data into the table. We will be using the same dba_objects table, but now we are going to insert the data after the table is created empty with the compress option.

insert into test_03
select * from dba_objects where rownum <= 10000;
commit;

The data is now inserted into the table, and the commit is also complete. Let’s check the table block allocated.

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

No blocks were allocated for table TEST_03. In this situation, we have to analyze the table and compute statistics to claim the space.

Statement: ANALYZE TABLE TEST_03 COMPUTE statistics;

So, the table TEST_03 is now analyzed. Let’s check the block size of the table again.

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

The blocks allocated for the table are 244 blocks. Even though the table is compressed, the block size is increased because we have selected the normal insert statement.

Case 4: Direct path inserts into the table, which is defined as compressed.

Let’s create an empty table with compress mode as basic.

create table TEST_04 compress basic
as
select * from dba_objects where rownum = 0;

This is another way to create an empty table.

So, the table TEST_04 is created. Let’s check the table contents

Statement: select * from TEST_04;

So, there are no records inside the table. Now, let’s insert the data into the table.

We will be using an optimizer called append inside the insert statement. This optimizer append will help in high-performance select statements and DML statements.

Statement: insert /*+ append */ into TEST_04 select * from dba_objects where rownum <= 10000;

The Append hint tells the optimizer to perform a direct-path insert, which improves the performance of INSERT and select operations for the reasons below.

• Data is appended to the end of the table rather than attempting to use the existing free space.
• Data is written directly into the data files, bypassing the buffer cache.

Let’s insert the records into the table.

So, the rows were inserted into the table. Let’s analyze the table for the correct statistics.

Statement: ANALYZE TABLE TEST_04 COMPUTE statistics

So, the table is now analyzed. Let’s look at the blocks allocated for the table.

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

So, the blocks allocated for the table TEST_04 is 43, and PCT_FREE is 0. The compression is enabled, and the compression mode is basic. This is a similar situation for case 2.

Case 5: Create a table without compression and change to compression.

Let’s create a table and then compress the table to change the table to compress mode.

create table test_05
as
select * from dba_objects where rownum <= 10000;

So, the table is created without compression. Let’s check the block size of the table.

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

So, we can see the blocks are 186, and the compression mode is disabled. Let’s try to compress the table TEST_05.

Statement: alter table TEST_05 compress basic;

So, the table TEST_05 is altered. When we compress the table, the existing data inside it will not be affected. Let’s check the blocks allocated after compression.

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

The blocks for the table are not changed. However, the move option allows you to compress the old data already present inside the table.

Statement: alter table test_05 move;

The table test_05 is now altered using the move option. Let’s analyze the table with computed statistics.

Statement: ANALYZE TABLE TEST_05 COMPUTE statistics

So, the table TEST_05 is analyzed. Let’s check the blocks of the table after using the move option.

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

The blocks used by the table is 43. So, if we compress the table after the data is inserted, the blocks will not change. But if we use the move option, then the old data will be compressed.

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