Tables that the database server supports reside on one or more portions of a disk or disks. You control the placement of a table on disk when you create it by assigning it to a dbspace. A dbspace consists of one or more chunks. Each chunk corresponds to all or part of a disk partition. When you assign chunks to dbspaces, you make the disk space in those chunks available for storing tables or table fragments.
When you configure chunks and allocate them to dbspaces, you must relate the size of the dbspaces to the tables or fragments that each dbspace is to contain. To estimate the size of a table, follow the instructions in Estimating Table Size.
The database administrator (DBA) who is responsible for creating a table assigns that table to a dbspace in one of the following ways:
The most recent DATABASE or CONNECT statement that the DBA issues before issuing the CREATE TABLE statement sets the current database.
The DBA can fragment a table across multiple dbspaces, as described in Planning a Fragmentation Strategy, or use the ALTER FRAGMENT statement to move a table to another dbspace. The ALTER FRAGMENT statement provides the simplest method for altering the placement of a table. However, the table is unavailable while the database server processes the alteration. Schedule the movement of a table or fragment at a time that affects the fewest users. For a description of the ALTER FRAGMENT statement, see the IBM Informix: Guide to SQL Syntax.
Other methods exist for moving tables between dbspaces. A DBA can unload the data from a table and then move that data to another dbspace with the SQL statements LOAD and UNLOAD, as the IBM Informix: Guide to SQL Syntax describes. The database server administrator can perform the same actions with the onload and onunload utilities, as the IBM Informix: Migration Guide describes, or with the High-Performance Loader (HPL), as the IBM Informix: High-Performance Loader User's Guide describes.
Moving tables between databases with LOAD and UNLOAD, onload and onunload, or HPL involves periods in which data from the table is copied to tape and then reloaded onto the system. These periods present windows of vulnerability during which a table can become inconsistent with the rest of the database. To prevent the table from becoming inconsistent, you must restrict access to the version that remains on disk while the data transfers occur.
Depending on the size, fragmentation strategy, and indexes that are associated with a table, it can be faster to unload a table and reload it than to alter fragmentation. For other tables, it can be faster to alter fragmentation. You might have to experiment to determine which method is faster for a table that you want to move or repartition.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]