Matching Oracle 12c Table Types to Business Requirements

Selecting Oracle 12c Table TypesProblem

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.

 

Вас заинтересует / Intresting for you:

Selecting Table Data Types App...
Selecting Table Data Types App... 673 views Ded Sun, 19 Aug 2018, 12:33:49
Oracle Database and Instance d...
Oracle Database and Instance d... 507 views Masha Thu, 21 Jun 2018, 18:23:39
Oracle Database and Security: ...
Oracle Database and Security: ... 871 views sepia Mon, 26 Nov 2018, 12:20:58
How to ensure the Oracle datab...
How to ensure the Oracle datab... 909 views Aaz24 Mon, 26 Nov 2018, 12:20:58

Comments on Matching Oracle 12c Table Types to Business Requirements

Be the first to comment
Please login to comment