Deferred Table Creation

The with deferred_allocation parameter for the create table command lets you defer page allocation for a table. The use of deferred tables can improve performance for applications that create numerous tables, but use only a small number of them. Tables are called “deferred” until SAP ASE allocates their pages.

System tables include entries for deferred tables. These entries allow you to create objects associated with deferred tables such as views, procedures, triggers, and so on.

SAP ASE performs page allocation for deferred tables when it inserts the first row (called table materialization). Access to the table before the first insert, such as selects, deletes or updates, functions that report space usage, or enforce referential integrity constraints during DML on other tables, behave as if the table is empty. That is, a select against a deferred table produces an empty result set. Although you can create indexes on deferred tables, the page allocation for these indexes is deferred until SAP ASE materializes the table.

• Deferred Table Creation at the Database Level
  Use the ‘deferred table allocation' database option to configure the database to defer page allocation for all subsequently created user tables.
• Create Deferred Tables
  Use the create table . . . with deferred_allocation parameter to create deferred tables.
• Explicitly Materialize Deferred Tables
  Use alter table . . . immediate_allocation to explicitly materialize a deferred table.
• Identify Deferred Tables
  sp_help includes information about deferred tables in the object_status column.
• Roll Back for Deferred Tables
  If the materialization of a deferred table is part of a transaction that gets rolled back, SAP ASE does not roll back any page allocations that have been performed for the deferred table.
• Command Behavior in Deferred Tables
  Most commands work similarly on deferred tables and empty tables.