I have a table that takes up a lot of physical space (several terabytes) and has several million records. It contains LOB fields. If I want to add a new column, will the size of the table affect this? And how will the performance of this operation be affected, for example, by adding a default value for a new column or making it nullable/not null?

CodePudding user response：

When updating all the records in a table then the physical space of the table is usually more important than the number of rows. Most database operations on a million rows would only take a second, but if the database has to read a terabyte of data then that can obviously slow things down.

However, there are several optimizations that might make adding a column to a physically large table only take a second:

1. LOBs stored separately Since LOBs are stored in a separate segment, Oracle may not need to read them to make your change. If the non-CLOB columns are small, then this change will only require reading and modifying megabytes of data.
2. Default metadata optimization Columns created with a default value will not actually write that value to the data files. Oracle will simply keep track of the change in the metadata, which should take less than a second.