ALTER TABLE NOCOMPRESS

What a difference three words can make.

No, not those ones, these ones MODIFY DEFAULT ATTRIBUTES.

https://docs.oracle.com/en/database/oracle/oracle-database/21/vldbg/maintenance-partition-tables-indexes.html#GUID-1C8A5365-F6DD-4A81-8450-1B66E8BEA657

create table domtest
(col1 number
,col2 varchar2(200))
partition by range (col1) interval (1)
(partition p0 values less than (1))
compress for oltp;

> Table created.

insert into domtest
with x as (select level lvl from dual connect by level <= 10)
select lvl, rpad(lvl,200,'X') from x;

> 10 row(s) inserted.

commit;

> Statement processed.

select table_name, def_compression, def_compress_for
from   user_part_tables
where  table_name = 'DOMTEST';

TABLE_NAME	DEF_COMPRESSION	DEF_COMPRESS_FOR
DOMTEST	ENABLED	ADVANCED

select table_name, partition_name, high_value, compression, compress_for
from   user_tab_partitions
where  table_name = 'DOMTEST';

TABLE_NAME	PARTITION_NAME	HIGH_VALUE	COMPRESSION	COMPRESS_FOR
DOMTEST	P0	1	ENABLED	ADVANCED
DOMTEST	SYS_P487584	2	ENABLED	ADVANCED
DOMTEST	SYS_P487585	3	ENABLED	ADVANCED
DOMTEST	SYS_P487586	4	ENABLED	ADVANCED
DOMTEST	SYS_P487587	5	ENABLED	ADVANCED
DOMTEST	SYS_P487588	6	ENABLED	ADVANCED
DOMTEST	SYS_P487589	7	ENABLED	ADVANCED
DOMTEST	SYS_P487590	8	ENABLED	ADVANCED
DOMTEST	SYS_P487591	9	ENABLED	ADVANCED
DOMTEST	SYS_P487592	10	ENABLED	ADVANCED
DOMTEST	SYS_P487593	11	ENABLED	ADVANCED

If I want to change the default compression at table level so that new partitions are no longer compressed, then those three words make a big difference.

alter table domtest nocompress;
>Table altered.

This does what we want:

select table_name, def_compression, def_compress_for
from   user_part_tables
where  table_name = 'DOMTEST';

TABLE_NAME	DEF_COMPRESSION	DEF_COMPRESS_FOR
DOMTEST	DISABLED	 - 

But it does a whole lot we don’t. It doesn’t move the existing data but it does change the partition attributes for new data.

select table_name, partition_name, high_value, compression, compress_for
from   user_tab_partitions
where  table_name = 'DOMTEST';

TABLE_NAME	PARTITION_NAME	HIGH_VALUE	COMPRESSION	COMPRESS_FOR
DOMTEST	P0	1	DISABLED	 - 
DOMTEST	SYS_P487584	2	DISABLED	 - 
DOMTEST	SYS_P487585	3	DISABLED	 - 
DOMTEST	SYS_P487586	4	DISABLED	 - 
DOMTEST	SYS_P487587	5	DISABLED	 - 
DOMTEST	SYS_P487588	6	DISABLED	 - 
DOMTEST	SYS_P487589	7	DISABLED	 - 
DOMTEST	SYS_P487590	8	DISABLED	 - 
DOMTEST	SYS_P487591	9	DISABLED	 - 
DOMTEST	SYS_P487592	10	DISABLED	 - 
DOMTEST	SYS_P487593	11	DISABLED	 - 

Whereas if we reset and then use the correct syntax from the get-go:

alter table domtest modify default attributes nocompress;
>Table altered.

select table_name, def_compression, def_compress_for
from   user_part_tables
where  table_name = 'DOMTEST';

TABLE_NAME	DEF_COMPRESSION	DEF_COMPRESS_FOR
DOMTEST	DISABLED	 - 

select table_name, partition_name, high_value, compression, compress_for
from   user_tab_partitions
where  table_name = 'DOMTEST';

TABLE_NAME	PARTITION_NAME	HIGH_VALUE	COMPRESSION	COMPRESS_FOR
DOMTEST	P0	1	ENABLED	ADVANCED
DOMTEST	SYS_P487584	2	ENABLED	ADVANCED
DOMTEST	SYS_P487585	3	ENABLED	ADVANCED
DOMTEST	SYS_P487586	4	ENABLED	ADVANCED
DOMTEST	SYS_P487587	5	ENABLED	ADVANCED
DOMTEST	SYS_P487588	6	ENABLED	ADVANCED
DOMTEST	SYS_P487589	7	ENABLED	ADVANCED
DOMTEST	SYS_P487590	8	ENABLED	ADVANCED
DOMTEST	SYS_P487591	9	ENABLED	ADVANCED
DOMTEST	SYS_P487592	10	ENABLED	ADVANCED
DOMTEST	SYS_P487593	11	ENABLED	ADVANCED