Managing Extents

Do not allow a table to acquire a large number of extents because an upper limit exists on the number of extents allowed. Trying to add an extent after you reach the limit causes error -136 (No more extents) to follow an INSERT request.

To determine the upper limit on number of extents allowed for a table

Run the following oncheck option to obtain the physical address of the object (table or index fragment) for which you wish to calculate extent limits.
```
oncheck -pt databasename:tablename 
```
Figure 29 shows sample output for oncheck -pt.

Figure 29. oncheck -pt Output

TBLspace Report for stores7:wbyrne.sfe_enquiry 
Physical Address 7002c7 
Number of special columns 18 
Number of keys 0 
Number of extents 65 
Number of data pages 960

Split the physical address of the table or index fragment into the chunk# (leading digits) and page# (last 5 digits), and then run oncheck -pP chunk# page#, specifying the arguments as hexadecimal numbers by prefixing with 0x.
In Figure 29, the Physical Address is 7002c7. Therefore, chunk# is 0x007 (or 0x07) and the page# is 0x0002c (or 0x2c) in the following oncheck command:
```
oncheck -pP 0x7 0x2c 
```
Figure 30 shows sample output for oncheck -pp 0x7 0x2c.
Figure 30. oncheck -pp chunk# page# Output
```
addr   stamp  nslots flag type  frptr frcnt next prev 
7002c7 112686 5      2    PARTN 828   1196  0    0
slot ptr len flg 
1     24  92   0 
2    116  40   0 
3    156 144   0 
4    300   0   0 
5    300 528   0
```
From the output of the oncheck -pP, take the number below the frcnt column and divide it by 8 to obtain the number of additional extents you can have for this object.
In the sample oncheck -pP in Figure 30, the frcnt column shows the value 1196. The following calculation shows the number of additional:
```
Additional_extents = trunc (frcnt / 8)
                   = trunc (1196 / 8)
                   = 149
```
To obtain the maximum number of extents, add the value in the Number of extents line in the oncheck -pt output to the Additional_extents value, as the following formula shows:
```
Maximum_number_extents = Additional_extents + 
   Number_of_extents
```
In the sample oncheck -pt in Figure 29, the Number of extents line shows the value 65. The following calculation shows the maximum number of extents for this table:
```
Maximum_number_extents = 149 + 65 = 214
```

To help ensure that the limit is not exceeded, the database server performs the following actions:

Checking for Extent Interleaving

When two or more growing tables share a dbspace, extents from one tblspace can be placed between extents from another tblspace. When this situation occurs, the extents are said to be interleaved. Interleaving creates gaps between the extents of a table, as Figure 31 shows. Performance suffers when disk seeks for a table must span more than one extent, particularly for sequential scans.

Try to optimize the table-extent sizes to allocate contiguous disk space, which limits head movement. Also consider placing the tables in separate dbspaces.

Check periodically for extent interleaving by monitoring chunks. Execute oncheck -pe 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.

Eliminating Interleaved Extents

Reorganizing Dbspaces and Tables to Eliminate Extent Interleaving

You can rebuild a dbspace to eliminate interleaved extents so that the extents for each table are contiguous, as Figure 32 illustrates. The order of the reorganized tables within the dbspace is not important, but the pages of each reorganized table should be contiguous so that no lengthy seeks are required to read the table sequentially. When the disk arm reads a table nonsequentially, it ranges only over the space that table occupies.

The LOAD statement re-creates the tables with the same properties they had before, including the same extent sizes.

You can also unload a table with the onunload utility and reload the table with the companion onload utility. For further information about selecting the correct utility or statement to use, see the IBM Informix: Migration Guide.

Creating or Altering an Index to Cluster

Depending on the circumstances, you can eliminate extent interleaving if you create a clustered index or alter an index to cluster. When you use the TO CLUSTER clause of the CREATE INDEX or ALTER INDEX statement, the database server sorts and reconstructs the table. The TO CLUSTER clause reorders rows in the physical table to match the order in the index. For more information, see Clustering .

The TO CLUSTER clause eliminates interleaved extents under the following conditions:

To display the location and size of the blocks of free space, execute the oncheck -pe command.

To use the TO CLUSTER clause of the ALTER INDEX statement

For each table in the chunk, drop all fragmented or detached indexes except the one that you want to cluster.
Cluster the remaining index with the TO CLUSTER clause of the ALTER INDEX statement.
This step eliminates interleaving the extents when you rebuild the table by rearranging the rows.
Re-create all the other indexes.
You compact the indexes in this step because the database server sorts the index values before it adds them to the B-tree.

You do not need to drop an index before you cluster it. However, the ALTER INDEX process is faster than CREATE INDEX because the database server reads the data rows in cluster order using the index. In addition, the resulting indexes are more compact.

To prevent the problem from recurring, consider increasing the size of the tblspace extents. For more information, see the IBM Informix: Guide to SQL Tutorial.

Using ALTER TABLE to Eliminate Extent Interleaving

If you use the ALTER TABLE statement to add or drop a column or to change the data type of a column, the database server copies and reconstructs the table. When the database server reconstructs the entire table, it rewrites the table to other areas of the dbspace. However, if other tables are in the dbspace, no guarantee exists that the new extents will be adjacent to each other.

Reclaiming Unused Space Within an Extent

Once the database server allocates disk space to a tblspace as part of an extent, that space remains dedicated to the tblspace. Even if all extent pages become empty after you delete data, the disk space remains unavailable for use by other tables.

You might want to resize a table that does not require the entire amount of space that was originally allocated to it. You can reallocate a smaller dbspace and release the unneeded space for other tables to use.

As the database server administrator, you can reclaim the disk space in empty extents and make it available to other users by rebuilding the table. To rebuild the table, use any of the following SQL statements:

Reclaiming Space in an Empty Extent with ALTER INDEX

If the table with the empty extents includes an index, you can execute the ALTER INDEX statement with the TO CLUSTER clause. Clustering an index rebuilds the table in a different location within the dbspace. All the extents associated with the previous version of the table are released. Also, the newly built version of the table has no empty extents.

For more information about the syntax of the ALTER INDEX statement, see the IBM Informix: Guide to SQL Syntax. For more information about clustering, see Clustering .

Reclaiming Space in an Empty Extent with the UNLOAD and LOAD Statements or the onunload and onload Utilities

If the table does not include an index, you can unload the table, re-create the table (either in the same dbspace or in another one), and reload the data with the UNLOAD and LOAD statements or the onunload and onload utilities.

For further information about selecting the correct utility or statement, see the IBM Informix: Migration Guide. For more information about the syntax of the UNLOAD and LOAD statements, see the IBM Informix: Guide to SQL Syntax.

Releasing Space in an Empty Extent with ALTER FRAGMENT

You can use the ALTER FRAGMENT statement to rebuild a table, which releases space within the extents that were allocated to that table. For more information about the syntax of the ALTER FRAGMENT statement, see the IBM Informix: Guide to SQL Syntax.