in Oracle

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

.
.
.