Drop Hidden Columns in Oracle table

Extended Statistics feature enables Oracle to calculate statistics on group of columns to improve the row estimates.This will be when there is correlation between the values of two (or more) columns in a table.

How to identify Extended Stats ?
There will be system generated column in the table (can be found when queries in user_tab_cols table)


select column_name, data_default, hidden_column
from   user_tab_cols
where  table_name = 'EXT_ST_TAB';

COLUMN_NAME                    DATA_DEFAULT                   HID
-----------                ------------                   ---
SYS_STUYPW88OE302TFVBNC6$MMQXE SYS_OP_COMBINED_HASH("X","Y"   YES
Z                                                             NO
Y                                                             NO
X                                                             NO

The above SYS_ column was generated by system due to merging the arguments together to form a hash and it is hidden column.

And to make sure it is the column created by extended stats feature then DATA_DEFAULT value must be SYS_OP_COMBINED_HASH().


Find the stat_extensions details

select owner, table_name, extension_name, creator
       ,extension
       , cast(substr( extension,1,200) as varchar2(200) )
  from all_stat_extensions
 where table_name='EXT_ST_TAB'

Get the out put from below query and execute them in db for dropping those extended stats

select 'exec dbms_stats.drop_extended_stats(''' || owner || ''',''' || table_name || ''',''' || cast(substr( extension,1,200) as varchar2(200) ) || ''' );' 

from all_stat_extensions
where table_name='EXT_ST_TAB'

exec dbms_stats.drop_extended_stats('ADARLA', 'EXT_ST_TAB', '(x, y)');

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

Sometimes if you add additional column with DEFAULT value then also you can notice system generated column in the table. This Oracle optimization feature which will add this SYS_ column.

Note: When you set any column as UNUSED also you see hidden column 

Case 1: ADD column with DEFAULT value

CREATE TABLE DWADM.HIDDEN_COL_TST

(

  A1  NUMBER primary key,

  B1  NUMBER                   

);

Check column created in user_tab_cols or dba_tab_cols

select  table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'HIDDEN_COL_TST';

TABLE_NAME                    COL        HID         VIR

------------------                    -----        -----        -----
HIDDEN_COL_TST            A1           NO         NO
HIDDEN_COL_TST            B1           NO         NO

Now try adding new column with DEFAULT value

ALTER TABLE HIDDEN_COL_TST ADD (A2 NUMBER DEFAULT 0);

Again check for column in system metadata table

select  table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'HIDDEN_COL_TST';

TABLE_NAME                    COL                        HID         VIR

------------------                    -----                        -----        -----
HIDDEN_COL_TST            A1                           NO         NO
HIDDEN_COL_TST            B1                           NO         NO
HIDDEN_COL_TST            SYS_NC00003$  YES         NO
HIDDEN_COL_TST            A2                           NO         NO


We can notice Hidden column SYS_NC0003$ which was created by system.

If for any reason you don't want this Hidden Column on your table then you have to set below parameter to FALSE before adding new column to the table.

Alter session set "_add_col_optim_enabled"=false ;

• This parameter is set to true by default if Parameter COMPATIBLE >= 11 .

   1. Before 11g or if _ADD_COL_OPTIM_ENABLED = FALSE:

       The DDL will cause the column to be added to the table definition and every row of the table is updated with the new column and its default value. For large tables the update of all rows to add a column can take some time.

   2. From 11g Onward provided _ADD_COL_OPTIM_ENABLED = TRUE:

       The DDL adds the column to the table definition but there is no update of the actual table rows.
       Instead the dictionary stores a flag so that it knows that any row without the column value present should return the DEFAULT value instead.
       The setting of the parameter only affects new ADD COLUMN commands. Once a column has been added using the optimized method a flag in the dictionary indicates that specific column as being optimized.

       You can check which columns have been Added with the optimization active using SQL of the form below :

select owner, object_name, name
from dba_objects, col$
where bitand(col$.PROPERTY,1073741824)=1073741824
and object_id=obj#;

Case 2: Set Column UNUSED

ALTER TABLE HIDDEN_COL_TST SET UNUSED COLUMN B1;

select  table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'HIDDEN_COL_TST';

TABLE_NAME                    COL                                                        HID         VIR

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

HIDDEN_COL_TST            A1                                                           NO         NO

HIDDEN_COL_TST            SYS_C00002_19070200:44:53$    YES         NO

HIDDEN_COL_TST            SYS_NC00003$                                  YES         NO

HIDDEN_COL_TST            A2                                                           NO         NO

We can notice B2 column was removed and hidden column created in place of it.


Now our objective is to remove these hidden columns.
DROP column command will not work as DML and DDL operations are not directly allowed on guard-columns.

There is separate syntax for deleting these unused columns.

ALTER TABLE HIDDEN_COL_TST DROP UNUSED COLUMNS;

Unfortunately above command also not deleting those unused and hidden columns though command execution successful. This is due to table compression enabled  because it's expensive and requires a full table decompression. Any drop column will be converted to set unused internally.
The reason is that dropping a column requires a full table update and will result in full decompression .

Possible workaround for deleting these unused hidden column is by using dbms_redefinition.cons_use_pk and then add primary key.

Here is the example.

create table UNUSED_TAB

( 

TXN_DATE DATE,

TXN_FLG VARCHAR2(1),

TXN_LINE_NBR NUMBER PRIMARY KEY

) COMPRESS FOR ALL OPERATIONS;

alter table unused_tab add c1 number default 0;

alter table unused_tab set unused column c1;

select table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'UNUSED_TAB';

TABLE_NAME    COLUMN_NAM                           HID      VIR

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

UNUSED_TAB    TXN_DATE                                NO        NO

UNUSED_TAB    TXN_FLG                                  NO        NO

UNUSED_TAB    TXN_LINE_NBR                         NO        NO

UNUSED_TAB    SYS_C00004_19070201:17:03$   YES       NO

UNUSED_TAB    SYS_C00005_19070201:17:03$   YES       NO

DECLARE

p_option PLS_INTEGER := dbms_redefinition.cons_use_pk;

p_owner varchar2(30) := 'ADARLA';

p_tname varchar2(30) := 'UNUSED_TAB';

BEGIN

dbms_redefinition.can_redef_table(p_owner, p_tname, p_option);
dbms_output.put_line('Option returned : '||p_option);

END;

create table UNUSED_TAB_I

( 

TXN_DATE DATE,

TXN_FLG VARCHAR2(1),

TXN_LINE_NBR NUMBER PRIMARY KEY

);

DECLARE

num_errors pls_integer;

BEGIN

dbms_redefinition.start_redef_table(uname => 'ADARLA',

orig_table => ‘UNUSED_TAB’,

int_table => ‘UNUSED_TAB_I’,

col_mapping => NULL,

options_flag => dbms_redefinition.cons_use_pk);

dbms_redefinition.copy_table_dependents(uname => 'ADARLA',

orig_table => ‘UNUSED_TAB’,

int_table => ‘UNUSED_TAB_I’,

copy_indexes => dbms_redefinition.cons_orig_params,

copy_triggers => TRUE,

copy_constraints => TRUE,

copy_privileges => TRUE,

ignore_errors => TRUE,

num_errors => num_errors);

dbms_output.put_line('Number of Errors: '||num_errors);

dbms_redefinition.sync_interim_table(uname => 'ADARLA',

orig_table => ‘UNUSED_TAB’,

int_table => ‘UNUSED_TAB_I’);

dbms_redefinition.finish_redef_table(uname => 'ADARLA',

orig_table => ‘UNUSED_TAB’,

int_table => ‘UNUSED_TAB_I’);

END;

drop table UNUSED_TAB_I purge;

select table_name, column_name, hidden_column, virtual_column 

from dba_tab_cols 

where table_name = 'UNUSED_TAB';

TABLE_NAME    COLUMN_NAM                           HID      VIR

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

UNUSED_TAB    TXN_DATE                                NO        NO

UNUSED_TAB    TXN_FLG                                  NO        NO

UNUSED_TAB    TXN_LINE_NBR                         NO        NO