Types of Tables

Regular table:

A regular table (generally referred to as a “table”) is the most commonly used form of storing user data. This is the default table and is the main focus of discussion in this lesson. A database administrator has very limited control over the distribution of rows in a table. Rows can be stored in any order depending on the activity in the table.

Partitioned table:

A partitioned table enables the building of scalable applications. It has the following characteristics:

  • A partitioned table has one or more partitions, each of which stores rows that have been partitioned using range partitioning, hash partitioning, composite partitioning, or list partitioning.
  • Each partition in a partitioned table is a segment and can be located in a different tablespace.
  • Partitions are useful for large tables that can be queried or manipulated using several processes concurrently.
  • Special commands are available to manage partitions within a table.

Index-organized table:

An index-organized table is like a heap table with a primary key index on one or more of its columns. However, instead of maintaining two separate storage spaces for the table and a B-tree index, an index-organized table maintains a single B-tree containing the primary key of the table and other column values. An overflow segment may exist due to the PCTTHRESHOLD value being set and the result of longer row lengths requiring the overflow area.

Index-organized tables provide fast key-based access to table data for queries involving exact matches and range searches.

Also, storage requirements are reduced because key columns are not duplicated in the table and index. The remaining non-key columns are stored in the index unless the index entry becomes very large; in that case, the Oracle server provides an OVERFLOW clause to handle the problem.

 

Clustered table:

A clustered table provides an optional method for storing table data. A cluster is made up of a table or group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together.

Clusters have the following characteristics:

  • Clusters have a cluster key, which is used to identify the rows that need to be stored together.
  • The cluster key can consist of one or more columns.
  • Tables in a cluster have columns that correspond to the cluster key.
  • Clustering is a mechanism that is transparent to the applications using the tables. Data in a clustered table can be manipulated as though it were stored in a regular table.
  • Updating one of the columns in the cluster key may migrate the row.
  • The cluster key is independent of the primary key. The tables in a cluster can have a primary key, which may be the cluster key or a different set of columns.
  • Clusters are usually created to improve performance. Random access to clustered data may be faster, but full table scans on clustered tables are generally slower.
  • Clusters renormalize the physical storage of tables without affecting the logical structure.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: