Home | Previous Page | Next Page   Managing Tables and Indexes > Planning and Managing Tables >

Managing Disk Space for Tables

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.

Choosing the Initial and Next Extent Sizes

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:

Tip:
Databases and database tables are required to contain increasingly large amounts of data. It is not unusual for a table in a data warehouse to contain several hundred megabytes of data. For such tables, create dbslices designed to contain a single table and fragment the table appropriately into the dbslices. For information, see Planning Table and Index Fragmentation.

Some very small tables might be placed in a single dbspace. The following steps suggest one approach to assigning extents for such tables.

To allocate space for table extents if a dbspace contains more than one table
  1. Decide how to allocate space among the tables in the dbspace. For example, you might divide the dbspace among three tables so that one table has 40 percent, another table has 20 percent, and a third table has 30 percent, with 10 percent reserved for small tables and overhead.
  2. Assign each table a quarter of its share of the dbspace as its initial extent.
  3. Assign each table an eighth of its share as its next-extent size.
  4. Monitor the growth of the tables regularly with onutil. For more information on how to use the onutil utility, refer to the IBM Informix: Extended Parallel Server Administrator's Reference.

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.

Tip:
If you expect to store more than about 20,000 tables or table fragments in a single dbspace, to allow the database server to manage the required overhead more efficiently, use the FRAGMENTS clause when you create the dbspace For an example, see Creating Dbslices for Temporary Tables and Sort Files.

Specifying the Extent Size for System-Managed Temporary Tables

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.

TEMP_TAB_EXT_SIZE
specifies the number of kilobytes for the first extent size
TEMP_TAB_NEXT_SIZE
specifies the number of kilobytes for the next extent size
DEFAULT
resets values to system defaults

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.

Limiting the Number of Extents for a Table

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:

To calculate the upper limit on extents for a particular table

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.

Tip:
If tables overflow the allocated dbspaces, use the onutil ALTER DBSLICE...ADD DBSPACE command to add dbspaces to a dbslice. Then use the SQL statement ALTER FRAGMENT to redistribute the table and index fragments across all dbspaces in the dbslice.

Reducing Extent Interleaving

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.

Figure 6. Interleaved Table Extents
begin figure description - This figure is described in the surrounding text. - end figure description

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.

Tip:
To reduce the likelihood of interleaved extents, define tables with appropriately large extent sizes.

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.

To eliminate interleaved extents
  1. Drop all indexes on the tables.
  2. Unload the tables into external tables, as described in the IBM Informix: Administrator's Guide.
  3. Drop all tables.
  4. Redefine all of the tables with appropriate extent sizes.
  5. Reload the tables sequentially.
  6. For each table, recreate indexes, run UPDATE STATISTICS, and create a level-0 backup.

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.

Reclaiming Unused Extent Space

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:

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]