Umbenennung und Löschung von Virtual Columns

Charles Hooper hat dieser Tage einen Artikel veröffentlicht, der einige seltsame Effekte im Zusammenhang der Umbenennung von Virtual Columns und der Verwenung des Hints _OPTIMIZER_IGNORE_HINTS anspricht. Interessant finde ich dabei vor allem die seltsamen Effekte, die die Umbenennung hervorrufen kann. Dazu ein kleines Beispiel (mit 11.2.0.1.0), das auf dem Test des Herrn Hooper basiert und diesen behutsam erweitert:

drop table t1;

create table t1(
    a number
  , b number    
);

create index t1_idx on t1(a + 1);

select column_name
     , data_default
  from user_tab_cols
 where table_name = 'T1';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ------------
A
B
SYS_NC00003$                   "A"+1

Der Test legt eine Tabelle mit zwei Spalten (A, B) an und ergänzt dann einen FBI mit Bezug auf die Spalte A, was implizit zur Erzeugung einer virtuellen Spalte (SYS_NC00003$) führt. Wenn man den FBI wieder löscht, dann verschwindet auch die virtuelle Spalte:

drop index t1_idx;

select column_name
     , data_default
  from user_tab_cols
 where table_name = 'T1';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ------------
A
B

Das Verhalten ändert sich, wenn man die virtuelle Spalte explizit umbenennt. In diesem Fall bleibt die Spalte auch nach der Löschung des Index verfügbar:

create index t1_idx on t1(a + 1);

alter table t1 rename column SYS_NC00003$ to C;

drop index t1_idx;

select column_name
     , data_default
  from user_tab_cols
 where table_name = 'T1';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ -------------
A
B
C                              "A"+1

Offenbar behandelt Oracle diese virtuelle Spalte jetzt als benutzerdefiniert und verzichtet deshalb auf die automatische Bereinigung. Merkwürdig wird der Fall, wenn man jetzt die Basisspalte zur virtuelle Spalte löscht:

alter table t1 drop column A;

select column_name
     , data_default
  from user_tab_cols
 where table_name = 'T1';

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ------------
B
SYS_NC00002$                   "A"+1

Die Löschung der Spalte A führt demnach dazu, dass die virtuelle Spalte wieder einen synthetischen Namen erhält (allerdings nicht mehr SYS_NC00003$, sondern SYS_NC00002$) - aber nicht zur Löschung der Spalte, die jetzt ziemlich in der Luft hängt:

select b
     , SYS_NC00002$
  from t1;

select b
*
FEHLER in Zeile 1:
ORA-00904: "A": ungültiger Bezeichner

Dieser Verhalten ist auf direktem Weg nicht zu erreichen, denn der Versuch die Basisspalte einer explizit erzeugten Virtual Column zu löschen, ruft einen Fehler hervor:

create table t2(
    a number
  , b number
);

alter table t2 add c generated always as (a + 1);

alter table t2 drop column a;
alter table t2 drop column a
                           *
FEHLER in Zeile 1:
ORA-54031: Spalte, die gelöscht werden muss, wird in einem virtuellen Spaltenausdruck benutzt

Mein Eindruck ist, dass das Verhalten an dieser Stelle nicht vollständig konsistent ist: offenbar werden in diesem Fall bei der Löschung nicht alle Abhängigkeiten überprüft.