RelationalDBDesignRelationalDBDesign





Partitioning Tables  «Prev  Next»
Lesson 2Partitioning index-organized Tables
ObjectiveDescribe how to partition an index-organized Table

Partitioning index-organized Tables

New to Oracle is 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. 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.

Syntax

Use the CREATE TABLE command to create a partitioned, index-oriented table. Look at the following MouseOver by clicking on the "Open Window" link for the syntax and an example of creating a partitioned table that is also an index-organized table.
Click the Open Window link.
A primary key is required for an index-oriented table

Syntax Example Partitioned Index Organized Table


Restrictions

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.