is my data compressed?



  • 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.



  • 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
    

    https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_COMPRESSION.html#GUID-8C9942CA-4EBD-48FF-9E8C-A59BF21A0176__BEIBFCCB

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



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2