Oracle查询分区是否压缩,压缩表中的数据是否已压缩？(已解决)

COMP_NOCOMPRESS CONSTANT NUMBER := 1;

COMP_FOR_OLTP CONSTANT NUMBER := 2;

COMP_FOR_QUERY_HIGH CONSTANT NUMBER := 4;

COMP_FOR_QUERY_LOW CONSTANT NUMBER := 8;

COMP_FOR_ARCHIVE_HIGH CONSTANT NUMBER := 16;

COMP_FOR_ARCHIVE_LOW CONSTANT NUMBER := 32;

COMP_RATIO_MINROWS CONSTANT NUMBER := 1000000;

COMP_RATIO_ALLROWS CONSTANT NUMBER := -1;

上表列出了压缩类型的常数值，例如COMP_FOR_QUERY_HIGH是4，COMP_FOR_QUERY_LOW 是8

这里我们从上述查询GET_COMPRESSION_TYPE指定rowid的情况下得到的是4说明该列以COMP_FOR_QUERY_HIGH形式压缩：

SQL>  update hcc_maclean set OBJECT_NAME=OBJECT_NAME||'DBM' where owner='MACLEAN';

8 rows updated.

SQL> commit;

Commit complete.

SQL>  select DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',rowid) from HCC_MACLEAN where owner='MACLEAN';

DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','HCC_MACLEAN',ROWID)

------------------------------------------------------------------

1

1

1

1

1

1

1

1

8 rows selected.

以上更新一定量数据后可以看到COMPRESSION_TYPE由COMP_FOR_QUERY_HIGH降级为COMP_NOCOMPRESS，这说明这张表虽然compress for query high但部分数据在更新后实际不再被压缩。

http://www.oracledatabase12g.com ... -insert-update.html