Home | Previous Page | Next Page   Dimensional Databases > Implementing a Dimensional Database (XPS) > Logging and Nonlogging Tables in Extended Parallel Server >

Choosing Table Types

The individual tables in a data-warehousing environment often have different requirements. To help determine the appropriate table type to use for your tables, answer the following questions:

Table 12 lists the properties of the six types of tables that Extended Parallel Server supports and shows how you can use external tables to load these types of tables. Use this information to select a table type to match the specific requirements of your tables.

Table 12. Characteristics of the Table Types for Extended Parallel Server
Type Permanent Logged Indexes Light Append Used Rollback Available Recoverable Restorable from Archive External Tables Load Mode
SCRATCH No No No Yes No No No Express or deluxe load mode
TEMP No Yes Yes Yes Yes No No Express or deluxe load mode
RAW Yes No No Yes No No No Express or deluxe load mode
STATIC Yes No Yes No No No No None
OPERATIONAL Yes Yes Yes Yes Yes Yes No Express or deluxe load mode
STANDARD Yes Yes Yes No Yes Yes Yes Deluxe load mode

Scratch and Temp Temporary Tables

Scratch tables are nonlogging temporary tables that do not support indexes, constraints, or rollback.

Temp tables are logged temporary tables, although they also support bulk operations such as light appends. (Express mode loads use light appends, which bypass the buffer cache. Light appends eliminate the overhead associated with buffer management but do not log the data.) Temp tables support indexes, constraints, and rollback.

Tip:
SELECT...INTO TEMP and SELECT...INTO SCRATCH statements are parallel across coservers, just like ordinary inserts. Extended Parallel Server automatically supports fragmented temporary tables across nodes when those tables are explicitly created with SELECT...INTO TEMP and SELECT...INTO SCRATCH.

Extended Parallel Server creates explicit temporary tables according to the following criteria:

Raw Permanent Tables

Raw tables are nonlogging permanent tables that use light appends. Express-mode loads use light appends, which bypass the buffer cache. You can load a raw table with express mode. For information about express-mode loads, see your IBM Informix: Administrator's Reference.

Raw tables support updates, inserts, and deletes but do not log them. Raw tables do not support index or referential constraints, rollback, recoverability, or restoration from archives.

Use raw tables for the initial data loading and scrubbing. Once these steps are completed, alter the table to a higher level. For example, if an error or failure occurs while you are loading a raw table, the resulting data is whatever was on the disk at the time of the failure.

In a data-warehousing environment, you might choose to create a fact table as a raw table when both of the following conditions are true:

Static Permanent Tables

Static tables are nonlogging, read-only permanent tables that do not support insert, update, and delete operations. When you anticipate no insert, update, or delete operations on the table, you might choose to create the table as a static table. With a static table, you can create and drop nonclustered indexes and referential constraints because they do not affect the data.

Static tables do not support rollback, recoverability, or restoration from archives. Their advantage is that the database server can use light scans and avoid locking when you execute queries because static tables are read-only.

Tip:
Static tables are important when you want to create a table that uses GK indexes because a static table is the only table type that supports GK indexes.

Operational Permanent Tables

Operational tables are logging permanent tables that use light appends and do not perform record-by-record logging. They allow fast update operations.

You can roll back operations or recover after a failure with operational tables, but you cannot restore them reliably from an archive of the log because the bulk insert records that are loaded are not logged. Use operational tables in situations where you derive data from another source so restorability is not an issue, but where you do not require rollback and recoverability.

You might create a fact table as an operational table because the data is periodically refreshed. Operational tables support express load mode (in the absence of indexes and constraints) and data is recoverable.

Standard Permanent Tables

A standard table in Extended Parallel Server is the same as a table in a logged database that you create with Dynamic Server. All operations are logged, record by record, so you can restore standard tables from an archive. Standard tables support recoverability and rollback.

If the update and refresh cycle for the table is infrequent, you might choose to create a standard table type, as you need not drop constraints or indexes during a refresh cycle. Building indexes is time consuming and costly, but necessary.

Tip:
Standard tables do not use light appends, so you cannot use express-load mode when you use external tables to perform the load.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]