Problem
You’re new to Oracle Database 12c and have read about the various table types available. For example, you can choose between heap-organized tables, index-organized tables, and so forth. You want to build a database application and need to decide which table type to use.
Solution
Oracle provides a wide variety of table types. The default table type is heap-organized. For most applications, a heap-organized table is an effective structure for storing and retrieving data. However, there are other table types that you should be aware of, and you should know the situations under which each table type should be implemented. Table 1 describes each table type and its appropriate use.
Table 1. Oracle Table Types and Typical Uses
Table Type/Feature | Description | Benefit /Use |
Heap-organized | The default Oracle table type and the most commonly used. | Use this table type unless you have a specific reason to use a different type. |
Temporary | Session private data, stored for the duration of a session or transaction; space is allocated in temporary segments. | Program needs a temporary table structure to store and modify data. Table data isn’t required after the session terminates. |
Index-organized (IOT) | Data stored in a B-tree index structure sorted by primary key. | Table is queried mainly on primary key columns; good for range scans, provides fast random access. |
Partitioned | A logical table that consists of separate physical segments. | Type used with large tables with tens of millions of rows; dramatically affects performance scalability of large tables and indexes. |
External | Tables that use data stored in operating system files outside of the database. | This type lets you efficiently access data in a file outside of the database (like a CSV or text file). External tables also provide an efficient mechanism for transporting data between databases. |
Materialized view (MV) | A table that stores the output of a SQL query; periodically refreshed when you want the MV table updated with a current snapshot of the SQL result set. | Aggregating data for faster reporting or replicating data to offload performance to a reporting database. |
Clustered | A group of tables that share the same data blocks. | Type used to reduce I/O for tables that are often joined on the same columns. Rarely used. |
Nested | A table with a column with a data type that is another table. | Seldom used. |
Object | A table with a column with a data type that is an object type. | Seldom used. |
How It Works
In most scenarios, a heap-organized table is sufficient to meet your requirements. This Oracle table type is a proven structure used in a wide variety of database environments. If you properly design your database (normalized structure) and combine that with the appropriate indexes and constraints, the result should be a well-performing and maintainable system.
Normally most of your tables will be heap-organized. However, if you need to take advantage of a non-heap feature (and are certain of its benefits), then certainly do so. For example, Oracle partitioning is a scalable way to build very large tables and indexes. Materialized views are a solid feature for aggregating and replicating data. Index-organized tables are efficient structures when most of the columns are part of the primary key (like an intersection table in a many-to-many relationship). And so forth.
Caution You shouldn’t choose a table type simply because you think it’s a cool feature that you recently heard about. Sometimes folks read about a feature and decide to implement it without first knowing what the performance benefits or maintenance costs will be. You should first be able to test and prove that a feature has solid performance benefits.