Online Redefinition Enhancements In Oracle Database 11g

• Online table redefinition supports the following:

–      Tables with materialized views and view logs

–      Triggers with ordering dependency

• Online redefinition does not systematically invalidate dependent objects.

Oracle Database 11g supports online redefinition for tables with materialized views and view

logs. In addition, online redefinition supports triggers with the FOLLOWS or PRECEDES clause,which establishes an ordering dependency between the triggers.

In previous database versions, all directly and indirectly dependent views and PL/SQL packages

would be invalidated after an online redefinition or other DDL operations.

These views and PL/SQL packages would automatically be recompiled whenever they are next invoked. If there are a lot of dependent PL/SQL packages and views, the cost of the revalidation or recompilation can be significant.

In Oracle Database 11g, views, synonyms, and other table-dependent objects (with the exception

of triggers) that are not logically affected by the redefinition, are not invalidated.

So, for example,

If referenced column names and types are the same after the redefinition, then they are not

invalidated.

This optimization is “transparent,” that is, it is turned on by default.

Another example: If the redefinition drops a column, only those procedures and views that

reference the column are invalidated. The other dependent procedures and views remain valid.

Note that all triggers on a table being redefined are invalidated (as the redefinition can potentially change the internal column numbers and data types), but they are automatically revalidated with the next DML execution to the table.

I hope you all have enjoyed reading this article. Comments are welcome....