Löschung von Spalten

Mir ist durchaus klar, dass sich ein großer Teil meiner Beiträge hier auf die (verkürzende und manchmal sinnentstellende) Paraphrase der Artikel von Jonathan Lewis beschränkt. Aber an einführenden Sätzen wie den folgenden aus dem Artikel Dropping Columns, den der Herr Lewis gerade bei AllThingsOracle veröffentlicht hat, komme ich einfach nicht vorbei:

One of the bugs that came up on 9th Jan was described as: “Bug 18700681 : DROP COLUMNS USING ALTER TABLE VERY SLOW”. My first thought when I saw this was that it wasn’t a bug it was expected behaviour (with the caveat that “very slow” might actually mean “even slower than the ‘very slow’ that I was expecting”).

Dass die Löschung von Spalten in einer sehr großen Oracle-Tabelle wenig Freude bereitet, gehört jedenfalls zu den Dingen, die mir auch schon aufgefallen sind. Meine Antworten darauf wären in der Regeln CTAS oder dbms_redefinition. Im AllThingsOracle-Artikel erfährt man dazu Folgendes:

• um eine Spalte zu entfernen, kann man sie als unused markieren oder via drop column vollständig entfernen.
• das "set unused" ist relativ billig, da es nur das data dictionary aktualisiert, dafür aber auch keinen Speicherplatz freigibt. Das "drop column" hingegen stellt eine komplette Reorganisation der Tabelle dar und ist entsprechend kostspielig.
• beide Operationen sind irreversibel. In aktuellen Oracle-Versionen könnte man eine Spalte auch als invisible markieren, was man als eine weniger endgültige zum "set unused" ansehen könnte.
• bestimmte Spalten kann man über "drop column" bzw. "set unused" nicht entfernen - etwa den partition key einer partitionierten Tabelle oder den primary key einer IOT, was inhaltlich recht unmittelbar einleuchtet.
• allerdings kann man die Spalten des primary key einer heap Tabelle löschen, was zur Löschung des PK-Index führt.
• ein "drop column" erfordert das Lesen aller Blocks der Tabelle. Werden gleichzeitig mehrere Spalten gelöscht, so wird jede Löschung als separates Update behandelt und erzeugt undo und redo.
• beim "drop column" wird ein exclusives lock auf der Tabelle erzeugt und die Operation in einer einzelnen großen Transaktion durchgeführt, sofern man nicht die checkpoint Klausel spezifiziert. Die checkpoint Option macht es möglich, eine zwischenzeitlich abgebrochene "drop column" Operation über ein "continue" fortzusetzen - allerdings ist die Tabelle nach dem Abbruch für DML und Queries nicht verfügbar.
• als grobe Orientierung bei der Löschung einzelner Spalten gilt (auch für nologging Tabellen):
• undo pro Datensatz: 100 byte + durchschnittliche Spaltengröße.
• redo pro Datensatz: 250 byte + durchschnittliche Spaltengröße.
• ein Test zeigt, dass die Löschung mehrerer Spalten zwar nur einen Tablescan hervorruft, aber undo und redo pro Spalte auftreten und sich summieren. In diesem Test liegt der Overhead für undo bei etwa 78 bytes pro Spalte und Datensatz. Für redo ergeben sich 234 bytes pro Datensatz und Spalte. An den Werten ändert sich nicht viel, wenn man die checkpoint-Klausel verwendet. Allerdings werden in diesem Fall mehrere undo Segmente verwendet.
• eine Spalte kann nicht gelöscht werden, wenn für die Tabelle basic compression im Einsatz ist. Mit "compress for OLTP" (aks "row store compress advanced") ist das hingegen möglich. Allerdings scheint in diesem Fall keine Löschung stattzufinden, sondern nur eine Anpassung im data dictionary.

Von meinen eingangs erwähnten Ideen CTAS und dbms_redefinition ist hier keine Rede. Ihr Vorteil könnte auch höchstens darin liegen, dass sie das Basisobjekt nicht so massiv sperren - andererseits würde DML während der CTAS-Operation (und dem zugehörigen Austausch der Objekte) die Strategie ohnhin witzlos machen.