【Oracle】索引構成表（IOT）完全ガイド｜ORGANIZATION INDEX・主キー検索の高速化・ヒープ表との比較まで解説

-- 通常のヒープ表（参考: ORGANIZATION HEAP がデフォルト）
CREATE TABLE orders_heap (
    order_id   NUMBER       NOT NULL,
    customer_id NUMBER      NOT NULL,
    order_date DATE         NOT NULL,
    total_amount NUMBER(12, 2),
    status     VARCHAR2(20),
    CONSTRAINT orders_heap_pk PRIMARY KEY (order_id)
);
-- ヒープ表: orders_heap セグメント + orders_heap_pk インデックスセグメントの2つが作成される

-- 同じ構造を IOT として作成する（ORGANIZATION INDEX を追加するだけ）
CREATE TABLE orders_iot (
    order_id   NUMBER       NOT NULL,
    customer_id NUMBER      NOT NULL,
    order_date DATE         NOT NULL,
    total_amount NUMBER(12, 2),
    status     VARCHAR2(20),
    CONSTRAINT orders_iot_pk PRIMARY KEY (order_id)
) ORGANIZATION INDEX;
-- IOT: orders_iot の B-Tree セグメント 1 つだけ（主キーインデックスを兼ねる）

-- DBA_TABLES で確認する（IOT_TYPE 列）
SELECT table_name, iot_type, organization
FROM DBA_TABLES
WHERE owner = 'HR' AND table_name IN ('ORDERS_HEAP', 'ORDERS_IOT');
-- IOT: IOT_TYPE = 'IOT' , ORGANIZATION = 'INDEX'
-- ヒープ: IOT_TYPE = NULL, ORGANIZATION = 'HEAP'

-- PCTTHRESHOLD: ブロックサイズの何% を超えた行はオーバーフローセグメントへ
-- INCLUDING: 指定した列までを IOT 本体に格納し、残りをオーバーフローへ

CREATE TABLE product_catalog_iot (
    product_id      NUMBER           NOT NULL,
    category_id     NUMBER           NOT NULL,
    product_name    VARCHAR2(200)    NOT NULL,
    unit_price      NUMBER(10, 2),
    description     VARCHAR2(4000),   -- 長い列はオーバーフローに格納したい
    CONSTRAINT prod_catalog_pk PRIMARY KEY (product_id)
)
ORGANIZATION INDEX
    PCTTHRESHOLD 20           -- ブロックの20%を超えた行はオーバーフローへ
    INCLUDING product_name    -- product_id と product_name までは IOT 本体に格納
    OVERFLOW TABLESPACE users_tbs;  -- オーバーフローセグメントの表領域

-- ポイント: 主キーでの検索頻度の高い列は IOT 本体に残す
-- → product_id（主キー）・product_name・unit_price は IOT 本体
--   description（長い列）はオーバーフローに格納される

-- オーバーフローセグメントの存在を確認する
SELECT table_name, iot_type
FROM DBA_TABLES
WHERE table_name LIKE '%PRODUCT_CATALOG%' AND owner = 'HR';
-- IOT_TYPE = 'IOT'（本体）と 'IOT_OVERFLOW'（オーバーフロー）が表示される

-- IOT（orders_iot）に customer_id での検索用二次索引を作成する
CREATE INDEX orders_iot_cust_ix ON orders_iot(customer_id);
-- 二次索引は ROWID の代わりに論理 ROWID（主キー値 + guess）を格納する

-- 二次索引の実行計画（参考）
EXPLAIN PLAN FOR
SELECT order_id, order_date FROM orders_iot WHERE customer_id = 1001;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'TYPICAL'));
-- ヒープ表では: INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID
-- IOT では:    INDEX RANGE SCAN（二次索引）→ INDEX UNIQUE SCAN（IOT 本体の主キー）
--              論理 ROWID の guess が外れた場合は追加の IOT ルックアップが発生する

-- DBA_INDEXES で IOT の二次索引を確認する
SELECT index_name, table_name, index_type
FROM DBA_INDEXES
WHERE table_name = 'ORDERS_IOT' AND owner = 'HR';
-- INDEX_TYPE: 'NORMAL/REV'（ヒープ表のインデックス）に対し
--             IOT の二次索引は 'NORMAL'（ROWID が論理ROWIDになっている）