02 June, 2016

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

.
.
.

1 comment:

Maxim said...

In my opinion, the behaviour of drop column for compressed for OLTP tables is even more worse, as in the case of basic compression - if one drops the column the statement is executed flawlessly (as you shown) and one would expect, the column is indeed removed, but in fact, oracle silently set this column unused, and this might lead to some problems if one is not aware of this behaviour. For example subsequent - partition exchange on such table might fail (because unused column is in place), so to get rid of it the same process like in case of basic compression has to be applied - uncompress, drop unused columns, compress again.