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.
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 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.
Extended Parallel Server creates explicit temporary tables according to the following criteria:
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 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.
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.
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.