Compress Your Table With NO Downtime!

We’ve all been there. Your application lives, data volumes grow and comes the time to reduce disk space without disturbing the business!

If you use an Oracle Autonomous AI Database 26ai, you are SO LUCKY!

I had to do such housekeeping. My table stores test statistics. It grows, a lot, every day. It was reaching 48 GB with close to 266M rows. One third of the database size.

SQL> select bytes/1024/1024/1024 
from user_segments 
where segment_name='MY_STATS';

   BYTES/1024/1024/1024 
_______________________ 
          48.3720703125

Application is online 24/7 thus how to do it?

Three steps:

1. Change the table structure (DDL)
2. Online compress the data with the proper method
3. Track compression progress

Changing the table structure is easy:

ALTER TABLE my_stats COMPRESS FOR QUERY HIGH;

Done. All the compression methods are available on Autonomous AI Databases (here Transaction Processing Serverless). I used an aggressive one that matches the workloads on this table: numerous insertions and analytical queries. Compression ratio ~x10.

Get Loïc Lefèvre’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

See documentation for available compression methods from the Compression advisor PL/SQL package.

Compress the table while application can still perform DML operations (writes, reads):

ALTER TABLE my_stats MOVE ONLINE;

The key word here is of course ONLINE which tells the database: “Hey don’t prevent my apps to do their job as usual!”.

To track this operation, you can rely on the V$SESSION_LONGOPS view which basically tracks all long operations:

select opname as "Operation",
       target_desc as "Target", 
       round(100*sofar/totalwork,2) as "Progress (%)", 
       time_remaining as "Time remaining (s)" 
  from v$session_longops 
 where time_remaining > 0;

This will display all the unfinished long operations including the compression of your table. Note that the operation of compressing online a table can involve several steps such as online index maintenance, and freed disk space reclamation.

SQL> select bytes/1024/1024/1024 
from user_segments 
where segment_name='MY_STATS';

   BYTES/1024/1024/1024 
_______________________ 
              4.7109375

Overall, the operation took 50 minutes and freed 43.66 GBs!

You’re welcome 😊