Data storage concepts

When converting from Informix® to Oracle® the aim is to try to preserve as much of the data storage information as possible in the process. The most important storage decisions made for Informix database objects (like initial sizes and physical placement) can be applied to the Oracle database.

Storage concepts are quite similar in Informix and in Oracle, but the names are different.

This table compares Informix storage concepts to Oracle storage concepts:

Table 1. Physical units of storage
Informix ORACLE

The largest unit of physical disk space is a "chunk", which can be allocated either as a cooked file ( I/O is controlled by the OS) or as raw device (=UNIX® partition, I/O is controlled by the database engine). A "dbspace" uses at least one "chunk" for storage.

You must add "chunks" to "dbspaces" in order to increase the size of the logical unit of storage.

One or more "data files" are created for each "tablespace" to physically store the data of all logical structures. Like Informix "chunks", a "data file" can be an OS file or a raw device.

You can add "data files" to a "tablespace" in order to increase the size of the logical unit of storage or you can use the AUTOEXTEND option when using OS files.

A "page" is the smallest physical unit of disk storage that the engine uses to read from and write to databases.

A "chunk" contains a certain number of "pages".

The size of a "page" must be equal to the operating system's block size.

At the finest level of granularity, Oracle stores data in "data blocks" which size corresponds to a multiple of the operating system's block size.

You set the "data block" size when creating the database.

An "extent" consists of a collection of contiguous "pages" that the engine uses to allocate both initial and subsequent storage space for database tables.

When creating a table, you can specify the first extent size and the size of future extents with the EXTENT SIZE and NEXT EXTENT options.

For a single table, "extents" can be located in different "chunks" of the same "dbspace".

An "extent" is a specific number of contiguous "data blocks", obtained in a single allocation.

When creating a table, you can specify the first extent size and the size of future extents with the STORAGE() option.

For a single table, "extents" can be located in different "data files" of the same "tablespace".

Table 2. Logical units of storage
Informix ORACLE
A "table" is a logical unit of storage that contains rows of data values. Same concept as Informix.
A "database" is a logical unit of storage that contains table and index data. Each database also contains a system catalog that tracks information about database elements like tables, indexes, stored procedures, integrity constraints and user privileges. Same concept as Informix, but one Oracle instance can manage only one database, in the same way that Informix does.

Database tables are created in a specific "dbspace", which defines a logical place to store data.

If no dbspace is given when creating the table, Informix defaults to the current database dbspace.

Database tables are created in a specific "tablespace", which defines a logical place to store data.

If no tablespace is given when creating the table, Oracle defaults to the user's default tablespace.

The total disk space allocated for a table is the "tblspace ", which includes "pages" allocated for data, indexes, blobs, tracking page usage within table extents.

Do not confuse the Informix "tblspace" concept and Oracle "tablespaces".

A "segment" is a set of "extents" allocated for a certain logical structure. There are four different types of segments, including data segments, index segments, rollback segments and temporary segments.
Table 3. Other concepts related to storage
Informix ORACLE
When initializing an Informix engine, a "root dbspace" is created to store information about all databases, including storages information (chunks used, other dbspaces, etc.) Each Oracle database has a "control file" that records the physical structure of the database, like the database name, location and names of "data files" and "redo log" files, and time stamp of database creation.

The "physical log" is a set of continuous disk pages where the engine stores "before-images" of data that has been modified during processing.

The "logical log" is a set of "logical-log files" used to record logical operations during online processing. All transaction information is stored in the logical log files if a database has been created with transaction log.

Informix combines "physical log" and "logical log" information when doing fast recovery. Saved "logical logs" can be used to restore a database from tape.

A "rollback segment" records the actions of SQL transactions that can be rolled back, and it records the data as it existed before an operation in a transaction.

The "redo log files" hold all changes made to the database, in case the database experiences an instance failure.

Each database has at least two "redo log files".

Redo entries record data that can be used to reconstruct all changes made to the database, including the rollback segments stored in the database buffers of the SGA. Therefore, the online redo log also protects rollback data.