目的から項目を選ぶ

Oracle 19cの表・索引DDLを目的別に選ぶための案内です。以下から詳細ページを選んでください。各ページの製品・版・前提条件も確認し、そのまま本番へ一括実行する手順とは扱わないでください。

1. 索引の作成 (索引関連 SQL)：索引を作成・設計する（製品別の比較を含む）
2. 索引の再作成：Oracle索引の再構築を調べる
3. 索引の削除（DROP INDEX）：索引の削除と依存を調べる

親カテゴリ：DDL。DDLや採番を消費するSELECTは読取だけの確認ではありません。以下は独立した教材例であり、実DBでの実行・性能・復旧成功を保証しません。

Oracle 19cの名前付き索引

Oracleの基本構文を先に示します。既存表・列・同名索引を確認した検証環境用の独立例で、SQLの実行確認はしていません。後続には製品別の比較例を保持しています。

-- Oracle Database 19c: independent example, not executed.
-- Requires an existing users table with an email column.
CREATE INDEX idx_users_email ON users(email);

この記事の要点

CREATE INDEX idx ON t(col) で B-Tree インデックスを作成（デフォルト） UNIQUE INDEX は重複禁止。NULL は許容 (DB により挙動差) PostgreSQL は USING btree / hash / gist / gin / brin でアルゴリズム指定可 複合インデックス (Composite) は列順・検索条件・ORDER BY・選択率とcostを照合し、実行計画と実測で効果を判断する（順序一致だけで最適とは限らない） Partial Index (PG/SQL Server): WHERE句付きで一部行だけ索引化。容量・保守負荷と利用可否は対象行や検索条件への適合次第 Function-based Index: CREATE INDEX idx_fn ON t(LOWER(email))（Oracleでは索引名を指定） で関数結果に索引 ALTER INDEX ... REBUILD で再構築。再構築の要否・効果は実測で判断（統計収集とは区別）

基本: CREATE INDEX

保存した製品混在の比較例です。末尾のCREATE INDEX ... COMMENTはMySQL 8.4の構文でOracle 19cには転用できません。「一意制約付き」はUNIQUE索引の作成であり、宣言したUNIQUE制約とは区別します。各文は別の例です。

-- 1 カラム索引（B-Tree がデフォルト）
CREATE INDEX idx_users_email ON users(email);

-- 一意制約付き
CREATE UNIQUE INDEX uq_users_email ON users(email);

-- 降順
CREATE INDEX idx_logs_at_desc ON logs(created_at DESC);

-- 複合インデックス
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- スキーマ指定
CREATE INDEX shop.idx_p_name ON shop.products(name);

-- コメント付き
CREATE INDEX idx_t_x ON t(x) COMMENT 'Hot path for /search';

UNIQUE INDEX と UNIQUE 制約の違い

項目	UNIQUE INDEX	UNIQUE 制約
記述	CREATE UNIQUE INDEX	ALTER TABLE ADD CONSTRAINT
NULL の扱い	Oracle 19cの単一列UNIQUEは複数NULLを許容。複合一意キーは全列NULLなら制約を満たすが、一部NULLでNULL位置と残りの値の組合せが同じ2行は違反。SQL Serverの単一列・通常UNIQUE索引はNULLを1件だけ許容し、WHERE col IS NOT NULL付きの一意filtered indexはNULL行を対象外にする別設計	Oracle 19cのUNIQUE制約も左記の単一列/複合キーの区別が必要。SQL Serverの単一列・通常UNIQUE制約はNULLを1件だけ許容。MySQL 8.4のNULL許容列のUNIQUEは複数NULL可。PostgreSQL 18は既定でNULLを異なる値として扱うが、NULLS NOT DISTINCT指定では同じ値として扱う。NULL許容と一意性の比較規則を区別する
外部キー参照	PostgreSQL 18: 非部分UNIQUE索引の列も参照可能。Oracle 19c: 索引単独でなくPRIMARY KEY/UNIQUE制約を参照。MySQL 8.4: 適格な一意キーが基本	参照列・型等の条件を満たすPRIMARY KEY/UNIQUEが対象。MySQL 8.4 InnoDBの非一意キーや一意キーの一部への参照は非標準の非推奨互換機能です。restrict_fk_on_non_standard_keyは既定ONで拒否し、OFFでは警告付きで許可されます。参照列が索引の先頭から同じ順序で並ぶことも必要で、列のprefix索引はFKに使えません。NDBは明示的一意キーが必要。設定緩和は勧めない
論理意味	性能 + 一意保証	業務ルール表明

各 DB のインデックスタイプ

製品名ごとの比較断片です。SQL Serverのfacts(...)は省略記法で実行できません。列型・演算子クラス・Oracle Text等の準備や版/エディション条件を省いており、一括実行しないでください。

-- PostgreSQL の豊富なタイプ
CREATE INDEX idx_btree ON t USING btree(col);     -- デフォルト
CREATE INDEX idx_hash  ON t USING hash(col);      -- 等価比較専用
CREATE INDEX idx_gist  ON t USING gist(geom);     -- 地理情報 / 全文
CREATE INDEX idx_gin   ON t USING gin(tags);      -- JSONB / 配列
CREATE INDEX idx_brin  ON t USING brin(ts);       -- 巨大時系列向け軽量

-- MySQL / MariaDB
CREATE INDEX idx_b   ON t(col);                                -- B-Tree
CREATE FULLTEXT INDEX idx_f ON t(body);                        -- 全文検索
CREATE SPATIAL INDEX idx_s ON t(geom);                         -- 空間
ALTER TABLE t ADD INDEX idx(col) USING HASH;                   -- MEMORY エンジン

-- Oracle
CREATE INDEX idx ON t(col);                                    -- B-Tree
CREATE BITMAP INDEX idx_b ON t(col);                           -- 低カーディナリティ向け
CREATE INDEX idx_fn ON t(LOWER(name));                         -- 関数索引
CREATE INDEX idx_ctx ON t(body) INDEXTYPE IS CTXSYS.CONTEXT;   -- 全文 (Text)

-- SQL Server
CREATE NONCLUSTERED INDEX idx ON t(col);
CREATE CLUSTERED INDEX idx_pk ON t(id);                        -- 表自体の物理順序
CREATE COLUMNSTORE INDEX cs_idx ON facts(...);                 -- 列指向（OLAP）

複合インデックス（カラム順序が命）

複合インデックス(A, B, C)では左端からの条件が通常の探索範囲を絞る基本です。ただしMySQL 8.4には単一表・索引内の列だけを参照・GROUP BY/DISTINCTなし・後続列の範囲条件等を要するSkip Scanがあります。条件を満たしても統計とコスト次第で、左端条件なしの全検索に使える機能ではありません。下記断片だけではpaid条件の採用を判断できません。

保存例の「左端を飛ばすと使えない」は断定し過ぎです。MySQL 8.4には条件付きSkip Scanがありますが、下記paid条件の採用を保証しません。WHEREだけの断片であり、範囲後の列の評価も実行計画次第です。

CREATE INDEX idx_o ON orders(customer_id, status, created_at);

-- ✅ 使える
WHERE customer_id = 1;
WHERE customer_id = 1 AND status = 'paid';
WHERE customer_id = 1 AND status = 'paid' AND created_at >= '2024-01-01';

-- ❌ 左端を飛ばすと使えない（MySQL の場合）
WHERE status = 'paid';                          -- customer_id を指定していない
WHERE created_at >= '2024-01-01';                -- 同上

-- △ 範囲のあとは止まる
WHERE customer_id = 1 AND created_at >= '...';   -- status 指定なし → created_at は使えるが効果限定
-- → 検索パターンに合わせて (customer_id, created_at) も検討

Partial Index (部分索引)

「特定の条件に合う行だけ索引化」する機能です。対象行数や利用SQLによって容量・更新負荷への効果は異なります。

PostgreSQL/SQL Serverの行条件付き索引とMySQLのprefix索引は別概念です。索引サイズや更新負荷の改善を無条件に保証しません。各製品の個別例として読んでください。

-- PostgreSQL
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
-- → status = 'active' の行だけ索引化
-- WHERE status = 'active' AND email = '...' の SELECT で利用

-- SQL Server: Filtered Index
CREATE INDEX idx_pending ON orders(id) WHERE status = 'pending';

-- MySQL は Partial Index 非対応（≠ 「索引の prefix だけ」の意味の partial）
-- MySQL の prefix index は別概念
CREATE INDEX idx_email_prefix ON users(email(20));   -- email の先頭 20 文字

Function-based Index (関数索引)

製品別の関数索引の比較例です。式・型・照合や既存データと利用SQLを照合してください。MySQLの生成列と索引追加は書込みDDLであり診断ではありません。

-- 大文字小文字を無視する検索の高速化
-- PostgreSQL
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- ★ WHERE 側も同じ式を使うこと

-- Oracle
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- MySQL 8.0+ : Functional Index
CREATE INDEX idx ON users ((LOWER(email)));        -- ★ 括弧 2 重

-- 計算列に索引 (生成列 + 通常索引)
ALTER TABLE users ADD email_lc VARCHAR(255)
       GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_email_lc ON users(email_lc);

DROP INDEX

保存した製品別の削除構文比較です。末尾のDROP INDEX IF EXISTS idx_users_email;はPostgreSQL構文です。SQL Server 2016以降の現行構文にはONで対象表/ビューの指定が必要で、この末尾行だけでは不完全です。先のtable.index形式は後方互換構文で新規利用の推奨ではありません。Oracleへもそのまま転用しないでください。主キー/一意制約/FKとの依存を確認し、削除を通すために制約を外す手順にはしません。

-- 標準
DROP INDEX idx_users_email;                       -- PostgreSQL / Oracle

-- MySQL は ON が必要
DROP INDEX idx_users_email ON users;
-- または
ALTER TABLE users DROP INDEX idx_users_email;

-- SQL Server
DROP INDEX users.idx_users_email;
DROP INDEX idx_users_email ON users;

-- 存在しない場合のエラー回避
DROP INDEX IF EXISTS idx_users_email;             -- PG / SQL Server 2016+

ALTER INDEX REBUILD

索引を再構築する製品別比較です。再構築の必要性と容量・実行計画・実測を確認し、速度改善や定期実施を無条件に保証しません。PostgreSQLのCONCURRENTLYは12で導入されました。

保存例の「11+ ONLINE」は誤りです。REINDEX CONCURRENTLYはPostgreSQL 12で導入されました。トランザクションブロック内では実行できず、複数走査や待機があります。OracleのALTER INDEX idx REBUILD;はTABLESPACE省略時には従来の表領域を使い、「別表領域」への移動を示しません。移動には宛先の明示と権限・容量等の条件確認が別途必要です。Oracle ONLINEやMySQL再構築も無ロック・瞬時・高速を保証しません。

-- Oracle
ALTER INDEX idx REBUILD;                          -- 別表領域に作り直して切替（高速）
ALTER INDEX idx REBUILD ONLINE;                   -- DML を止めない
ALTER INDEX idx COALESCE;                         -- 同一表領域内で詰める（軽量）

-- PostgreSQL
REINDEX INDEX idx;
REINDEX INDEX CONCURRENTLY idx;                   -- 11+ ONLINE

-- SQL Server
ALTER INDEX idx ON users REBUILD;
ALTER INDEX idx ON users REORGANIZE;              -- 軽量版（断片化 < 30% 推奨）

-- MySQL は REBUILD コマンド無し → ALTER TABLE で再構築
ALTER TABLE users ENGINE = InnoDB;                -- 全索引と表の再構築
OPTIMIZE TABLE users;                              -- 同様

統計情報 (Index Stats)

この製品別比較にはDBMS_STATS/ANALYZE/UPDATE STATISTICS等の変更処理が含まれます。読取だけの診断ではありません。自動実行や一律の統計更新を勧めません。

-- Oracle
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_EMP_EMAIL');
SELECT index_name, distinct_keys, leaf_blocks, blevel, clustering_factor
FROM   user_indexes;

-- PostgreSQL
ANALYZE users;
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

-- MySQL
ANALYZE TABLE users;
SELECT * FROM information_schema.statistics WHERE table_name = 'users';

-- SQL Server
UPDATE STATISTICS users idx_users_email WITH FULLSCAN;
DBCC SHOW_STATISTICS('users', 'idx_users_email');

使われない索引の検出

利用回数0は観測期間・リセット・定期処理の影響を受け、不要という証明ではありません。Oracle MONITORING USAGEはDDLです。列順・一意性・FK・hintまで確認し、結果だけで削除しないでください。

-- PostgreSQL: 一度も使われていない索引
SELECT s.schemaname, s.relname AS table, s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM   pg_stat_user_indexes s
WHERE  s.idx_scan = 0
  AND  s.indexrelid NOT IN (SELECT indexrelid FROM pg_index WHERE indisunique);

-- MySQL 8.0+ (Performance Schema)
SELECT object_schema, object_name, index_name
FROM   sys.schema_unused_indexes;

-- Oracle: V$OBJECT_USAGE
ALTER INDEX idx MONITORING USAGE;
-- ... 一定期間運用後 ...
SELECT * FROM v$object_usage;

インデックスの注意点

• 索引には書込み時の保守と容量のコストがある。更新対象列やアクセスパスを含むワークロード全体で必要性を判断し、更新系テーブルに無闇に増やさない
• カーディナリティが低い列でも選択率・分布・複合条件や取得列によってB-Treeの効果は異なる。部分索引は対応製品と条件への適合を確認する。OracleのBITMAPは1行の更新でも複数行に対応する索引キーentryをロックするため、同時書込みの多いOLTPへ低カーディナリティだけを理由に安易に推奨しない
• WHERE句の関数は元列の通常索引による探索範囲の絞込みを妨げる場合がある。式・型・照合の適合する関数索引は候補になり得るが、採用は統計・costと実行計画次第。関数の存在だけで索引が使えないとは断定しない
• 暗黙キャストの影響は製品・列とパラメータの型・変換方向による。例えばMySQL 8.4の文字列列と数値の比較では、その列の索引で値を高速に探索できない。全変換を同一視せず、列側/値側の変換と比較結果・実行計画を確認する
• 索引の採用は統計・選択率・cost等で決まり、定期ANALYZEの有無だけでは断定できない。Oracle 19cのoptimizer統計は自動収集の状態を確認し、必要な手動収集はDBMS_STATSを検討する。ANALYZEによるoptimizer統計収集はobsoleteであり、製品共通の定期実行手順にはしない

FAQ

Q: 索引を張るほど速くなる？
A: SELECTの高速化は保証されません。全表走査が低costとなる場合もあり、索引追加の効果は実行計画・選択率・取得列・統計とワークロード次第です。書込み時の索引保守と容量のコストも含め、同じ結果を返すSQLの計画と実測を前後比較して判断します。

Q: 主キーは自動でインデックスがつく？
A: Oracle 19cは主キーの強制に適格な既存索引を再利用でき、その索引は一意でも非一意でも構いません。適格な既存索引がなければ通常は一意索引を作成します。PRIMARY KEY制約と物理索引を同一視しません。

Q: ONLINE REBUILD は本当に DML を止めない？
A: 無ロック・瞬時・無影響ではありません。MySQL 8.4のonline DDLもmetadata lock保持transactionの終了を待つ場合があり、最終段階の排他的metadata lockが必要です。PostgreSQL 18のCONCURRENTLYも複数走査・transaction待機・追加資源を要し、トランザクションブロック内では実行できません。Oracle ONLINEも対象制限と競合を確認します。

公式資料と確認範囲

Oracle Database 19c、MySQL 8.4、PostgreSQL 18とMicrosoft SQL Serverの一次資料で構文と条件を照合しています。導入版の根拠はPostgreSQL 12のリリースノートです。SQLや実ブラウザでの検証結果ではなく、他版・エディションや省略された検体の成功を保証しません。

• Oracle 19c COMMIT：DDL前後の確定。
• Oracle 19c TRUNCATE TABLE：通常表とprivate temporary table。
• Oracle 19c CREATE TRIGGER：DML/DDLイベントの区別。
• Oracle 19c DROP TABLE、FLASHBACK TABLE：削除と条件付き復旧。
• Oracle 19c CREATE SEQUENCE：表と独立した採番。
• Oracle 19c constraint、CREATE INDEX：主キー・索引の区別。
• PostgreSQL 18 Constraints、MySQL 8.4 FOREIGN KEY、MySQL 8.4 非標準キーの制限：参照可能なキーと互換条件。
• PostgreSQL 12 Release Notes、PostgreSQL 18 REINDEX：CONCURRENTLYの導入版と待機・制限。
• MySQL 8.4 Range Optimization：Skip Scanの条件。
• MySQL 8.4 Online DDL Limitations、CREATE INDEX：ロックと製品固有構文。

補足の根拠：Oracle 19c CREATE TABLEのDEFAULT、索引・関数索引とBITMAPのロック、ALTER INDEXのTABLESPACE省略、ANALYZE、統計収集、costによるアクセスパス選択、MySQL 8.4の型変換、PostgreSQL 18の部分索引、SQL Server UNIQUEとNULL、SQL Server DROP INDEXの対象指定。