Oracle Database 11gR2 Performance Tuning Cookbook

Migrating to index organized tables

There are situations in which we access a table only—or mainly—using the primary key value. Situations such as a code lookup table, or a table containing inverted indexes, fit well in this definition.

In this recipe, we will see how to combine a heap table and a B-tree index in what is called an index organized table, and what benefits—and caveats—we have in performance when adopting this structure to store our data.

How to do it...

The following steps will demonstrate index organized tables:

1. Connect to the database as user SH:

   CONNECT sh@TESTDB/sh
   

2. Create an index organized table based on the COUNTRIES table of the SH schema:

   CREATE TABLE IOT_COUNTRIES (
    COUNTRY_ID NUMBER NOT NULL,
    COUNTRY_ISO_CODE CHAR(2) NOT ...