Extent Size Options

The Extent Size options can define the size of storage extents allocated to the index.
Extent Size Options

1?  EXTENT SIZE first_kilobytes?  NEXT SIZE next_kilobytes
Element Description Restrictions Syntax
first_kilobytes Length in kilobytes of the first extent for the index Must return a positive number; maximum is the chunk size, in kilobytes Expression
next_kilobytes Length in kilobytes of each subsequent extent Same as for first_kilobytes 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 you need to revise the extent sizes of an index, you can modify the extent and next-extent sizes in the generated schema files of an unloaded table. For example, to make a database more efficient, you might drop an index, modify the extent sizes in the schema files, and then create a new index. For information about how to optimize extents, see your HCL OneDB™ Administrator's Guide.

Only extent size values that you explicitly assign as extent sizes for the new index are stored in the system catalog. The value that you specify in the EXTENT SIZE option to the CREATE INDEX statement is stored in the fextsize column of the sysindices system catalog table, and the value that you specify in the NEXT SIZE option is stored in the nextsize column of the same table. If you omit these options, however, the database server stores a value of zero ( 0 ) in those system catalog columns, rather than the default value that it calculates and allocates for the first extent or the next extent of the index.

Example of an index defined with explicit extent sizes

The following program fragment creates a new table and defines two nonfragmented indexes on the table. 
CREATE TABLE IF NOT EXISTS t (a INT, b INT);
CREATE INDEX  IF NOT EXISTS idx1 ON t(a) EXTENT SIZE 32 NEXT SIZE 32;
CREATE INDEX  IF NOT EXISTS idx2 ON t(b);

Here the definition of idx1 specifies 32 kilobytes as explicit extent sizes. The second index, idx2, has default extent sizes that the system calculates. The two CREATE INDEX statements produce system catalog descriptions of these indexes that include these extent size entries:
  • The sysindices.fextent and sysindices.nextent column values are each 32 for idx1.
  • The sysindices.fextent and sysindices.nextent column values are each 0 for idx2.

Here the 0 values for idx2 indicate that no explicit extent sizes were specified (rather than indicating that no storage space was allocated).