Choosing Table Types

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

Choosing Table Types

The database server provides specific table types for specific purposes. The table type that you choose depends on the kind of queries and transactions that are run on the tables.

STANDARD, RAW, STATIC, and OPERATIONAL tables are permanent tables. They are accessible by any session that has the required access privileges. To remove a permanent table, you must explicitly drop it. TEMP and SCRATCH tables are temporary tables. They are accessible only by the session that creates them and are dropped automatically when the session exits.

The default table type is STANDARD, which corresponds to a permanent table in a logged database. If you issue the CREATE TABLE statement without specifying the table type, you create a STANDARD table.

Tip:

To change the default permanent table type for a session, execute the SET TABLE_TYPE statement and specify the new default table type. To change the default temporary type, execute the SET TEMP TABLE_TYPE statement and specify SCRATCH.

Table 3 lists the available table types and summarizes their major features and capabilities.

Table 3. Available Table Types
Type	Permanent	Logged	Indexes	Light Append	Rollback
STANDARD	Yes	Yes	Yes	No	Yes
RAW	Yes	No	Yes	Yes	No
STATIC	Yes	No	Yes	No	No
OPERATIONAL	Yes	Yes	Yes	Yes	Yes
TEMP	No	Yes*	Yes	Yes	Yes*
SCRATCH	No	No	Yes	Yes	No
	If no triggers are defined on the table * For information about logged TEMP tables, see Temporary Tables That User Applications Create

To change a permanent table from one type to another, use the ALTER TABLE statement. Table-type conversion has following general restrictions:

Before you convert a table type to RAW, you must drop all constraints.
Before you convert a table to STANDARD, you must perform a level-0 backup.
You cannot alter a TEMP or SCRATCH table or convert a permanent table to a temporary table.

The following sections describe the use of each table type, listing specific restrictions.

Using STANDARD Tables

STANDARD tables are the default table type. Activity that affects a STANDARD table is always logged, and can be recovered. All insert, delete, and update operations are permitted.

STANDARD tables do not permit light append, which is used for efficient bulk loading of data. Instead, data in a STANDARD table must be loaded row by row. This feature is not a disadvantage in a real-time OLTP environment in which users update or add rows one or a few at a time. Because STANDARD tables are logged, the recoverability provided by the rollback and fast recovery capabilities means that data changes for these tables cannot be lost. Before converting a RAW or STATIC table to standard type, make a level-0 backup of the table.

Specify the lock mode appropriately for a STANDARD table, depending on how it is used. In addition, make sure that SELECT statements and update cursors are executed at an appropriate isolation level. For information about setting lock modes for tables, see Specifying a Table Lock Mode. For descriptions of the isolation levels and their use, refer to the IBM Informix: Guide to SQL Tutorial. For information about appropriate isolation levels for various kinds of applications, see Review the Isolation Level.

Using RAW Tables

RAW tables are used primarily to load data from external tables. RAW tables use light appends, but changes are not logged. RAW tables do not support referential constraints, or rollback. Whether fast recovery or restoration from a backup is possible depends on several factors. For more information, refer to the chapters in the IBM Informix: Extended Parallel Server Administrator's Guide that refer to data storage locations and fast recovery.

To benefit from light append and eliminate the overhead of checking constraints and building indexes when you load data from an external table in Express mode, use RAW tables. Light-append operations append rows to a table and bypass the buffer cache to avoid buffer-management overhead. Because rows are appended to the table, available space in existing table pages is not reused. The table is locked exclusively during an express load so that no other user can access the table during the load. To reuse the table space created by removing old data, load new data in Deluxe mode.

After you load data into a RAW table, use the ALTER TABLE statement to convert the table from RAW to a different type. Before you convert a RAW table to a STANDARD table, perform a level-0 backup.

Using STATIC Tables

STATIC tables permit read-only access. For this reason, they are not locked or logged, which reduces overhead and speeds queries and transactions. Use STATIC tables for stable data, such as postal codes, city and state combinations.

Although inserts, updates, or deletes are not permitted, indexes and referential constraints are allowed. The database server can use light scans for STATIC tables, as described in Light Scans.

Only STATIC tables can have Generalized Key (GK) indexes, which are specialized indexes used for specific carefully tuned queries. GK indexes are rarely useful for ad hoc queries. If the database server uses a GK index on a table to process a query, it cannot use any other indexes on that table. If you change the type of a table from static to any other type, you must drop all GK indexes on it. For more information, see Generalized-Key Indexes.

When a transaction accesses a STATIC table, Dirty Read isolation is appropriate because the table is read-only and its contents do not change.

Using OPERATIONAL Tables

OPERATIONAL tables are used primarily to load data extracted from active OLTP database systems to create tables for various kinds of DSS applications. OPERATIONAL tables permit updates, inserts, and deletes, and are logged to permit rollback or recovery after system failure.

Load data in Express mode using light append if an OPERATIONAL table does not have any indexes. Light appends reduce recoverability and restorability, but restorability is less important if the table is used to load derived data. OPERATIONAL tables can be recovered from a backup only if data was not loaded with light append after the most recent level-0 backup.

Load data into OPERATIONAL tables in Deluxe mode, which can update existing indexes, if the cost of completely rebuilding table indexes is too high. Unique constraint checking is also allowed.

For information about loading data with external tables, refer to the IBM Informix: Extended Parallel Server Administrator's Reference. For information, see Light Appends.

Using Temporary Tables

You might create temporary tables for the following purposes, among others:

To reduce sorting scope for queries
To select an ordered subset of table rows that more than one query requires
To create other tables that can easily be derived from permanent table data

Although temporary tables are session specific, queries run by the same session can use temporary tables created earlier in the session. When the session exits, all temporary tables are dropped.

For information about creating and managing disk space for temporary tables and other temporary use, see Managing Temporary Space.

Temporary Tables That User Applications Create

Applications or users can create temporary tables with the SQL statements CREATE TEMPORARY TABLE or CREATE SCRATCH TABLE statement or with the SELECT INTO TEMP or SELECT INTO SCRATCH clause of an SQL query.

Temporary tables created with the TEMP keyword can be either logged or unlogged. Unlogged tables avoid the overhead of logging, but cannot be recovered by roll back. Logged temporary tables must be stored in logging dbspaces instead of in temporary dbspaces.

Create temporary tables in either of the following ways:

To select data into a temporary table without creating the temporary table first, use the INTO SCRATCH or INTO TEMP clause of the SELECT statement, as the following example shows.
```
SELECT * FROM customer 
   WHERE custno > 3500
   INTO SCRATCH temp_table;
```
When you select rows into a TEMP or SCRATCH table, you cannot specify an explicit fragmentation scheme. The database server creates one temporary table fragment on each coserver that hosts a source table fragment.

Temporary tables created with the SELECT...INTO Scratch statement are not logged. If you do not need to roll back data in a temporary table, use SELECT...INTO SCRATCH statements. Temporary tables created with the SELECT... INTO TEMP statement are logged by default and are created in logging dbspaces. Both SCRATCH and TEMP tables can be indexed.
To create a temporary table definition that you can populate later, use the CREATE TEMP TABLE or the CREATE SCRATCH TABLE statement.
When you create a temporary table, you can specify a fragmentation method and fragment location. Tables that you create with the TEMP keyword must be stored in standard non-temporary dbspaces unless you use the WITH NO LOG clause. The following SQL statement creates a nonlogging temporary table that is fragmented by hash in a temporary dbslice:
```
CREATE TEMP big_bills (cust_no CHAR(6), fname CHAR(15),
lname CHAR(25), addr1 CHAR(30)...)
   WITH NO LOG
   FRAGMENT BY HASH(cust_no)
   IN dbsl1_temp;
```
To populate the temporary table, use the following SELECT statement:
```
SELECT * FROM customer
   WHERE bill_amt > 1500
   INTO big_bills;
```
To create a logging TEMP table, specify a non-temporary dbspace or dbslice where it should be created. You cannot create a logging TEMP table in a temporary dbspace.

Temporary tables that you create with the TEMP keyword can have defined constraints.

Temporary tables and indexes on temporary tables are session specific and are visible only to the session that creates them. When the session ends, its temporary tables and indexes are removed.

For detailed information about creating temporary tables, refer to the IBM Informix: Guide to SQL Syntax.

Temporary Tables That the Database Server Creates

When the database server executes queries that contain clauses such as SORT or GROUP BY or when it creates an index, it creates a temporary table for intermediate output.

For information about specifying the extent size for database server-created tables, see Specifying the Extent Size for System-Managed Temporary Tables.

You do not control the location of temporary tables that the database server creates automatically to process queries. The database server places these temporary tables in the dbspaces or dbslices as specified by the setting of the DBSPACETEMP configuration parameter or environment variable. For information about setting DBSPACETEMP, see Specifying Temporary Space Availability.