Compression -- 8 : DROPping a Column of a Compressed Table

Building on the series on Compression .....

What happens if we try to DROP a column in a Compressed Table ?  How can we execute the DROP ?

Starting with BASIC Compression.

SQL> connect hemant/hemant
Connected.
SQL> create table compress_basic as select * from source_data where 1=2;

Table created.

SQL> alter table compress_basic compress;

Table altered.

SQL> insert /*+ APPEND */ into compress_basic
  2  select * from source_data           
  3  where rownum < 100001;

100000 rows created.

SQL> commit;
 
Commit complete.

SQL> select compression, compress_for
  2  from user_tables
  3  where table_name = 'COMPRESS_BASIC'
  4  /

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  BASIC

SQL> alter table compress_basic drop (object_name);
alter table compress_basic drop (object_name)
                                 *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> 
SQL> !oerr ora 39726
39726, 00000, "unsupported add/drop column operation on compressed tables"
// *Cause:  An unsupported add/drop column operation for compressed table
//          was attemped.
// *Action: When adding a column, do not specify a default value.
//          DROP column is only supported in the form of SET UNUSED column
//          (meta-data drop column).

SQL> 

So, I would have to set the column to UNUSED !

SQL> alter table compress_basic set unused column object_name;

Table altered.

SQL> alter table compress_basic drop (object_name);
alter table compress_basic drop (object_name)
                                 *
ERROR at line 1:
ORA-00904: "OBJECT_NAME": invalid identifier


SQL> alter table compress_basic drop unused columns;
alter table compress_basic drop unused columns
*
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> 

I can't drop a column from a table with Compression enabled.

Is there another way ?

SQL> alter table compress_basic move nocompress;

Table altered.

SQL> alter table compress_basic drop unused columns;

Table altered.

SQL> 

To actually execute the DROP, I have to Uncompress the table !

 So : Remember : You have to be careful when designing a table that you intend to Compress.  You won't be able to DROP columns !


Repeating the test case with OLTP Compression :

SQL> create table compress_oltp as select * from source_data where 1=2;

Table created.

SQL> alter table compress_oltp compress for oltp;

Table altered.

SQL> select compression, compress_for 
  2  from user_tables
  3  where table_name = 'COMPRESS_OLTP'
  4  /

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  ADVANCED

SQL> 
SQL> insert into compress_oltp
  2  select * from source_data
  3  where rownum < 100001;

100000 rows created.

SQL> commit;

Commit complete.

SQL> alter table compress_oltp drop (object_name);

Table altered.

SQL> 

Much easier, I could DROP the column.  But, wait.  Is there a catch ?

SQL> drop table compress_oltp purge;

Table dropped.

SQL> create table compress_oltp as select * from source_data where 1=2;

Table created.

SQL> alter table compress_oltp compress for oltp;

Table altered.

SQL> select compression, compress_for
  2  from user_tables
  3  where table_name = 'COMPRESS_OLTP'
  4  /

COMPRESS COMPRESS_FOR
-------- ------------------------------
ENABLED  ADVANCED

SQL> alter table compress_oltp nocompress;

Table altered.

SQL> select compression,  compress_for
  2  from user_tables
  3  where table_name = 'COMPRESS_OLTP'
  4  /

COMPRESS COMPRESS_FOR
-------- ------------------------------
DISABLED

SQL> alter table compress_oltp drop (object_name);
alter table compress_oltp drop (object_name)
                                *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> 

If I ALTER the table to NOCOMPRESS (which can take effect only on *new* rows, not existing rows), I cannot DROP a column.  This is because Oracle is unsure if there is a mix of Comressed and Non-Compressed rows in the table now.

What I'd have to do is to rebuild it as a NOCOMPRESS table.

SQL> alter table compress_oltp move nocompress;

Table altered.

SQL> alter table compress_oltp drop (object_name);

Table altered.

SQL> 

So, once a table is set to COMPRESS OLTP and then set to NOCOMPRESS, you can't simply DROP a column.


This test-case came out of an issue a friend of mine faced today.  He found that he couldn't drop a column from a table that was formerly set to COMPRESS FOR OLTP.

I pointed him to Support Document 1288918.1

.
.
.