The question is about the (sophisticated) database middleware of an application. Changes to the tables are done with a tool from the application. The goal is to modify the structure of a table (add/remove columns, modify column properties, add/remove indexes and so on) with a minimum of impact on the continuously running application. Dropping a column takes a lot of time, so we already work around it.
To drop a column, the tool currently does someting like:
• delete the column from the app's internal data dictionary (in order to no longer use that column)
• alter table modify (col_name null); -- remove restrictions on the column
• alter table rename column col_name to deleteme_001; -- "delete" the column
• update table set deleteme_001 = null; -- simply said; but in fact, it is done in small portions.
Up to now, everything works fine, and usually there is not even a single error mesage popping up in the application. But the "deleteme" columns still have to be removed on a sunday...
The next step is the "drop column", and I would expect the following:
• I can add a column to a table of 100.000.000 records within zero seconds. Thus, I assume NULL values are no part of the row structure.
• As a consequence, a "drop column" must a) make sure no new values are being inserted into that column, b) check that no record contains a value in that column, and then c) just drop it.
But when I drop such a "deleteme" column, there is a lot of redo log being written, and it seems to consume too much time to execute it on a tuesday afternoon. It just seems to: there is no lock on the table, the application is countinously working. The only issue is a huge workload. (Timing and logging tested in versions: 8.1.7, 9.2.0.1.0, 10.1.0.3.0, 10.2.0.1.0, 11.1.0.6.0, 11.2.0.3.0, 12.1.0.2.0, thereby suffering no lock was tested in versions: 11.1.0.6.0, 11.2.0.3.0, 12.1.0.2.0, and speeding up "drop column" by setting the values to NULL in advance was tested in 8.1.7 & 9.2.0.1.0.)
==> Do you have a more precise clue of what is going on when dropping a column? (Is there any documentation you can recommend?)
In order to drop that column within (nearly) zero seconds, the database would have to know that all column values from all rows are NULL. I tried, but the database has not ingeniously interpreted the constraint:
• alter table ... add constraint ... check (deleteme_001 is null) enable novalidate; -- zero seconds for "a)" above
• alter table ... modify constraint ... enable validate; -- resonable time for "b)" above, comparable to a full select
• alter table ... drop column deleteme_001; -- much time for "c)", comparable to a full update
==> Do you know about a clue that I can give to the database such that it knows it can simply drop th column without updating all rows?
==> I guess I should combine the current method with Tom Kytes' recommendation to set the column UNUSED, and no longer care about it. Correct?
The latter would mean: a) rename to "deleteme_001", b) set the values to NULL in portions, c) set the column unused.
Notes:
• If you doubt that it is about the database having to know that all column values from all rows are NUL, please do not pull the second question apart, but simply limit yourself to the first question. Thank you.
• Since the only issue is a huge workload, this question is not a cry for help. It is about learning about the database.
• In the past and now, I have done about two days of research before posting. That doesn't make me know much, but at least I have tried seriously.