O'Reilly logo

Oracle Database 11gR2 Performance Tuning Cookbook by Ciro Fiorillo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 NULL, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required