EXTENT SIZE Options

The EXTENT SIZE options can define the size of storage extents allocated to the table.

This syntax fragment is part of the Storage options.

EXTENT SIZE Options


Element	Description	Restrictions	Syntax
first_kilobytes	Length in kilobytes of the first extent for the table; default is 16 kilobytes.	Must return a positive number; maximum is the chunk size	Expression
next_kilobytes	Length in kilobytes of each subsequent extent; default is 16 kilobytes.	Must return a positive number; maximum is the chunk size	Expression

Usage

The minimum length of first_kilobytes (and of next_kilobytes) is four times the disk-page size on your system. For example, if you have a 2-kilobyte page system, the minimum length is 8 kilobytes.

If the CREATE TABLE (or the CREATE TEMP TABLE) statement includes no IN dbspace clause, no EXTENT SIZE specification, and no NEXT SIZE specification, no storage is allocated for the table until at least one data row is inserted into it. The default size of the first extent is either 16 kilobytes or 4 pages.

The next example specifies a first extent of 20 kilobytes and allows the rest of the extents to use the default size:

CREATE TABLE emp_info
   (
   f_name     CHAR(20),
   l_name     CHAR(20),
   position   CHAR(20),
   start_date DATETIME YEAR TO DAY,
   comments   VARCHAR(255)
   )
EXTENT SIZE 20;

If a table contains no data, you can use the ALTER TABLE MODIFY EXTENT SIZE or ALTER TABLE MODIFY NEXT SIZE statements of SQL to change the size of the first extent and of the next extent of the empty table. These operations are not supported, however, for tables that contain one or more rows. For more information about these options to the ALTER TABLE statement, see MODIFY EXTENT SIZE and MODIFY NEXT SIZE clause.

If you need to revise the extent sizes of a table, you can modify the first extent and next-extent sizes in the generated schema files of an unloaded table. For example, to make a database more efficient, you might unload a table, modify the extent sizes in the schema files, and then create and load a new table. For information about how to optimize extents, see your HCL OneDB™ Administrator's Guide.