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,
    COUNTRY_NAME VARCHAR2(40) NOT NULL,
    COUNTRY_SUBREGION VARCHAR2(30) NOT NULL,
    COUNTRY_SUBREGION_ID NUMBER NOT NULL,
    COUNTRY_REGION VARCHAR2(20) NOT NULL,
    COUNTRY_REGION_ID NUMBER NOT NULL,
    COUNTRY_TOTAL VARCHAR2(11) NOT NULL,
    COUNTRY_TOTAL_ID...