1

Lets say I want to implement basic compression for my table. I know it can be done in two steps, for example:

  1. alter table MYTABLE compress;
  2. alter table MYTABLE move;

Is there a way to check that both steps were implemented? How do I know that data was compressed?

I'm using Oracle 19c if that matters.

2 Answers 2

4
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
     count(*)
     from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);

COMPRESSION_TYPE   COUNT(*)
---------------- ----------
               1      23861

SQL> alter table t1 compress;

Table altered.

SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
     count(*)
     from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);

COMPRESSION_TYPE   COUNT(*)
---------------- ----------
               1      23861

Finally:

SQL> alter table t1 move;

Table altered.

SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
     count(*)
     from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);

COMPRESSION_TYPE   COUNT(*)
---------------- ----------
            4096      23861

Table 38-1 DBMS_COMPRESSION Constants - Compression Types

1 - No compression
...
4096 - Basic table compression
2

You can run the DBMS_COMPRESSION.GET_COMPRESSION_RATIO system stored procedure to obtain the actual compression ratio for the table. It won't tell you whether someone has run alter table ... move, obviously, but it will tell you if the table is actually compressed or only configured.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.