2种方法解决这个问题
前提是改变表的压缩方式 from Basic Table Compression to OLTP
首先要知道表是用那种方式压缩,我的case种表是一张分区表,并且还有子分区所以,所以需要查 subpartition 。
select table_name,compression,compress_for,PARTITION_NAME, SUBPARTITION_NAME
from user_tab_subpartitions
where table_name = 'TEST_TABLE';
返回结果显示的是Basic compression.
change to OLTP compression.
ALTER TABLE TCDS_FA_FOF_TRANSACTN compress for OLTP;
method 1, set UNUSED COLUMN
ALTER TABLE TEST_TABLE SET UNUSED COLUMN TEST_NME;
ALTER TABLE TEST_TABLE DROP UNUSED COLUMNS;
method 2, DROP COLUMN
ALTER TABLE TEST_TABLE DROP COLUMN TEST_NME;
OLTP compression is better than BASIC, when table have duplicated data, compression option can save space. the only difference is BASIC need uncompress action before access data lock, but for OLTP no UNCOMPRESS option.