Choosing Table Types

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:

Does the table require indexes?
What constraints does the table need to define?
What is the refresh and update cycle on the table?
Is the table a read-only table?
Does the table need to be logged?

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:

If the query that you use to populate the Temp or Scratch table produces no rows, the database server creates an empty, unfragmented table.
If the rows that the query produces do not exceed 8 kilobytes, the temporary table resides in only one dbspace.
If the rows exceed 8 kilobytes, Extended Parallel Server creates multiple fragments and uses a round-robin fragmentation scheme to populate them.

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:

The fact table does not need to specify constraints and indexes, which are enforced by some different mechanisms.
Creating and loading the fact table is not a costly job. The fact tables could be useful but not critical for decision support, and if data is lost you can easily reload the table.

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.