Oracle | Index Organized Tables

Oracle Index Organized Tables (IOT) Version 19c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Description Index Organized Tables are tables that, unlike heap tables, are organized like B*Tree indexes.

Note: From Jonathan Lewis on secondary indexes on IOTs

I think secondary indexes on IOTs need some careful testing. It's probably not an area that many people have used in a high-stress environment. There are two main issues:

The primary key is used in the secondary index instead of a rowid so for large primary keys, the index would be bigger than the equivalent index on a simple table.
The secondary index holds a 'guess' block address for the row it points to so that a query can go to the right block more cheaply. But if the row has moved (e.g. leaf block split) then the guess is wrong and is a cost, not a benefit. But this won't be a problem if your application is always adding data at the 'right-hand' edge of the index.

Depending of version, there are various features and limitations on what you can do with secondary indexes that you will have to trade, balance and test, if you go down that path.

Data Dictionary Objects

ALL_ALL_TABLES	CDB_TAB_COLS	TAB$
ALL_TABLES	COL$	USER_ALL_TABLES
ALL_TAB_COLS	DBA_ALL_TABLES	USER_TABLES
CDB_ALL_TABLES	DBA_TABLES	USER_TAB_COLS
CDB_TABLES	DBA_TAB_COLS

Create

Simple Create IOT CREATE TABLE <table_name> ( <column_name> <data type and precision>, <column_name> <data type and precision>, CONSTRAINT <constraint_name> PRIMARY KEY (<primary key constraint columns>)) ORGANIZATION INDEX;

CREATE TABLE labor_hour (

      WORK_DATE   DATE,

      EMPLOYEE_NO VARCHAR2(8),

      CONSTRAINT pk_labor_hour 

      PRIMARY KEY (work_date, employee_no))

      ORGANIZATION INDEX;

      

      SELECT table_name, iot_name, iot_type

      FROM user_tables;

      

      DROP TABLE labor_hour;

      

      SELECT object_name, original_name, type, related

      FROM user_recyclebin;

      

      FLASHBACK TABLE labor_hour TO BEFORE DROP;

      

      SELECT object_name, object_type

      FROM user_objects

      ORDER BY 1,2;

      

      ALTER INDEX "BIN$jzohHP0LRqusV3X3jtaDRQ==$0" RENAME TO pk_labor_hour;

Index Compressed IOT

Note: See limitations section at page bottom CREATE TABLE <table_name> ( <column_name> <data type and precision>, <column_name> <data type and precision>, CONSTRAINT <constraint_name> PRIMARY KEY (<primary key constraint columns>)) ORGANIZATION INDEX COMPRESS <number_of_columns>;

CREATE TABLE compressed_iot

      (owner, object_type, object_name,

      CONSTRAINT pk_compressed_iot

      PRIMARY KEY(owner, object_type, object_name))

      ORGANIZATION INDEX

      COMPRESS 2 AS

      SELECT owner, object_type, object_name

      FROM all_objects;

Complex IOT with Including Clause CREATE TABLE <table_name> ( <column_name> <data type and precision>, <column_name> <data type and precision>, CONSTRAINT <constraint_name> PRIMARY KEY (<primary key constraint columns>)) ORGANIZATION INDEX INCLUDING <column_name> PCTTHRESHOLD <pct_integer> OVERFLOW TABLESPACE <tablespace_name>;

CREATE TABLE labor_hour (

      WORK_DATE            DATE,

      EMPLOYEE_NO          VARCHAR2(8),

      SUMMIT_WORK_ORDER_NO VARCHAR2(7),

      DASH                 VARCHAR2(2),

      CLASS_CODE           VARCHAR2(6),

      PAYCODE              VARCHAR2(2),

      ASSIGNED_CREW_NUMBER VARCHAR2(5),

      TRANSFER_CREW_NUMBER VARCHAR2(5),

      REFERENCE_TYPE       VARCHAR2(1),

      REFERENCE_NUMBER     VARCHAR2(10),

      OVERTIME_CODE        VARCHAR2(1),

      SHIFT_DIFFERENTIAL   VARCHAR2(1) NOT NULL,

      HOURS                NUMBER(4,2) NOT NULL,

      MOD_USER_ID          VARCHAR2(30) DEFAULT USER,

      MOD_USER_DATE        DATE DEFAULT SYSDATE,

      CONSTRAINT pk_labor_hour 

      PRIMARY KEY (work_date, employee_no, summit_work_order_no, dash, class_code, paycode, assigned_crew_number, transfer_crew_number, reference_type, reference_number, overtime_code, shift_differential))

      ORGANIZATION INDEX

      INCLUDING hours

      PCTTHRESHOLD 10

      OVERFLOW TABLESPACE uwdata;

Complex IOT with Including Clause And Partitioning CREATE TABLE <table_name> ( <column_name> <data type and precision>, <column_name> <data type and precision>, CONSTRAINT <constraint_name> PRIMARY KEY (<primary key constraint columns>)) ORGANIZATION INDEX INCLUDING <column_name> OVERFLOW TABLESPACE <tablespace_name> PARTITION BY RANGE (<partitioning_column>) (<partition definitions>);

-- DDL for the tablespaces required for this demo are [here]

CREATE TABLE labor_hour (

      WORK_DATE            DATE,

      EMPLOYEE_NO          VARCHAR2(8),

      SUMMIT_WORK_ORDER_NO VARCHAR2(7),

      DASH                 VARCHAR2(2),

      CLASS_CODE           VARCHAR2(6),

      PAYCODE              VARCHAR2(2),

      ASSIGNED_CREW_NUMBER VARCHAR2(5),

      TRANSFER_CREW_NUMBER VARCHAR2(5),

      REFERENCE_TYPE       VARCHAR2(1),

      REFERENCE_NUMBER     VARCHAR2(10),

      OVERTIME_CODE        VARCHAR2(1),

      SHIFT_DIFFERENTIAL   VARCHAR2(1) NOT NULL,

      HOURS                NUMBER(4,2) NOT NULL,

      MOD_USER_ID          VARCHAR2(30) DEFAULT USER,

      MOD_USER_DATE        DATE DEFAULT SYSDATE,

      CONSTRAINT pk_labor_hour 

      PRIMARY KEY (work_date, employee_no, summit_work_order_no, dash, class_code, paycode, assigned_crew_number, transfer_crew_number, reference_type, reference_number, overtime_code, shift_differential))

      
      ORGANIZATION INDEX

      
      INCLUDING  hours

      
      OVERFLOW TABLESPACE uwdata

      PARTITION BY RANGE (work_date) (

      PARTITION yr06 VALUES LESS THAN (TO_DATE('01-JAN-2017', 'DD-MON-YYYY')) TABLESPACE part1,

      PARTITION yr07 VALUES LESS THAN (TO_DATE('01-JAN-2018', 'DD-MON-YYYY')) TABLESPACE part2,

      PARTITION yr08 VALUES LESS THAN (TO_DATE('01-JAN-2019', 'DD-MON-YYYY')) TABLESPACE part3,

      PARTITION yr99 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);

Mapping Table Clause

Specify MAPPING TABLE to instruct Oracle to create a mapping of local to physical ROWIDs and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table.

Oracle creates the mapping table in the same tablespace as its parent index-organized table. You cannot query, perform DML operations on, or modify the storage characteristics of the mapping table.

You cannot specify the mapping_table_clause for a partitioned index-organized table.

Create IOT with mapping table CREATE TABLE <table_name> ( <column_name> <data_type><precision>, <column_name> <data_type><precision>, CONSTRAINT <constraint_name> (<constraint_column_list)) ORGANIZATION INDEX MAPPING TABLE;

CREATE TABLE t (

      x INT,

      y INT,

      CONSTRAINT pk_t_iot PRIMARY KEY(x))

      ORGANIZATION INDEX

      MAPPING TABLE;

      

      col iot_map_table new_val iot_map

      

      SELECT 'SYS_IOT_MAP_' || object_id iot_map_table

      FROM user_objects

      WHERE object_name = 'T';

      

      desc &iot_map

      

      -- as rows are inserted they are mapped to mapping table

      

      col SYS_NC_01 format a20

      

      INSERT INTO t VALUES (1, 2);

      INSERT INTO t VALUES (2, 2);

      

      SELECT rowid, a.* FROM &iot_map a;

      

      -- on update logical row changes but mapping table row doesn't

      UPDATE t SET x = 3 WHERE x = 1;

      

      SELECT rowid, a.* FROM &iot_map a;

      

      -- create a bitmapped index

      CREATE BITMAP INDEX bix_t

      ON t(y);

Limitations

IOTs can not be created with virtual columns SQL> CREATE TABLE labor_hour ( 2 WORK_DATE DATE, 3 EMPLOYEE_NO VARCHAR2(8), 4 REG_TIME NUMBER, 5 OVER_TIME NUMBER, 6 TOT_TIME AS (REG_TIME + OVER_TIME), 7 CONSTRAINT pk_labor_hour 8 PRIMARY KEY (work_date, employee_no)) 9 ORGANIZATION INDEX; ORGANIZATION INDEX * ERROR at line 9: ORA-54008: expression column is not supported for an index organized table

IOTs can key compressed by not table compressed SQL> CREATE TABLE labor_hour ( 2 WORK_DATE DATE, 3 EMPLOYEE_NO VARCHAR2(8), 4 CONSTRAINT pk_labor_hour 5 PRIMARY KEY (work_date, employee_no)) 6 ORGANIZATION INDEX COMPRESS; Table created. SQL> SELECT compression 2 FROM user_tables 3 WHERE table_name = 'LABOR_HOUR'; COMPRESS -------- DISABLED

IOTs can not have reverse key indexes SQL> CREATE TABLE labor_hour ( 2 WORK_DATE DATE, 3 EMPLOYEE_NO VARCHAR2(8), 4 CONSTRAINT pk_labor_hour 5 PRIMARY KEY (work_date, employee_no)) REVERSE 6 ORGANIZATION INDEX; PRIMARY KEY (work_date, employee_no)) REVERSE * ERROR at line 5: ORA-14125: REVERSE/NOREVERSE may not be specified in this context