インデックスの作成

本記事では、SQLステートメントを使用してインデックスを作成する方法について説明します。また、インデックスを作成するための前提条件、インデックスの概要、制限事項、推奨事項などを紹介し、いくつかの例を示します。

インデックスの概要

インデックスはセカンダリインデックスとも呼ばれ、任意に追加可能なテーブル構造です。OceanBaseデータベースは、クラスタ化インデックスモデルを採用しています。ユーザー指定の主キーには主キーインデックスが自動生成されます。それ以外にユーザーが作成するインデックスがセカンダリインデックスです。業務ニーズにもとづき、特定のフィールドにインデックスを作成することで、そのフィールドの検索速度を上げることができます。

OceanBaseデータベースのインデックスに関する詳細については、インデックスの概要を参照してください。

前提条件

インデックスを作成する前に、以下の点を確認する必要があります：

• OceanBaseクラスタをデプロイし、Oracleモードのテナントを作成していること。OceanBaseクラスタのデプロイに関する詳細については、デプロイの概要を参照してください。

• OceanBaseデータベースのOracleテナントに接続していること。データベースへの接続に関する詳細については、接続方法の概要を参照してください。

• テーブルが作成されていることを確認してください。テーブルの作成に関する詳細については、テーブルの作成を参照してください。

• INDEX 権限と、インデックスを追加する対象テーブルの ALTER 権限を保有していることを確認してください。現在のユーザー権限を確認するための操作情報については、ユーザー権限の確認を参照してください。この権限を保有していない場合は、管理者に連絡して権限の付与を依頼してください。ユーザー権限の付与に関する操作情報については、直接権限付与を参照してください。

インデックス作成の制限

• OceanBaseデータベースでは、インデックス名はスキーマ（Schema）の範囲内で一意である必要があります。

• インデックス名は、128バイトを超えてはなりません。

• 複数の一意インデックスを作成できますが、その列の値は一意である必要があります。

• ローカル一意インデックスを使用してデータの一意性を制約する場合、ローカル一意インデックスにはテーブルのパーティションキーを含める必要があります。

• 関数ベースのインデックスの使用制限

  OceanBaseデータベースのOracleモードでは、関数ベースのインデックスで使用できる式に制限があり、一部のシステム関数の式を関数ベースのインデックスとして使用することはできません。具体的な関数リストについては、関数ベースのインデックスでサポートされているシステム関数リスト と 関数ベースのインデックスでサポートされていないシステム関数リストを参照してください。

インデックス作成の推奨事項

• インデックスが対象とする列や用途を簡潔に表す名前を使用することを推奨します。例えば idx_customer_name などです。詳細な命名に関する情報は、オブジェクト命名ルールの概要を参照してください。

• グローバルインデックスのパーティションルールと主テーブルのパーティションルールが同じで、パーティション数が同じ場合は、ローカルインデックスを作成することを推奨します。

• インデックスを作成するSQL文をパラレル実行する場合、その数はテナントのUnit仕様におけるCPUコアの上限を超えないようにしてください。テナントUnit仕様のコア数の上限を超えないようにすることを推奨します。例えば、テナントのUnit仕様が4コア（4C）の場合、同時に作成するインデックスの数は4つ以下にすることを推奨します。

• 頻繁に更新されるテーブルへの過度なインデックス作成は避け、頻繁にクエリ対象となる列にインデックスを作成すべきです。

• データ量の少ないテーブルにはインデックスを使用しないことを推奨します。データが少ない場合、全データをスキャンする方がインデックスを経由するよりも高速な場合があり、最適化の効果が得られない可能性があるためです。

• 検索性能よりも更新性能がはるかに重要である場合は、インデックスの作成を推奨しません。

• 効率的なインデックスの作成：

  • インデックスには、クエリで使用されるすべての列を含める必要があります。これにより、テーブル本体へのアクセスを可能な限り減らすことができます。

  • 等価条件で使用する列をインデックスの先頭に配置します。

  • フィルタリングやソートの際、より多くのデータを絞り込める選択性の高い列をインデックスの前方に配置します。

コマンドラインを使用するインデックスの作成

CREATE INDEX ステートメントを使用して、インデックスを作成してください。

例

例1：一意インデックスを作成する

インデックス列に重複する値が存在しないようにしたい場合は、一意（UNIQUE）インデックスを作成することができます。

以下のSQLステートメントを使用して、TEST_TBL1 という名前のテーブルを作成し、テーブル TEST_TBL1 に col2 列に基づく一意インデックスを作成します。

1. テーブル TEST_TBL1 を作成します。

   CREATE TABLE TEST_TBL1(col1 NUMBER, col2 NUMBER, col3 VARCHAR2(50), PRIMARY KEY(col1));
   

2. テーブル TEST_TBL1 に col2 列に基づいて、IDX_TEST_TBL1_COL2 という名前の一意インデックスを作成します。

   CREATE UNIQUE INDEX IDX_TEST_TBL1_COL2 ON TEST_TBL1(col2);
   

3. テーブル TEST_TBL1 のインデックス情報を確認します。

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL1';
   

   実行結果は次のとおりです：

   +---------------------------------+------------+-------------+------------+------------+
   | INDEX_NAME                      | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +---------------------------------+------------+-------------+------------+------------+
   | TEST_TBL1_OBPK_1703316804944854 | NORMAL     | SYS         | TEST_TBL1  | UNIQUE     |
   | IDX_TEST_TBL1_COL2              | NORMAL     | SYS         | TEST_TBL1  | UNIQUE     |
   +---------------------------------+------------+-------------+------------+------------+
   2 rows in set
   

例2：非一意インデックスを作成する

以下のSQLステートメントを使用して、TEST_TBL2 という名前のテーブルを作成し、テーブル TEST_TBL2 に col2 列に基づくインデックスを作成します。

1. テーブル TEST_TBL2 を作成します。

   CREATE TABLE TEST_TBL2(col1 NUMBER, col2 NUMBER, col3 VARCHAR2(50), PRIMARY KEY(col1));
   

2. テーブル TEST_TBL2 の col2 列に基づいて、IDX_TEST_TBL2_COL2 という名前のインデックスを作成します。

   CREATE INDEX IDX_TEST_TBL2_COL2 ON TEST_TBL2(col2);
   

3. テーブル TEST_TBL2 のインデックス情報を確認します。

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL2';
   

   実行結果は次のとおりです：

   +---------------------------------+------------+-------------+------------+------------+
   | INDEX_NAME                      | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +---------------------------------+------------+-------------+------------+------------+
   | TEST_TBL2_OBPK_1703317409002143 | NORMAL     | SYS         | TEST_TBL2  | UNIQUE     |
   | IDX_TEST_TBL2_COL2              | NORMAL     | SYS         | TEST_TBL2  | NONUNIQUE  |
   +---------------------------------+------------+-------------+------------+------------+
   2 rows in set
   

例3：ローカルインデックスを作成する

ローカルインデックスは、個々のパーティション上のデータに対して作成されるインデックスです。そのため、インデックスのキー値とテーブル内のデータは1対1で対応します。ローカルインデックスの各パーティションは必ずテーブルのいずれか1つのパーティションに対応し、両者は同じパーティションルールを共有します。したがって、ローカル一意インデックスが保証できるのはパーティション内部での唯一性のみであり、テーブルデータ全体のグローバルな唯一性を保証することはできません。ローカルインデックスは LOCAL キーワードを使用して作成します。

ローカルユニークインデックスを使用してデータの一意性に制約をかける場合、そのインデックスにはテーブルのパーティションキーを含める必要があります。

以下のSQLステートメントを使用して、TBL3_F_RL という名前のコンポジット・パーティションテーブルを作成し、テーブル TBL3_F_RL に col1 および col2 列に基づくローカル一意インデックスを作成します。

1. 非型テンプレートRange + Listのパーティションテーブル TBL3_F_RL を作成します。

   CREATE TABLE TBL3_F_RL(col1 NUMBER,col2 NUMBER) 
     PARTITION BY RANGE(col1)
       SUBPARTITION BY LIST(col2)
     (PARTITION p0 VALUES LESS THAN(100)
       (SUBPARTITION sp0 VALUES(1,3),
        SUBPARTITION sp1 VALUES(4,6),
        SUBPARTITION sp2 VALUES(7,9)),
      PARTITION p1 VALUES LESS THAN(200)
       (SUBPARTITION sp3 VALUES(1,3),
        SUBPARTITION sp4 VALUES(4,6),
        SUBPARTITION sp5 VALUES(7,9))
      ); 
   

2. テーブル TBL3_F_RL に col1 列と col2 列に基づいて、IDX_TBL3_F_RL_COL1_COL2 という名前のインデックスを作成します。

   CREATE UNIQUE INDEX IDX_TBL3_F_RL_COL1_COL2 ON TBL3_F_RL(col1,col2) LOCAL;
   

3. テーブル TBL3_F_RL のインデックス情報を確認します。

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TBL3_F_RL';
   

   実行結果は次のとおりです：

   +-------------------------+------------+-------------+------------+------------+
   | INDEX_NAME              | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +-------------------------+------------+-------------+------------+------------+
   | IDX_TBL3_F_RL_COL1_COL2 | NORMAL     | SYS         | TBL3_F_RL  | UNIQUE     |
   +-------------------------+------------+-------------+------------+------------+
   1 row in set
   

例4：グローバルインデックスを作成する

グローバルインデックスを作成するためのキーワードは GLOBAL です。ローカルインデックスと比較して、グローバルインデックスの最大の特徴は、グローバルインデックスのパーティションルールがテーブルのパーティションルールと独立していることです。グローバルインデックスは、独自のパーティションルールとパーティション数を指定でき、必ずしもテーブルのパーティションルールと一致させる必要はありません。

• OceanBaseデータベースのOracleモードでは、インデックスプロパティキーワードが指定されていない場合、デフォルトで GLOBAL プロパティが適用され、単一パーティションのグローバルインデックスが作成されます。

• グローバルインデックスのパーティションルールは、必ずしもテーブルのパーティションルールと一致している必要はありません。

• グローバルインデックスのパーティションルールと主テーブルのパーティションルールが同じで、パーティション数が同じ場合は、ローカルインデックスを作成することを推奨します。理由の1つはグローバルインデックスの方がメンテナンスコストが高いこと、もう1つは、主テーブルと同じ表グループに指定しない限り、グローバルインデックスは主テーブルのパーティションと物理的に同じ場所に配置される保証がないためです。

以下のSQLステートメントを使用して、TBL4_H という名前のパーティションテーブルを作成し、TBL4_H テーブルに col2 列に基づいたグローバルインデックスを作成します。

1. Hashパーティションによるパーティションテーブル TBL4_H を作成します。

   CREATE TABLE TBL4_H(col1 NUMBER PRIMARY KEY,col2 NUMBER)
     PARTITION BY HASH(col1) PARTITIONS 5;
   

2. テーブル TBL4_H に col2 列に基づくRangeパーティションインデックスである IDX_TBL4_H_COL2 という名前のグローバルインデックスを作成します。

   CREATE INDEX IDX_TBL4_H_COL2 ON TBL4_H(col2) GLOBAL
     PARTITION BY RANGE(col2)
      (PARTITION p0 VALUES LESS THAN(100), 
       PARTITION p1 VALUES LESS THAN(200), 
       PARTITION p2 VALUES LESS THAN(300)
      );
   

3. テーブル TBL4_H のインデックス情報を確認します。

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TBL4_H';
   

   実行結果は次のとおりです：

   +------------------------------+------------+-------------+------------+------------+
   | INDEX_NAME                   | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +------------------------------+------------+-------------+------------+------------+
   | TBL4_H_OBPK_1703321659273683 | NORMAL     | SYS         | TBL4_H     | UNIQUE     |
   | IDX_TBL4_H_COL2              | NORMAL     | SYS         | TBL4_H     | NONUNIQUE  |
   +------------------------------+------------+-------------+------------+------------+
   2 rows in set
   

例5：関数ベースのインデックスを作成する

テーブル内の一列または複数列の値に対する計算結果に基づいて作成されるインデックスを、関数ベースのインデックスと呼びます。関数ベースのインデックスは最適化技術の一つであり、これを使用することで、クエリ実行時に一致する関数の値を高速に特定でき、計算の繰り返しを避けることでクエリ効率を向上させます。

OceanBaseデータベースのOracleモードでは、関数ベースのインデックスで使用できる式に制限があり、一部のシステム関数の式を関数ベースのインデックスとして使用することはできません。具体的な関数リストについては、関数ベースのインデックスでサポートされているシステム関数リスト と 関数ベースのインデックスでサポートされていないシステム関数リストを参照してください。

セッション変数の固定化メカニズム

関数ベースのインデックスのセッション変数の固定化メカニズムにより、インデックス作成時と使用時の計算セマンティクスが完全に一致し、セッション環境の変化による「インデックスはヒットするが結果が正しくない」状況を回避します。

仕組み

関数ベースのインデックスを作成する際、システムはインデックス式を自動分析し、その式に依存するセッション変数（例：time_zone、sql_mode、collation_connection など）を特定します。その後、これらの変数の現在値をインデックスメタデータに保存します。 クエリが関数ベースのインデックスにヒットした場合：

1. システムはインデックス式の local_session_var_id から、インデックス作成時に保存されたセッション変数のスナップショットを取得します。
2. 式の型推論や計算を行う際、現在のセッション変数の値ではなく、保存された変数の値を使用します。
3. 計算結果がインデックス作成時と完全に一致することを保証します。

サポートされるセッション変数

関数ベースのインデックスで固定化がサポートされるセッション変数は以下の通りです：

システムは、インデックス式に基づいて固定化が必要な変数を動的に推論します。例えば、インデックス式に NOW() などの時間関数が含まれている場合、time_zone を固定化します。文字列関数が含まれている場合は、collation_connection が固定化される可能性があります。

使用上の注意事項

変動しやすい式への依存を避ける システムは自動的にセッション変数を固定化しますが、インデックス定義ではセッションの影響を受ける関数の使用を極力控え、決定的な列を使用することを推奨します。

-- ❌ 不推奨（time_zoneに依存している）
CREATE INDEX idx_now ON logs ((DATE(NOW())));

-- ✅ 推奨（決定的な列を使用している）
CREATE INDEX idx_log_date ON logs ((DATE(log_time)));

セッション変数の手動管理は不要

システムがセッション変数の一貫性を自動的に処理するため、現在のセッション変数の変更が、作成済みの関数ベースのインデックスの正確性に影響することを懸念する必要はありません。クエリ実行時には、インデックス作成時に保存された変数のスナップショットが自動的に使用されます。

以下のSQLステートメントを使用して、TEST_TBL5 という名前のテーブルを作成し、id 列に基づく関数ベースのインデックスを TEST_TBL5 テーブルに作成します。

1. テーブル TEST_TBL5 を作成します。

   CREATE TABLE TEST_TBL5(id NUMBER, name VARCHAR2(18));
   

2. テーブル TEST_TBL5 の id 列に基づいて、IDX_TEST_TBL5_ID という名前のインデックスを作成します。

   CREATE INDEX IDX_TEST_TBL5_ID ON TEST_TBL5 ((id+1));
   

3. 以下のSQLステートメントを使用して、作成された関数ベースのインデックスを確認できます。

   SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL5';
   

   実行結果は次のとおりです：

   +-----------------+-----------------------+-------------+------------+------------+
   | INDEX_NAME      | INDEX_TYPE            | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +-----------------+-----------------------+-------------+------------+------------+
   | IDX_TEST_TBL5_ID | FUNCTION-BASED NORMAL | SYS         | TEST_TBL5  | NONUNIQUE  |
   +-----------------+-----------------------+-------------+------------+------------+
   1 row in set
   

例6：空間インデックスを作成する

空間インデックスは、空間データの処理と最適化を行うためのデータベースインデックスです。地理情報システム（GIS）や位置データのストレージおよびクエリに広く利用されています。OceanBaseデータベースでは、空間インデックスを作成する際の構文はOracleと異なり、空間インデックス列のSRIDはテーブル作成時に指定します。

空間インデックスの作成には、以下の点に注意する必要があります：

• GIS機能を使用する前に、業務テナントでGIS metaデータを設定する必要があります。具体的な手順については、空間インデックスの作成 - 準備を参照してください。
• パーティションテーブルに空間インデックス制約を作成する方法については、空間インデックスの作成 - 制約を参照してください。

1. テーブル TEST_TBL6 を作成します。

   obclient [test]> CREATE TABLE TEST_TBL6(id NUMBER PRIMARY KEY, name VARCHAR2(32), shape SDO_GEOMETRY SRID 4326);
   

2. テーブル TEST_TBL6 のshape列に基づいて、IDX_TBL6_Gという名前の空間インデックスを作成します。

   obclient [test]> CREATE INDEX IDX_TBL6_G ON TEST_TBL6(shape) INDEXTYPE IS MDSYS.SPATIAL_INDEX;
   

3. テーブル TEST_TBL6 のインデックス情報を確認します。

   obclient [test]> SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL6';
   

   実行結果は次のとおりです：

   +---------------------------------+------------+-------------+------------+------------+
   | INDEX_NAME                      | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +---------------------------------+------------+-------------+------------+------------+
   | TEST_TBL6_OBPK_1718852454772761 | NORMAL     | SYS         | TEST_TBL6  | UNIQUE     |
   | IDX_TBL6_G                      | DOMAIN     | SYS         | TEST_TBL6  | NONUNIQUE  |
   +---------------------------------+------------+-------------+------------+------------+
   2 rows in set
   

例7：カラムストア・インデックスを作成する

テーブル TEST_TBL7 を作成した後、列インデックス IDX1_TBL7_CG を作成します。

1. テーブル TEST_TBL7 を作成します。

   CREATE TABLE TEST_TBL7(id NUMBER, name VARCHAR2(20), age NUMBER);
   

2. テーブル TEST_TBL7 にカラムストア・インデックス IDX1_TBL7_CG を作成し、インデックステーブルに age 列の冗長データを格納します。

   CREATE INDEX IDX1_TBL7_CG ON TEST_TBL7(name) STORING(age) WITH COLUMN GROUP(each column);
   

3. テーブル TEST_TBL7 のインデックス情報を確認します。

   obclient [test]> SELECT INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,UNIQUENESS FROM user_indexes WHERE table_name='TEST_TBL7';
   

   実行結果は次のとおりです：

   +--------------+------------+-------------+------------+------------+
   | INDEX_NAME   | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +--------------+------------+-------------+------------+------------+
   | IDX1_TBL7_CG | NORMAL     | SYS         | TEST_TBL7  | NONUNIQUE  |
   +--------------+------------+-------------+------------+------------+
   1 row in set (0.026 sec)
   

次の操作

インデックスを作成した後、クエリのパフォーマンスを最適化する必要があるかもしれません。SQLチューニングに関する詳細については、SQLチューニングの概要を参照してください。

関連ドキュメント

• インデックスの確認に関する詳細については、インデックスの確認を参照してください。
• インデックスの管理に関する詳細については、DROP INDEX および インデックスの削除を参照してください。
• 一意インデックスと非一意インデックスに関する詳細については、一意インデックスと非一意インデックスを参照してください。
• ローカルインデックスとグローバルインデックスに関する詳細については、ローカルインデックスとグローバルインデックスを参照してください。