Partitioning Tables  «Prev  Next»

Lesson 2Partitioning index-organized Tables
ObjectiveDescribe how to partition an index-organized Table in Oracle

Partitioning index-organized Tables

Oracle has the capability to partition an index-organized table[1]. Partitioning an index-organized table results in a table whose rows are stored in the exact order by primary key and are also segmented into individual sets of rows. This can greatly reduce response time of a query that uses the primary key in its WHERE clause.
The following graphic illustrates the concept of dividing an index-organized table into partitions. In the graphic, the CUSTOMER table is an index-organized table.

Arrange Rows by Primary Key

This means that all the rows are arranged in order by primary key (in this case, the ORDER_ID). The CUSTOMER table is divided into three partitions:
  1. LOW_PART,
  2. MED_PART, and
  3. HIGH_PART.

Partitioning Syntax

Use the CREATE TABLE command to create a partitioned, index-oriented table. Look at the following diagram and the corresponding syntax which gives an example of creating a partitioned table that is also an index-organized table.
Index Organized Table
  1. A primary key is required for an index-oriented table
  2. This clause tells Oracle that the table is an index-oriented table. There are some optional parameters that can be added to this clause that we are not displaying. See Oracle SQL Reference document for details.
  3. Include optional storage clauses here such as TABLESPACE, PCTFREE, and PCTUSED
  4. Define the partitions here. Remember that the column list must be the same as the PRIMARY KEY column list.

Syntax Example Partitioned Index Organized Table

Restrictions on Partitioning an index-organized Table

There are two restrictions on partitioning an index-organized table:
  1. The index-organized table must be partitioned according to values of its primary key.
  2. The table cannot contain any columns of data types LOB or VARRAY.

An index-organized table that is not partitioned can include LOB and VARRAY data types. The next lesson covers partitioning an object table.

Partition Table using Lob Data

Click the link below to create a partitioned index-organized table.
Arrange SQL Command In Order

[1]Index-organized table: A table that is stored in the database in physical order by its primary key.