Choosing Table Extent Sizes

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Table Performance Considerations > Managing Extents >

Choosing Table Extent Sizes

When you create a table, you can specify extent sizes for the following storage spaces:

Data rows of a table in a dbspace
Each fragment of a fragmented table
Smart large objects in an sbspace

Extent Sizes for Tables in a Dbspace

When you create a table, you can specify the size of the first extent as well as the size of the extents to be added as the table grows. The following example creates a table with a 512-kilobyte initial extent and 100-kilobyte added extents:

CREATE TABLE big_one (...column specifications...)
   IN big_space
   EXTENT SIZE 512
   NEXT SIZE 100

The default value for the extent size and the next-extent size is eight times the disk page size on your system. For example, if you have a 2-kilobyte page, the default length is 16 kilobytes.

To change the size of extents to be added, use the ALTER TABLE statement. This change does not affect extents that already exist. The following example changes the next-extent size of the table to 50 kilobytes:

ALTER TABLE big_one MODIFY NEXT SIZE 50

The next-extent sizes of the following kinds of tables do not affect performance significantly:

A small table is defined as a table that has only one extent. If such a table is heavily used, large parts of it remain buffered in memory.
An infrequently used table is not important to performance no matter what size it is.
A table that resides in a dedicated dbspace always receives new extents that are adjacent to its old extents. The size of these extents is not important because, being adjacent, they perform as one large extent.

When you assign an extent size to these kinds of tables, the only consideration is to avoid creating large numbers of extents. A large number of extents causes the database server to spend extra time finding the data. In addition, an upper limit exists on the number of extents allowed. (Considering the Upper Limit on Extents covers this topic.)

No upper limit exists on extent sizes except the size of the chunk. The maximum size for a chunk is 4 terabytes. When you know the final size of a table (or can confidently predict it within 25 percent), allocate all its space in the initial extent. When tables grow steadily to unknown size, assign them next-extent sizes that let them share the dbspace with a small number of extents each. The following steps outline one possible approach.

To allocate space for table extents

Decide how to allocate space among the tables.
For example, you might divide the dbspace among three tables in the ratio 0.4: 0.2: 0.3 (reserving 10 percent for small tables and overhead).
Give each table one-fourth of its share of the dbspace as its initial extent.
Assign each table one-eighth of its share as its next-extent size.
Monitor the growth of the tables regularly with oncheck.

As the dbspace fills up, you might not have enough contiguous space to create an extent of the specified size. In this case, the database server allocates the largest contiguous extent that it can.

Extent Sizes for Table Fragments

When you fragment an existing table, you might want to adjust the next-extent size because each fragment requires less space than the original, unfragmented table. If the unfragmented table was defined with a large next-extent size, the database server uses that same size for the next-extent on each fragment, which results in over-allocation of disk space. Each fragment requires only a proportion of the space for the entire table.

For example, if you fragment the preceding big_one sample table across five disks, you can alter the next-extent size to one-fifth the original size. For more information about the ALTER FRAGMENT statement, see the IBM Informix: Guide to SQL Syntax. The following example changes the next-extent size to one-fifth of the original size:

ALTER TABLE big_one MODIFY NEXT SIZE 20

Extent Sizes for Smart Large Objects in Sbspaces

When you create a table, it is recommended that you use one of the following extent sizes for smart large objects in the sbspace:

Extent size that the database server calculates for you
Final size of the smart large object, as indicated by one of the following functions when you open the sbspace in an application program:
- The DataBlade API mi_lo_specset_estbytes function
  For more information on the DataBlade API functions to open a smart large object and set the estimated number of bytes, see the IBM Informix: DataBlade API Programmer's Guide.
- The ESQL/C ifx_lo_specset_estbytes function
  For more information on the ESQL/C functions to open a smart large object and set the estimated number of bytes, see the IBM Informix: ESQL/C Programmer's Manual.

For more information about sizing extents, see Sbspace Extent Sizes. For more information, see Monitoring Sbspaces.