MODIFY EXTENT SIZE

Use the MODIFY EXTENT SIZE clause with the ALTER TABLE statement to change the size of the first extent of a table in a dbspace.

You cannot use the MODIFY EXTENT SIZE clause to change the size of the first extent:

  • of a table in a blobspace
  • of external tables, virtual tables, or system catalog tables
  • in the tblspace tblspace

This syntax fragment is part of the ALTER TABLE statement.

MODIFY EXTENT SIZE Clause

1  MODIFY EXTENT SIZE kilobytes
Element Description Restrictions Syntax
kilobytes Length (in kilobytes) assigned here to the first extent for this table Specification cannot be a variable, and (4(page size)) < kilobytes < (chunk size) Expression

The minimum extent size is 4 times the disk-page size. For example, on a system with 2-kilobyte pages, the minimum length is 8 kilobytes. The maximum length is the chunk size.

The following example specifies an extent size of 32 kilobytes: 
ALTER TABLE customer MODIFY EXTENT SIZE 32;

When you change the size of the first extent, the database server records the change in the system catalog and on the partition page, but only makes the actual change when the table is rebuilt or a new partition or fragment is created.

For example, if a table has a first extent size of 8 kilobytes and you use the ALTER TABLE statement to change this to 16 kilobytes, the server does not drop the current first extent and recreate it with the new size. Instead, the new first extent size of 16 kilobytes takes effect only when the server rebuilds the table after actions such as creating a cluster index on the table or detaching a fragment from the table.

If a TRUNCATE TABLE statement without the REUSE option is executed before the ALTER TABLE statement with the MODIFY EXTENT SIZE clause, there is no change in the size of the current first extent.

If an existing table in a dbspace has data in it, the first and next extents are already allocated for the table and you will not be able to change the size of the first or next extent. If you want to change the size of existing extents, you must drop the table, recreate it with a storage clause indicating the desired size, and load the data again.

You can change the size of the first and next extent at the same time. The following example specifies changing the size of the first and next extent:

ALTER TABLE customer MODIFY EXTENT SIZE 32 NEXT SIZE 32

The first and next extent sizes are recorded in the PNSIZES logical log record.