In defense of HCC: ORA-39726: unsupported add/drop column operation on compressed tables

A friend asked to check the

ISSUE:
ORA-39726 is hit when adding column with default value to basic compressed table.

CAUSE:
compressed table will not allow some operation, which are allowed in uncompressed table.

"Is there any error during add column with default value to table compressed with HCC ?
Does Oracle unpack HCC data during add column with default value to the table compressed with HCC ?"  he asked.

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

And the answer is here.

RDBMS version is 12.1.0.2.171017 (i choosed old enough version i can find. newer versions belived work the same).

$ opatch lspatches
26635845;Database PSU 12.1.0.2.171017, Oracle JavaVM Component (OCT2017)
25729214;OCW Interim patch for 25729214
26717470;Database Bundle Patch : 12.1.0.2.171017 (26717470)


$ sqlplus / as sysdba
SQL> create tablespace yu extent management local segment space management auto;
SQL> create user yu identified by yu default tablespace yu;
SQL> grant dba to yu ;

SQL> conn yu/yu

SQL> create table NOCOMP as select owner,object_type,object_name,object_id,created from dba_objects order by owner,object_type,object_name;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
insert into nocomp select * from nocomp;
commit;

Table created.

SQL>
151156 rows created.

SQL>
302312 rows created.

SQL>
604624 rows created.

SQL>
1209248 rows created.

SQL>
Commit complete.

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------
NOCOMP

SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 1;

SEGMENT_NAME        BYTES   MB    BLOCKS
-------------- ---------- ------- ----------
NOCOMP          159383552   152   9728


SQL> create table BASICOMP compress as select * from NOCOMP;

create table OLTPCOMP  compress for OLTP as select * from NOCOMP;

create table QL   compress for query LOW as select * from NOCOMP;
create table QLRL compress for query LOW row level locking as select * from NOCOMP;

create table QH   compress for query HIGH as select * from NOCOMP;
create table QHRL compress for query HIGH row level locking as select * from NOCOMP;

create table AL   compress for archive LOW as select * from NOCOMP;
create table ALRL compress for archive LOW row level locking as select * from NOCOMP;

create table AH   compress for archive HIGH as select * from NOCOMP;
create table AHRL compress for archive HIGH row level locking as select * from NOCOMP;


Table created.

SQL>
Table created.

SQL> SQL>
Table created.

SQL>
Table created.

SQL> SQL>
Table created.

SQL>
Table created.

SQL> SQL>
Table created.

SQL>
Table created.

SQL> SQL>
Table created.

SQL>
Table created.

SQL> exec dbms_stats.gather_schema_stats('YU');
PL/SQL procedure successfully completed.

SQL> col segment_name for a8
SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 2 desc;

SEGMENT_      BYTES        MB     BLOCKS
-------- ---------- ---------- ---------
NOCOMP    159383552       152       9728
OLTPCOMP  100663296        96       6144
BASICOMP   92274688        88       5632
QLRL       47185920        45       2880
QL         44040192        42       2688
QHRL       26214400        25       1600
ALRL       25165824        24       1536
QH         24117248        23       1472
AL         23068672        22       1408
AHRL       18874368        18       1152
AH         16777216        16       1024

SQL> col table_name for a8
SQL> select table_name,num_rows,blocks from user_tables order by blocks desc;

TABLE_NA   NUM_ROWS    BLOCKS
-------- ---------- ---------
NOCOMP      2418496      9652
OLTPCOMP    2418496      5799
BASICOMP    2418496      5211
QLRL        2418496      2848
QL          2418496      2670
QHRL        2418496      1577
ALRL        2418496      1492
QH          2418496      1456
AL          2418496      1400
AHRL        2418496      1148
AH          2418496      1010

SQL> 
alter table NOCOMP   add new_column number(3) default 0;
alter table BASICOMP add new_column number(3) default 0;
alter table OLTPCOMP add new_column number(3) default 0;
alter table QLRL     add new_column number(3) default 0;
alter table QL       add new_column number(3) default 0;
alter table QHRL     add new_column number(3) default 0;
alter table ALRL     add new_column number(3) default 0;
alter table QH       add new_column number(3) default 0;
alter table AL       add new_column number(3) default 0;
alter table AHRL     add new_column number(3) default 0;
alter table AH       add new_column number(3) default 0;
Table altered.

alter table BASICOMP add new_column number(3) default 0
                        *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL>
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL>
Table altered.

SQL> exec dbms_stats.gather_schema_stats('YU');
PL/SQL procedure successfully completed.

SQL> col segment_name for a8
SQL> select segment_name, bytes, bytes/1048576 MB, blocks from user_segments order by 2 desc;

SEGMENT_      BYTES     MB   BLOCKS  BLOCKS before add column
-------- ---------- ------- ------- --------------------------
NOCOMP    159383552    152     9728    9728
OLTPCOMP  100663296     96     6144    6144
BASICOMP   92274688     88     5632    5632
QLRL       47185920     45     2880    2880
QL         44040192     42     2688    2688
QHRL       26214400     25     1600    1600
ALRL       25165824     24     1536    1536
QH         24117248     23     1472    1472
AL         23068672     22     1408    1408
AHRL       18874368     18     1152    1152
AH         16777216     16     1024    1024

SQL> col table_name for a8
SQL> select table_name,num_rows,blocks from user_tables order by blocks desc;

TABLE_NA   NUM_ROWS    BLOCKS   BLOCKS before add column
-------- ---------- ----------   ------------------------
NOCOMP      2418496      9652     9652
OLTPCOMP    2418496      5799     5799
BASICOMP    2418496      5211     5211
QLRL        2418496      2848     2848
QL          2418496      2670     2670
QHRL        2418496      1577     1577
ALRL        2418496      1492     1492
QH          2418496      1456     1456
AL          2418496      1400     1400
AHRL        2418496      1148     1148
AH          2418496      1010     1010

No one block is added after ADD COLUMN modification!

As you can see the only issue for BASIC COMPRESSION:

alter table BASICOMP add new_column number(3) default 0
                         *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

In other cases "alter table add column" work well: no decompression at all.
As the DOC 12.1.0.2 say:
"... the default value is stored as metadata, the column itself is not populated with data"