LATEST TOPICS

ORA-39726: While dropping column from compressed table

We know that we can use the ALTER TABLE DROP COLUMN command to drop a column from a table. However, if the table is a compressed one; the scenario is bit different.

Today, I would be covering the issues that you might face while dropping a COLUMN from a COMPRESSED table.

 

Demonstration:

Drop column with BASIC compression enabled

Lets create a table with basic compression enabled.

SQL:labpa> create table order_info
  2  (
  3  order_id number(5),
  4  product_id number(4),
  5  order_count number(4),
  6  order_date date,
  7  comments varchar(20)
  8  )
  9  compress;

Table created.

SQL:labpa> desc ORDER_INFO
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ORDER_ID                                                       NUMBER(5)
 PRODUCT_ID                                                     NUMBER(4)
 ORDER_COUNT                                                    NUMBER(4)
 ORDER_DATE                                                     DATE
 COMMENTS                                                       VARCHAR(20)

Lets Load some data into the table.

SQL:labpa> declare
  2  begin
  3  for i in 1..1000
  4  LOOP
  5  insert into ORDER_INFO values (i,i,i+10,sysdate,'Order No '||i);
  6  END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Crosscheck if compression is enabled for the table

SQL:labpa> select table_name,COMPRESSION,COMPRESS_FOR from user_tables where table_name='ORDER_INFO';

TABLE_NAME      COMPRESS COMPRESS_FOR
--------------- -------- ------------------------------
ORDER_INFO      ENABLED  BASIC

Okay, let try to drop a column from the above table.

SQL:labpa> alter table ORDER_INFO drop column comments;
alter table ORDER_INFO drop column comments
                                   *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

As the error suggest, we can not drop a column from the table as it is a compressed table.

Lets try to DECOMPRESS the table and drop the column.

SQL:labpa> alter table ORDER_INFO nocompress;

Table altered.

SQL:labpa> select table_name,COMPRESSION,COMPRESS_FOR from user_tables where table_name='ORDER_INFO';

TABLE_NAME      COMPRESS COMPRESS_FOR
--------------- -------- ------------------------------
ORDER_INFO      DISABLED

SQL:labpa> alter table ORDER_INFO drop column comments;
alter table ORDER_INFO drop column comments
                                   *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

Still it is showing the same error and considering the table as compressed. But the question is why?

This is because ‘ALTER TABLE [table_name] NOCOMPRESS‘ applies to any future records after issuing the command and doesn’t decompress the data which are already compressed due to the previous compression settings.

Since, we still can not drop the column, we have the option to mark the column as UNUSED to prevent it appearing from the table definition. Note that setting a column as UNUSED just updates the table definition and doesn’t physically remove the column. SET UNUSED is particularly used in situations where it is expected that the drop column operation would take more time to complete.

To actually reclaim space, we need to either directly DROP the column or drop the UNUSED column (if SET UNUSED)

Okay, since we can’t drop the column from compressed table. Lets mark the column UNUSED to make it disappear from the table.

SQL:labpa> alter table ORDER_INFO set unused column comments;

Table altered.

SQL:labpa> desc ORDER_INFO
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ORDER_ID                                                       NUMBER(5)
 PRODUCT_ID                                                     NUMBER(4)
 ORDER_COUNT                                                    NUMBER(4)
 ORDER_DATE                                                     DATE

Okay, now the column is removed from the table. However, it is not yet physically removed and hence the space allocated is still not reclaimed.

Once, we mark a column UNUSED an entry is recorded in DBA_UNUSED_COL_TABS and/or USER_UNUSED_COL_TABS to help us physically remove the column at a later point of time.

As we have marked the column UNUSED, we have the following entry in USER_UNUSED_COL_TABS.

SQL:labpa> select * from USER_UNUSED_COL_TABS;

TABLE_NAME           COUNT
--------------- ----------
ORDER_INFO               1

Okay, lets try if we can drop the column that we have marked as UNUSED for our compressed table and reclaim the space.

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

Nope… Oracle is still not allowing us to drop the UNUSED column for compressed table.

Lets again try to decompress the table and drop the UNUSED column

SQL:labpa> alter table ORDER_INFO nocompress;

Table altered.

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

Still no luck. The reason is, the records before issuing the NOCOMPRESS command are still in compressed format and Oracle somehow doesn’t physically drop compressed column data.

Lets try to decompress the entire table and drop the unused column.

SQL:labpa> alter table ORDER_INFO move nocompress parallel 4;

Table altered.

SQL:labpa> alter table ORDER_INFO drop unused columns;

Table altered.

SQL:labpa> select * from USER_UNUSED_COL_TABS;

no rows selected

SQL:labpa> 

Wow !!! it worked.

 

Drop column with Advanced compression enabled

Lets analyze the same DROP COLUMN operation for Advanced table compression (Comes with Additional Licensing)

For this exercise, will drop the table created for the last exercise and create a new once with Advanced compression enabled


SQL:labpa> drop TABLE ORDER_INFO ;

Table dropped.

SQL:labpa> create table ORDER_INFO
  2  (
  3  order_id number(5),
  4  product_id number(4),
  5  order_count number(4),
  6  order_date date,
  7  comments varchar(20)
  8  )
  9  compress for oltp;

Table created.

SQL:labpa> desc ORDER_INFO
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ORDER_ID                                                       NUMBER(5)
 PRODUCT_ID                                                     NUMBER(4)
 ORDER_COUNT                                                    NUMBER(4)
 ORDER_DATE                                                     DATE
 COMMENTS                                                       VARCHAR(20)

SQL:labpa> select table_name,COMPRESSION,COMPRESS_FOR from user_tables where table_name='ORDER_INFO';

TABLE_NAME      COMPRESS COMPRESS_FOR
--------------- -------- ------------------------------
ORDER_INFO      ENABLED  ADVANCED

Lets Load some data into the table.

SQL:labpa> declare
  2  begin
  3  for i in 1..1000
  4  LOOP
  5  insert into ORDER_INFO values (i,i,i+10,sysdate,'Order No '||i);
  6  END LOOP;
  7  END;
  8  /

PL/SQL procedure successfully completed.

Now lets drop the column.

SQL:labpa> alter table ORDER_INFO drop column comments;

Table altered.

SQL:labpa> desc ORDER_INFO
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ORDER_ID                                                       NUMBER(5)
 PRODUCT_ID                                                     NUMBER(4)
 ORDER_COUNT                                                    NUMBER(4)
 ORDER_DATE                                                     DATE

Wow it worked. But hold on. did it really drop the column. Take a look at the DBA_UNUSED_COL_TABS or USER_UNUSED_COL_TABS.

SQL:labpa> select * from USER_UNUSED_COL_TABS;

TABLE_NAME           COUNT
--------------- ----------
ORDER_INFO               1

A entry for the table is showing there. That means the DROP COLUMN command just updated the table definition rather than physically removing the column.

Lets decompress the entire table like the way we did for the BASIC compression and drop the UNUSED column.

SQL:labpa> alter table ORDER_INFO move nocompress parallel 4;

Table altered.

SQL:labpa> alter table ORDER_INFO drop unused columns;

Table altered.

SQL:labpa> select * from USER_UNUSED_COL_TABS;

no rows selected

Now, the column is actually dropped with space being reclaimed !!

 

Conclusion:

To physically drop a column from a compressed table, we need to decompress the entire table. Even though Oracle says it supports ALTER TABLE DROP COLUMN for tables with Advanced compression, it just marks the column UNUSED rather than actually dropping it.

If decompress is not feasible to perform immediately, we can mark the columns UNUSED and later drop them when it is feasible to decompress the underlying table.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed