Disk storage for tables is defined in units called extents. Each extent is a block of physically contiguous pages in the dbspace. Even if the dbspace is made up of more than one chunk, each extent is allocated entirely in a single chunk so that its pages can remain contiguous. The database server allocates additional extents as the table increases in size. For information about the structure of disk space for tables and indexes, refer to the IBM Informix: Extended Parallel Server Administrator's Reference.
Contiguous disk pages are important for good performance. When the data pages are contiguous, disk-arm motion is minimized when the database server reads the rows sequentially. The use of extents is a compromise to accommodate the following factors:
Because table sizes are not always predictable, table space cannot be preallocated. The database server adds extents only when they are needed, but all the pages in each extent are contiguous for better performance. If the database server creates a new extent adjacent to the previous extent, it treats both extents as a single extent.
When you create a table, you can specify the size of the first extent as well as the size of each extent to be added as the table grows.
An extent is a set of contiguous disk pages within a single chunk. Extents cannot extend across chunk boundaries. The size of an extent is limited only by the size of the chunk. If you expect tables in a dbspace to grow steadily to an unknown size, assign next-extent sizes large enough so that each dbspace that contains a table fragment will contain a small number of extents. Not only does a small number of extents improve data-access efficiency, but the number of extents permitted for a table is limited.
The default value for the extent size and the next-extent size is four times the disk page size on your database server. If your database server uses the default page size of 4-kilobytes, the default extent size and the next-extent size is 16 kilobytes. This is a small extent size for tables and table fragments in a large database. Make sure that you specify extent sizes that are appropriate for the size to which you expect the table to grow in the next few months.
The database limits the number of extents permitted for a single table, as explained in Limiting the Number of Extents for a Table.
The following example creates a table with a 512-kilobyte initial extent and 200-kilobyte next extents:
CREATE TABLE big_one (...column specifications...) IN big_space EXTENT SIZE 512 NEXT SIZE 200
To change the next-extent size for an existing table, use the ALTER TABLE statement. This change has no effect on extents that already exist. The following example reduces the next-extent size of the table to 100 kilobytes:
ALTER TABLE big_one MODIFY NEXT SIZE 100
When you fragment an existing unfragmented table, reduce its next-extent size because each fragment requires less space than the original 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 might result in over-allocation of disk space.
For example, if you fragment the preceding big_one sample table across five coservers, you can alter the next-extent size to one-fifth the original size. For more information on 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 100-kilobyte size:
ALTER TABLE big_one MODIFY NEXT SIZE 20
The next-extent sizes of the following kinds of tables are not as important for performance:
If the table has an attached index and the index is stored in the same dbspace, newly allocated extents probably are not contiguous.
Some very small tables might be placed in a single dbspace. The following steps suggest one approach to assigning extents for such tables.
If the dbspace does not contain contiguous space to create an extent of the specified size, the database server allocates as large an extent as it can.
To specify the extent size for some temporary tables created by the database server and for temporary tables created by SELECT INTO TEMP statements, use the set environment variables listed in the following table.
For example, to set the first extent size of a database server-created temporary table to 64 kilobytes and the next extent size to 128 kilobytes, execute the following SET ENVIRONMENT statements:
SET ENVIRONMENT TEMP_TAB_EXT_SIZE "64";
SET ENVIRONMENT TEMP_TAB_NEXT_SIZE "128";
The minimum value for TEMP_TAB_EXT_SIZE and TEMP_TAB_NEXT_SIZE is four times the page size on the system. If you specify a size below the minimum values, the database server sets the default first and next extent sizes to four pages.
The maximum value for TEMP_TAB_EXT_SIZE and TEMP_TAB_NEXT_SIZE is the maximum value of a chunk size. However, the logical limit depends on the maximum disk space available in a single dbspace of a dbslice. For example, if the maximum size available for a dbspace is 24000 kilobytes, then the maximum value for TEMP_TAB_EXT_SIZE is 24000 kilobytes.
For more information on using the SET ENVIRONMENT statement, see the IBM Informix: Guide to SQL Syntax.
The database server limits the number of extents that a table can acquire. The limit depends on several factors related to the page size specified in the configuration file.
If table fragments occupy dedicated dbspaces, the number of extents is not important. Newly allocated extents are contiguous with previously allocated extents. The database server treats contiguous as a single extent. However, if a table has an attached index, the index pages occupy a separate tablespace within the dbspace assigned to the table fragment. The index tablespace requires a separate set of extents, which might cause the index and table fragment extents to be interleaved.
The database server performs the following actions to help ensure that it does not exceed the table limits:
vcspace = 8 * vcolumns + 136 tcspace = 4 * tcolumns ixspace = 12 * indexes ixparts = 4 * icolumns extspace = pagesize - ( vcspace + tcspace + ixspace + ixparts +84) maxextents = extspace/8
Use the page size specified by the PAGESIZE configuration parameter.
vcolumns is the number of columns that contain simple-large-object and VARCHAR data.
tcolumns is the number of columns in the table.
indexes is the number of indexes on the table.
icolumns is the number of columns named in those indexes.
The table can have no more than maxextents extents.
If you receive error -136 (No more extents) after an INSERT request, the database server needs to add an extent to a table but cannot do so. Either not enough disk space is available in the dbspace, or the table occupies the maximum number of extents that is allowed. For information about how to correct the problem, refer to IBM Informix: Error Messages.
interleaved extents occur when extents of one tablespace are located between the extents of other tablespaces. Interleaving creates distances between the extents of all tables, as Figure 6 shows.
Interleaved extents occur in the following circumstances:
Performance suffers when disk seeks for a table must skip over extents, particularly for sequential scans. Try to optimize the table-extent sizes, which limits head movement, or consider placing tables in separate dbspaces.
To check for extent interleaving, monitor chunks. Execute onutil CHECK SPACE DISPLAY to obtain the physical layout of information in the chunk. The following information appears:
This output is useful for determining the degree of extent interleaving. If the database server cannot allocate an extent in a chunk despite an adequate number of free pages, the chunk might be badly interleaved.
In addition, you might be able to eliminate interleaved extents if you create a cluster index on the table, or if you use the ALTER TABLE statement to add or drop a column or to change the data type of a column. These methods are successful only if enough contiguous space exists in the dbspace to rebuild the table in contiguous extents.
After the database server has allocated disk space to a tablespace as part of an extent, that space remains allocated to the tablespace. Even if all extent pages are empty after data is deleted or truncated, other tables cannot use their disk space.
You can resize a table that does not require the entire amount of space that was originally allocated to it. Create a new table with smaller extent sizes, unload the data from the larger table, load it into the smaller table, and drop the original larger table. When you drop the original, larger table, you release its space.
To reclaim the disk space in empty extents without dropping and recreating the table, rebuild the table with either of the following methods:
You can use the ALTER FRAGMENT INIT statement to rebuild a table, which releases space in the extents that were allocated to that table.
Consider, however, that if the table contains data, ALTER FRAGMENT INIT might require significant logging and create an exceptionally long transaction. For more information, refer to the IBM Informix: Guide to SQL Syntax.
You can use the ALTER TABLE statement that invokes the standard-alter algorithm to rebuild a table, which releases space in the extents that were allocated to that table. For information about the standard-alter method, see Standard ALTER TABLE.
For information about using the ALTER TABLE statement to reclaim empty extent space, seeAltering a Table Definition. For more information, refer to the IBM Informix: Guide to SQL Syntax.