【Oracle】INDEX 専用表領域の設計方法｜I/O 分散・CREATE INDEX TABLESPACE・既存インデックスの移動・命名規則まで解説

-- テーブル用表領域（データ格納用）
CREATE TABLESPACE app_data
    DATAFILE '/oracle/disk1/app_data01.dbf'
    SIZE 5G AUTOEXTEND ON NEXT 500M MAXSIZE 20G;

-- INDEX 専用表領域（別ディスクに配置が理想）
CREATE TABLESPACE app_index
    DATAFILE '/oracle/disk2/app_index01.dbf'
    SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 10G;

-- ユーザーに QUOTA を付与
ALTER USER app_user QUOTA UNLIMITED ON app_data;
ALTER USER app_user QUOTA UNLIMITED ON app_index;

-- インデックスを INDEX 専用表領域に作成
CREATE INDEX idx_emp_dept ON employees(department_id)
    TABLESPACE app_index;

-- PRIMARY KEY 制約のインデックスも表領域を指定可能
ALTER TABLE employees ADD CONSTRAINT pk_emp
    PRIMARY KEY (employee_id)
    USING INDEX TABLESPACE app_index;

-- CREATE TABLE 内で PK の表領域を指定
CREATE TABLE orders (
    order_id   NUMBER,
    order_date DATE,
    amount     NUMBER,
    CONSTRAINT pk_orders PRIMARY KEY (order_id)
        USING INDEX TABLESPACE app_index
) TABLESPACE app_data;

-- ユーザーのデフォルト表領域を確認
SELECT username, default_tablespace FROM dba_users WHERE username = 'APP_USER';

-- デフォルト表領域はテーブル用にして、
-- インデックスは CREATE INDEX 時に毎回 TABLESPACE を指定する運用が一般的

-- 既存インデックスを INDEX 専用表領域に移動
ALTER INDEX idx_emp_dept REBUILD TABLESPACE app_index;

-- ONLINE で移動（DML をブロックしない）
ALTER INDEX idx_emp_dept REBUILD TABLESPACE app_index ONLINE;

-- NOLOGGING + PARALLEL で高速移動（メンテナンス時間帯）
ALTER INDEX idx_emp_dept REBUILD TABLESPACE app_index NOLOGGING PARALLEL 4;
ALTER INDEX idx_emp_dept LOGGING;
ALTER INDEX idx_emp_dept NOPARALLEL;

-- テーブル用表領域にあるインデックスを INDEX 表領域に一括移動
BEGIN
    FOR rec IN (
        SELECT index_name FROM user_indexes
        WHERE tablespace_name = 'APP_DATA'  -- テーブル用表領域にある
          AND index_type IN ('NORMAL', 'BITMAP', 'FUNCTION-BASED NORMAL')
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name
            || ' REBUILD TABLESPACE APP_INDEX ONLINE';
    END LOOP;
END;
/

-- インデックスごとの表領域を確認
SELECT index_name, table_name, tablespace_name, index_type
FROM user_indexes
ORDER BY tablespace_name, table_name;

-- テーブルとインデックスの表領域を対比
SELECT t.table_name,
       t.tablespace_name AS table_ts,
       i.index_name,
       i.tablespace_name AS index_ts,
       CASE WHEN t.tablespace_name = i.tablespace_name THEN '*** SAME ***' END AS warning
FROM user_tables t
JOIN user_indexes i ON t.table_name = i.table_name
ORDER BY t.table_name, i.index_name;

-- warning = '*** SAME ***' のインデックスは分離されていない

-- 表領域ごとの合計サイズ（テーブル vs インデックスの比較）
SELECT tablespace_name, segment_type,
       COUNT(*) AS segment_count,
       ROUND(SUM(bytes)/1024/1024, 1) AS total_mb
FROM user_segments
WHERE segment_type IN ('TABLE', 'INDEX')
GROUP BY tablespace_name, segment_type
ORDER BY tablespace_name, segment_type;

-- スキーマ全体のインデックスサイズ
SELECT ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_index_gb
FROM user_segments
WHERE segment_type = 'INDEX';

-- テーブル別のインデックスサイズ
SELECT i.table_name,
       COUNT(*) AS idx_count,
       ROUND(SUM(s.bytes)/1024/1024, 1) AS idx_total_mb
FROM user_indexes i
JOIN user_segments s ON i.index_name = s.segment_name AND s.segment_type = 'INDEX'
GROUP BY i.table_name
ORDER BY idx_total_mb DESC;

# インデックスを除外してインポート → 後から INDEX 表領域に再作成
impdp hr/password \
    directory=DP_DIR \
    dumpfile=hr_schema.dmp \
    schemas=HR \
    exclude=INDEX

# 別環境で表領域名が異なる場合は REMAP_TABLESPACE
impdp hr/password \
    directory=DP_DIR \
    dumpfile=hr_schema.dmp \
    schemas=HR \
    remap_tablespace=PROD_IDX:DEV_IDX

# SEGMENT_ATTRIBUTES:N で元の表領域情報を無視
impdp hr/password \
    directory=DP_DIR \
    dumpfile=hr_schema.dmp \
    schemas=HR \
    transform=SEGMENT_ATTRIBUTES:N

-- テーブル用
CREATE TABLESPACE crm_data
    DATAFILE '/oracle/disk1/crm_data01.dbf'
    SIZE 5G AUTOEXTEND ON NEXT 500M MAXSIZE 30G;

-- インデックス用
CREATE TABLESPACE crm_index
    DATAFILE '/oracle/disk2/crm_index01.dbf'
    SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 15G;

-- ユーザーに QUOTA
ALTER USER crm_user QUOTA UNLIMITED ON crm_data;
ALTER USER crm_user QUOTA UNLIMITED ON crm_index;

-- テーブル作成（PK インデックスも INDEX 表領域に）
CREATE TABLE crm_user.customers (
    customer_id NUMBER,
    name        VARCHAR2(200),
    email       VARCHAR2(200),
    CONSTRAINT pk_customers PRIMARY KEY (customer_id)
        USING INDEX TABLESPACE crm_index
) TABLESPACE crm_data;

CREATE INDEX crm_user.idx_cust_email ON crm_user.customers(email)
    TABLESPACE crm_index;

-- (1) INDEX 表領域を作成
CREATE TABLESPACE app_index
    DATAFILE '/oracle/disk2/app_index01.dbf'
    SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 10G;

-- (2) QUOTA 付与
ALTER USER app_user QUOTA UNLIMITED ON app_index;

-- (3) 全インデックスを一括移動
BEGIN
    FOR rec IN (
        SELECT index_name FROM user_indexes
        WHERE tablespace_name = 'APP_DATA'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || rec.index_name
            || ' REBUILD TABLESPACE APP_INDEX ONLINE';
    END LOOP;
END;
/

-- テーブルと同じ表領域に配置されたインデックス = 分離できていない
SELECT t.table_name, i.index_name,
       t.tablespace_name AS table_ts,
       i.tablespace_name AS index_ts
FROM user_tables t
JOIN user_indexes i ON t.table_name = i.table_name
WHERE t.tablespace_name = i.tablespace_name
ORDER BY t.table_name;

-- 結果があれば REBUILD TABLESPACE で移動を検討