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.
Syntax and an example of partitioned index-organized table
Highlight 1 A primary key is required for an index-oriented table.
Highlight 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. Se5Oracle SQL Reference document for details.
Highlight 3 Include optional storage clauses here such as TABLESPACE, PCTFREE, and PCTUSED.
Highlight 4 Define the partitions here. Remember that the column list must be the same as the PRIMARY KEY column list. 

CREATE TABLE tablename
(column1 datatype,
 column2 …,
 CONSTRAINT constraintname PRIMARY KEY (column list))
 ORGANIZATION INDEX PCTTHRESHOLD n
 physical storage clauses
 PARTITION BY RANGE (column list)
  PARTITION partname1 VALUES LESS THAN (values list),
  PARTITION partname2 …);

Example of index organized table 
CREATE TABLE CUSTOMER_ORDER
(ORDER_ID NUMBER,
 CUST_ID NUMBER,
 TOTAL_ORDER NUMBER,
 ORDER_DATE DATE,
 CONSTRAINT CUSTOMER_ORDER_PK PRIMARY KEY (ORDER_ID))
 ORGANIZATION INDEX PCTTHRESHOLD 25
 TABLESPACE USERS
 PCTFREE 20
 PARTITION BY RANGE (ORDER_ID)
 (PARTITION LOW_PART VALUES LESS THAN (1000),
  PARTITION MID_PART VALUES LESS THAN (50000),
  PARTITION HIGH_PART VALUES LESS THAN (MAXVALUE));


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
    The following section discusses how to create a partitioned index-organized table.

Arrange SQL command in order

You have completed the following SQL command that creates the SOLAR_SYSTEM table, a partitioned, index-oriented table. 
CREATE TABLE SOLAR_SYSTEM
(STAR_ID NUMBER, GALAXY_ID NUMBER, 
TOTAL_PLANETS NUMBER,  DISCOVERY_DATE DATE,
CONSTRAINT SOLAR_SYSTEM_PK PRIMARY KEY (STAR_ID))
ORGANIZATION INDEX PCTTHRESHOLD 33
TABLESPACE USERS PCTFREE 10
PARTITION BY RANGE (STAR_ID)
(PARTITION LOW_PART VALUES LESS THAN (19999),
PARTITION MED_PART VALUES LESS THAN (999999),
PARTITION HIGH_PART VALUES LESS THAN (MAXVALUE))

  • Reduces Scope of Maintenance Operations
    Equally important is the fact that partitioning substantially reduces the scope of maintenance operations and increases the availability of your data. You can perform all maintenance operations, such as backup, recovery, and loading, on a single partition. This flexibility makes it possible to handle extremely large data structures while still performing those maintenance operations in a reasonable amount of time. In addition, if you must recover one partition in a table for some reason, the other partitions in the table can remain online during the recovery operation. If you have been working with other databases that don’t offer the same type of partitioning, you may have tried to implement a similar functionality by dividing a table into several separate tables and then using a UNION SQL command to view the data in several tables at once. Partitioned tables give you all the advantages of having several identical tables joined by a UNION command without the complexity that implementation requires.

Partition Table and index identically

To maximize the benefits of partitioning, it sometimes makes sense to partition a table and an index identically so that both the table partition and the index partition map to the same set of rows. You can automatically implement this type of partitioning, which is called equipartitioning, by specifying an index for a partitioned table as a LOCAL index. Local indexes simplify maintenance, since standard operations, such as dropping a partition, will work transparently with both the index partition and the table partition.
  • Partitioning Features 10g, 11g, 12c
    Oracle has continued to increase the functionality of partitioning features. Since Oracle Database 10g Release 2, you can reorganize individual partitions online, the maximum number of partitions increased from
    1. (64 KB - 1) to (128 KB - 1), and
    2. query optimization using partition pruning improved.
    Oracle Database 11g further improved partition pruning, enabled applications to control partitioning, and added a Partition Advisor that can help you to understand when partitioning might improve the performance of your Oracle Database.
    Oracle Database 12c has added the ability to use partial indexes for a partitioned table. This capability means that you do not have to index all partitions in a table. You can indicate that a particular partition should not have an index, which means that there will not be a local index, or that partition will be excluded from the global index. You can turn indexing on or off for any individual partition.


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

SEMrush Software 2 SEMrush Banner 2