対象: Oracle Database 19c。索引の作成方法を、索引種別・一意性・格納先・同時更新の条件に分けて説明します。後続のPostgreSQL・MySQL・SQL Serverの比較例はOracle構文ではありません。本文中の全Oracle SQLは未実行です。

Oracleでは構文上有効なDDLの実行前と成功後に暗黙COMMITが発生します。DDLが失敗しても先行DMLが確定する場合があります。作成・削除・再構築を読取確認や、通常ROLLBACKでまとめて取り消せる試行として実行しないでください。

この記事の要点

基本: CREATE INDEX idx_name ON table(col1, col2) 一意索引: CREATE UNIQUE INDEXは索引キーの一意性を強制。PRIMARY KEY/UNIQUE制約の宣言とは別で、NULL条件も確認 タイプ指定: USING btree / hash / gin / gist (PostgreSQL) 部分インデックス (PG): WHERE deleted_at IS NULL で条件付き オンライン作成: OracleのONLINE、MySQLのonline DDL、PGのCONCURRENTLYは別機能。対象・版・同時更新の条件を確認し、無ロック・無停止は保証しない

CREATE INDEX 基本構文

通常の表の列索引の簡略表記です。角括弧は省略可能を表し、UNIQUEとBITMAPはどちらか一方です。両方を省略する通常の列索引はB-treeです。クラスタ・パーティション・domain索引等の全構文図ではありません。

CREATE [ UNIQUE | BITMAP ] INDEX 索引名
  ON 表名 (列名, ...)
  [ TABLESPACE 表領域名 ];

次は旧v1のtest_table教材を条件付きで復元した独立例です。既存test_table(col2)、適切な作成権限・容量が前提で、Oracle実機では未実行です。旧v2/v3のtest_indは索引名の異なる教材で、両方を順に作成する手順ではありません。

-- Oracle Database 19c; independent example; NOT executed.
-- Requires existing test_table(col2), appropriate privileges and space.
CREATE INDEX ix_test_01 ON test_table(col2);

次は基本例の代替であり連続実行しません。app_index_tsは既存の承認済み表領域を表す仮名です。所有者のquotaまたは該当権限、空き領域・一時領域を確認します。非partition索引のCREATEでTABLESPACEを省略すると索引所有schemaの既定表領域となり、表と同じとは限りません。新表領域作成・無制限権限の付与は含みません。

-- Oracle Database 19c; alternative to basic.sql; NOT executed.
-- app_index_ts is an existing approved tablespace placeholder.
CREATE INDEX ix_test_01 ON test_table(col2) TABLESPACE app_index_ts;

TABLESPACEは速度・一意性・ONLINEの保証ではありません。REBUILDで省略した場合の従来の表領域を使う規則とは別です。一意索引は索引キーの一意性を強制しますが、PRIMARY KEY/UNIQUE制約の宣言ではありません。Oracleの外部キー参照先にはPRIMARY KEY/UNIQUE制約が必要です。適格な既存索引を制約の強制に再利用する場合もあります。

通常の昇順B-treeは全キー列がNULLの行を格納しませんが、一部だけNULLの複合キーとは区別します。昇順の単一列UNIQUEは複数NULLを許し、複合一意キーでは同じ位置がNULLで残る値も同じ行は重複になり得ます。全NULLキー、PRIMARY KEYのNULL禁止、降順一意索引の複数NULLを重複として扱う規則を混同しません。格納とoptimizer採用も別です。

保存した製品別の独立例です。「UNIQUE制約と等価」は不正確で、索引と制約は別定義です。「左から順」は全アクセスパスの必要条件ではありません。SHOW INDEX・psql・pg_indexesはOracleの確認SQLではありません。同名のCREATE/DROPを連続実行せず、DROPを単なる後片付けと扱わないでください。

-- 単一カラム
CREATE INDEX idx_users_email ON users(email);

-- 複合インデックス（左から順に使われる）
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);

-- 一意インデックス（重複禁止 = UNIQUE 制約と等価）
CREATE UNIQUE INDEX idx_users_email_uq ON users(email);

-- インデックス削除
DROP INDEX idx_users_email;             -- PostgreSQL/Oracle
DROP INDEX idx_users_email ON users;    -- MySQL

-- インデックス確認
SHOW INDEX FROM users;                   -- MySQL
\d users                                 -- PostgreSQL psql
SELECT * FROM pg_indexes WHERE tablename = 'users';

命名規約とベストプラクティス

規約	例
プレフィックス idx_	idx_users_email
UNIQUE は uq_	uq_users_email
テーブル名_カラム名	idx_orders_user_id_status
名前の長さは製品・版・byte/文字の単位を確認	Oracleの索引名はCOMPATIBLEが12.2以上なら最大128bytes、未満なら30bytes。表領域名は別の上限。PG/MySQLの数値を転用しない

インデックスの種類

B-tree（既定）

Oracleの通常列索引は既定でB-tree。等価・範囲検索・ソートの候補ですが、採用と改善は統計・costと対象SQL次第です。

通常列索引のOracle B-treeとPostgreSQL専用USING btreeの比較です。既存users/ordersと権限が前提で、検索・ソートの採用や改善はcost次第です。同名例を続けて実行しません。

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders USING btree (order_date);   -- PG 明示

Oracle 19cのBITMAP（読取り中心の分析用途）

次は通常の非partition heap表を前提とする別教材で、B-tree例と両方作成する手順ではありません。低種類数だけで高速と判断せず、値の偏り・検索条件と同時更新を評価します。全NULLキーも格納しますが、UNIQUE制約の代わりにはしません。

-- Oracle Database 19c; separate read-mostly heap table example; NOT executed.
CREATE BITMAP INDEX ix_test_01_bm ON test_table(col2);

更新時の索引キーentryのロックは複数行に及ぶため、同時書込みの多いOLTPへ安易に推奨しません。UNIQUE併用とglobal partitioned BITMAPは不可です。IOTのbitmap副索引にはmapping tableの条件があります。edition・利用権は別途確認します。

Hash

ここはPostgreSQLのHash比較です。等価比較向けで範囲検索やソート用ではありません。高速化は保証しません。Oracleのhash clusterとは別です。

PostgreSQL専用のHash索引例です。Oracle CREATE INDEXへUSING hashを移植できず、Oracleのhash clusterと同一視しません。

-- PostgreSQL
CREATE INDEX idx_users_email_hash ON users USING hash (email);

GIN / GiST（PostgreSQL）

PostgreSQL専用のGIN/GiST断片です。列型・演算子クラスや拡張等の準備を省略しており、環境を問わず動く例ではありません。

-- GIN: 全文検索 / JSONB / 配列
CREATE INDEX idx_articles_tags ON articles USING gin (tags);   -- 配列カラム
CREATE INDEX idx_logs_data ON logs USING gin (data jsonb_path_ops);

-- GiST: 地理空間 / 範囲型
CREATE INDEX idx_locations_geom ON locations USING gist (geom);

部分インデックス（Partial Index）

ここはPostgreSQLの行条件付き索引の比較です。対象行数・述語適合・costにより容量や保守・検索への効果は異なります。Oracle 19cのpartition indexing propertyによるpartial indexとは別です。

PostgreSQL専用の行条件付き索引です。「条件が同じなら使ってくれる」は採用保証ではなく、述語適合とcost等が必要です。容量・保守コスト低下も条件次第。Oracleのpartition単位のpartial indexやMySQL prefix索引とは別です。

-- PostgreSQL（MySQL は未対応）
CREATE INDEX idx_users_active_email
ON users(email)
WHERE deleted_at IS NULL;

-- メリット
-- - インデックスサイズが小さい（生存ユーザーのみ）
-- - 書き込み時のメンテナンスコストが下がる
-- - 検索時に条件が同じならプランナが使ってくれる

-- 使う側のクエリは同条件を含める
SELECT * FROM users
WHERE email = 'taro@example.com' AND deleted_at IS NULL;

関数インデックス（Function-based Index / Expression Index）

製品別の独立した関数索引例です。同名索引を順に作成しません。Oracle LOWER例も式・型・照合・NULL・統計と実計画を照合します。ユーザー定義関数のDETERMINISTIC条件と組込みLOWERを混同しません。MySQLのfunctional key partsは8.0.13以降で、旧コメントの8.0+は広すぎます。式の結果型・キー長・照合を確認し、TEXT/BLOB系の結果へこの形を無条件に適用しません。末尾EXTRACT例は製品とorder_dateの型を省略しています。PostgreSQLでは索引式がIMMUTABLEである必要があり、timestamptzからのEXTRACTはSTABLEのためそのまま索引化できません。dateやtimestamp without time zoneとは区別し、時刻帯付きなら業務上の集計時刻帯と式を別途設計します。MySQLでは式ごとの括弧も必要で、末尾行をそのまま転用できません。全例は実機未実行で、採用や速度改善は保証しません。

-- PostgreSQL: LOWER(email) で大小文字無視の検索を高速化
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('Taro@Example.com');

-- Oracle
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- MySQL 8.0+
CREATE INDEX idx_users_email_lower ON users((LOWER(email)));  -- 関数は () で囲む

-- 日付の年月だけ
CREATE INDEX idx_orders_yyyymm ON orders(EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date));

カバリングインデックス (INCLUDE)

PostgreSQL/SQL Server専用のINCLUDE例で、Oracle構文ではありません。全取得列を含めても常に表アクセス0ではなく、PostgreSQLのindex-only scanはvisibility map等にも依存します。SQL Server全版/editionの成功は未検証です。

-- PostgreSQL 11+ / SQL Server: 非キー列をインデックスに含める
CREATE INDEX idx_orders_user_inc
ON orders(user_id)
INCLUDE (order_date, total_amount);

-- インデックスだけで SELECT が完結（Index Only Scan）
SELECT order_date, total_amount FROM orders WHERE user_id = 100;

オンライン作成（本番運用）

作成の更新制限や待機は製品・操作方式で異なります。Oracle 19cのONLINEは作成中のDMLを許可する指定です。既存トランザクション、競合DDL、必要領域・負荷と実行可能な時間帯を確認して方式を選びます。

Oracle 19cのONLINE作成とREBUILDの区別

次は基本例・TABLESPACE例の代替となる未実行例です。CREATE INDEX ... ONLINEは作成中のDMLを許可しますがparallel DMLは非対応です。通常索引のUROWID列へのONLINEは不可。BITMAP/clusterのCREATE ONLINEはCOMPATIBLEが10以上なら指定可能との条件があり、BITMAP全般のONLINE禁止とはしません。

-- Oracle Database 19c; alternative to basic.sql; NOT executed.
CREATE INDEX ix_test_01 ON test_table(col2) ONLINE;

ALTER INDEX ... REBUILD ONLINEではbitmap join indexとcluster index、deferrable UNIQUEを強制する索引などに別の制限があります。CREATEの条件をそのままREBUILDへ転用しません。partition索引全体を単一REBUILDすることもできません。対象構成・IOT副索引の列数・利用edition/契約・競合処理を照合します。DML許可は任意DDLやparallel DMLの許可、無ロック・無停止・固定秒数・無影響の保証ではありません。

MySQL 5.6+ (InnoDB)

MySQL InnoDBの独立した方式比較でOracle ONLINEとは別です。LOCK=NONEでも無ロックではなく、既存transactionの待機と終了時の排他的metadata lockがあります。旧「明示的に指定」「完全コピー」の2文はADD INDEX句とALGORITHM句の間のカンマが欠けています。MySQL 8.4の構文上はそれぞれ ADD INDEX idx_orders_user_id (user_id), ALGORITHM=INPLACE, LOCK=NONE と ADD INDEX idx_orders_user_id (user_id), ALGORITHM=COPY, LOCK=SHARED と区切ります。CREATE INDEXの後置指定とは別です。これらは独立した方式比較で同名索引を順に追加する手順ではなく、訂正版も実機未実行です。MySQL 8.4資料の照合を5.6全minorの互換保証にしません。

-- 既定でオンライン作成（DML を許可）
ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id);

-- 明示的に指定
ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id)
  ALGORITHM=INPLACE, LOCK=NONE;

-- 完全コピー (古い方式)
ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id)
  ALGORITHM=COPY, LOCK=SHARED;

PostgreSQL CONCURRENTLY

PostgreSQL専用の保存例です。末尾SELECTのindexnameはこのJOINに存在しない列で、そのまま実行できません。名前列はpg_class.relnameです。CONCURRENTLYはトランザクションブロック不可で、複数走査・待機・追加負荷があります。invalidでも更新保守や失敗後のUNIQUE強制が残る場合があり、一律DROPしません。

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- 注意:
-- - トランザクション内で実行不可
-- - 通常より時間がかかる（2 回スキャン）
-- - 失敗時に INVALID なインデックスが残る → DROP して再作成
SELECT indexname, indisvalid FROM pg_index
JOIN pg_class ON pg_class.oid = indexrelid
WHERE NOT indisvalid;

インデックス再構築

作成・再構築・統計収集は別です。REINDEX TABLEやANALYZE/OPTIMIZEは読取診断でなく変更処理で、一括・定期実行を勧めません。Oracle REBUILD ONLINEにはCREATE ONLINEとは異なる対象制限があり、partition索引全体の単一REBUILDは不可。必要性・領域・影響を確認し、効果を保証しません。

-- PostgreSQL: 肥大化したインデックスをスリム化
REINDEX INDEX idx_orders_user_id;
REINDEX TABLE orders;
REINDEX INDEX CONCURRENTLY idx_orders_user_id;  -- PG 12+

-- MySQL: 統計情報の更新（ANALYZE）+ 必要なら OPTIMIZE TABLE
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;          -- テーブル再構築（重い）

-- Oracle
ALTER INDEX idx_orders_user_id REBUILD ONLINE;

インデックス設計の指針

状況	指針
頻繁に WHERE / JOIN で使うカラム	索引の候補とし、対象SQL・選択率・保守コスト・実行計画と実測で判断
カーディナリティが低い (例: status = 0/1)	種類数だけでは判断不可。値の偏り・選択率・複合条件によって通常B-treeも候補。Oracle BITMAPは読取り中心の分析用途で検討し、同時更新の多いOLTPへ安易に勧めない。部分索引は製品・条件を確認
更新が頻繁なカラム	付けすぎ注意（書き込みが遅くなる）
複合インデックス順序	等価条件と範囲条件の列順を候補にし、結合・並べ替え・選択率・取得列・他SQLも含め計画と実測で判断
NULL を含むカラム	Oracle 19cの通常の昇順B-treeは全キー列がNULLの行を格納しない。一部NULLの複合キー、全NULLキーも格納するBITMAPとは区別。格納と検索での採用は別

FAQ

Q: インデックスは何個まで作って良い？
A: 一律の推奨上限はありません。検索効果と保守・容量を比較します。あるSQLのEXPLAINに現れないだけでは不要と判断できません。定期処理、列順・式・並び順、PK/UNIQUE/FKやhintを確認し、削除しない選択も残します。INVISIBLEでも保守・一意性は残り、DROP後の影響の完全再現ではありません。

Q: 複合インデックス (a, b, c) は WHERE b = ? に効く？
A: 先頭列条件は通常のrange scanで探索範囲を絞る基本ですが、Oracle 19cには先頭列なしのindex skip scan等もあります。先頭列の種類数が少ない場合等に候補となり、採用保証ではありません。aとcの条件でcがどこで評価されるかも計画の述語で確認し、「aだけ」と断定しません。結果・順序の同値性と計画・実測性能は分けて比較します。

Q: CONCURRENTLY が途中で失敗した
A: PostgreSQLでは失敗後にinvalid索引が残る場合があります。対象schema・定義・状態とエラーを確認し、保護への影響と再試行方法を判断します。DROP後の再作成は回復方法の一つで、自動削除の指示ではありません。Oracle ONLINEとは別の手順です。

公式資料と確認範囲

2026-10-27に一次資料で条件を再確認しました。全Oracle SQLは未実行です。他製品の比較SQLも実行していません。実plan・性能・ロック時間・容量・RU・edition/license・失敗後復旧・全minor互換と公開表示は未検証です。

• Oracle 19c CREATE INDEX：通常索引・UNIQUE/BITMAP・TABLESPACE・ONLINE・関数索引の条件。
• Oracle 19c ALTER INDEX：REBUILDのONLINE制限とTABLESPACE省略。
• Oracle 19c constraint：制約の宣言、参照先、複合キーのNULL。
• Oracle 19c Indexes and Index-Organized Tables：NULL・BITMAPの構造・更新ロック。
• Oracle 19c Optimizer Access Paths：skip scan、costによる選択。
• Oracle 19c Object Names：COMPATIBLE別のbyte上限。
• Oracle 19c COMMIT：DDL前後の暗黙COMMIT。
• Oracle 19c Managing Indexes：設計、監視と削除判断。
• PostgreSQL 18 CREATE INDEX：CONCURRENTLYの待機、invalid後の保守と一意性。
• PostgreSQL 18 pg_class：relname。
• PostgreSQL 18 pg_index：indisvalid。
• PostgreSQL 18 Partial Indexes：述語と利用条件。
• PostgreSQL 18 Index-Only Scans：visibility map等の条件。
• MySQL 8.4 Online DDL Operations：索引追加方式。
• MySQL 8.4 Online DDL Limitations：metadata lockと競合。

関数索引と旧比較構文の補足根拠: MySQL 8.0 functional key parts（8.0.13以降）、MySQL 8.4 ALTER TABLEの句区切り、PostgreSQL 18の索引式とIMMUTABLE、PostgreSQL公式18系catalog（EXTRACTの型別volatility）。資料・公式sourceによる確認であり、これらのDBで旧例や訂正版を実行した結果ではありません。