Dropping Virtual Columns Causes Unnecessary Invalidation

When you drop a column from a table (or set a column unused), any view that references this column becomes invalid.
As of Oracle 11gR1, where Fine Grained Dependency Tracking was introduced, views that reference other columns of the table, but do not reference the dropped column, should not become invalid. And this is usually true, but this week I discovered a case where it’s not.

When dropping a virtual column, even views that do not reference the dropped column become invalid.

Let’s see an example (tested in 11g, 12c and 18c):

We create a table with 3 columns, one of them is virtual

ORA$BASE> create table t (
  2    column_in_view             number,
  3    regular_column_not_in_view number,
  4    virtual_column_not_in_view number as (42) virtual
  5  );

Table created.

And we create a view that projects only one of the table columns

ORA$BASE> create or replace view v as
  2    select column_in_view from t;

View created.

The view is valid at this point

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
VALID

Dropping the non-referenced non-virtual column does not invalidate the view (which is a good thing, of course)

ORA$BASE> alter table t drop column regular_column_not_in_view;

Table altered.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
VALID

However, dropping the non-referenced virtual column does invalidate the view

ORA$BASE> alter table t drop column virtual_column_not_in_view;

Table altered.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
INVALID

And we can see the same behavior when setting the columns unused instead of dropping them:

ORA$BASE> drop table t;

Table dropped.

ORA$BASE> create table t (
  2    column_in_view             number,
  3    regular_column_not_in_view number,
  4    virtual_column_not_in_view number as (42) virtual
  5  );

Table created.

ORA$BASE> create or replace view v as
  2    select column_in_view from t;

View created.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
VALID

ORA$BASE> alter table t set unused column regular_column_not_in_view;

Table altered.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
VALID

ORA$BASE> alter table t set unused column virtual_column_not_in_view;

Table altered.

ORA$BASE> select status from user_objects where object_name = 'V';

STATUS
-------
INVALID

ORA$BASE>

When using EBR (Edition-Based Redefinition) for online application upgrades, Fine Grained Dependency Tracking is very important. The common practice for dropping a column with no downtime is as follows:

• In a new edition: take the column out of the editioning view that covers the table (and remove any references to it from the code)
• Expose the new edition
• As soon as all the sessions use the new edition: drop the column from the table (or set it unused)

This practice fails when the dropped column is a virtual one, due to the behavior we’ve seen above (which, in my opinion, is a bug). Until this bug is solved, I see two workarounds (which I don’t really like):

• Just leave the virtual column there, unexposed to the editioning view
• Create a new table (without that column) to replace the existing one (with the necessary migration work, including cross-edition triggers)

I’ll write more about this, and about other invalidation cases, in my EBR series.