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

This syntax fragment is part of the Storage options.
Read syntax diagramSkip visual syntax diagram

   '-EXTENT SIZE--first_kilobytes-'   

   '-NEXT SIZE--next_kilobytes-'   

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


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:
   f_name     CHAR(20),
   l_name     CHAR(20),
   position   CHAR(20),
   start_date DATETIME YEAR TO DAY,
   comments   VARCHAR(255)

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 Informix® Administrator's Guide.

Copyright© 2019 HCL Technologies Limited