Improve the performance of operations that attach and detach fragments

When you use ALTER FRAGMENT ATTACH and DETACH statements to add or remove a large amount of data in a very large table, you can take steps to improve the performance of the ATTACH and DETACH operations.

The ALTER FRAGMENT DETACH statement provides a way to delete a segment of the table data rapidly. Similarly, the ALTER FRAGMENT ATTACH statement provides a way to load large amounts of data incrementally into an existing table by taking advantage of the fragmentation technology. However, the ALTER FRAGMENT ATTACH and ALTER FRAGMENT DETACH statements can take a long time to execute when the database server rebuilds indexes on the surviving table.

The database server provides performance optimizations for the ATTACH and DETACH operations of the ALTER FRAGMENT statement that reuse the indexes on the surviving tables. By eliminating the index build during the ATTACH or DETACH operation,
  • this reduces the time required for the ALTER FRAGMENT ATTACH and ALTER FRAGMENT DETACH statements to execute,
  • and improves the availability of the table.

The ALTER FRAGMENT operation requires exclusive access and exclusive locks on all of the tables involved in the operation. When you use the FORCE_DDL_EXEC environment option to specify a time limit for the database server to force out any transactions in other sessions that have opened (or that hold locks on) the tables involved in an ALTER FRAGMENT ON TABLE operation, also use the SET LOCK MODE TO WAIT statement to specify that number of seconds as the limit for waiting.

If the database server is unable to get exclusive access and exclusive locks on the table because of DDL transactions in concurrent sessions, the server will start rolling back the transactions that are open or that have locks on the table, until the specified time limit is reached. You might want to enable the FORCE_DDL_EXEC option and issue the SET LOCK MODE TO WAIT statement on a busy system, perhaps one that runs 24 hours a day, if you do not want to wait for transactions in concurrent sessions to close before you can alter a fragment.