3.3.2 Determining Which Rows are Compressed

When Exadata Hybrid Columnar Compression tables are updated, the rows change to a lower level of compression.

For example, the compression level might change from COMP_FOR_QUERY_HIGH to COMP_FOR_OLTP or COMP_NOCOMPRESS.

By sampling the table rows, you can determine the percentage of rows that are no longer at the higher compression level.

• Use the following query to determine the compression level of a row:

  DBMS_COMPRESSION.GET_COMPRESSION_TYPE (
     ownname    IN    VARCHAR2, 
     tabname    IN    VARCHAR2, 
     row_id     IN    ROWID)
    RETURN NUMBER;

You can use ALTER TABLE or MOVE PARTITION to set the rows to a higher compression level. For example, if 10 percent of the rows are no longer at the highest compression level, then you might alter or move the rows to a higher compression level.