インデックスの作成

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

インデックスの概要

インデックスは、セカンダリインデックスとも呼ばれ、オプションのテーブル構造です。OceanBaseデータベースは集約インデックステーブルモデルを採用しており、ユーザーが指定した主キーに対しては、システムが自動的に主キーインデックスを生成します。一方、ユーザーが作成するその他のインデックスは、セカンダリインデックスとなります。業務ニーズに応じて、特定のフィールドにインデックスを作成することで、それらのフィールドに対するクエリのパフォーマンスを向上させることができます。

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

前提条件

インデックスを作成する前に、以下の事項を確認してください：

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

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

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

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

インデックス作成の制限

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

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

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

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

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

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

インデックス作成に関する推奨事項

• インデックスが対象とする列と用途を簡潔に表す名前の使用を推奨します。例：idx_customer_name。その他の命名規則については、オブジェクト名付け規則の概要を参照してください。

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

• インデックス作成のためのSQLステートメントを並列に発行する件数は、テナントのUnit仕様におけるCPUコア数の上限を超えないようにすることを推奨します。例えば、テナントの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：ローカルインデックスを作成する

ローカルインデックスは、単一のパーティション上のデータに対して作成されるインデックスであるため、ローカルインデックスのキー値とテーブル内のデータは一対一で対応します。つまり、ローカルインデックスのあるパーティションは必ずテーブルの特定のパーティションに対応し、両者は同じパーティションルールを持ちます。したがって、ローカル一意インデックスは、パーティション内部での一意性を保証できるものの、テーブルデータ全体のグローバルな一意性を保証することはできません。ローカルインデックスを作成する際のキーワードは 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つのパーティションしか存在しません。

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

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

以下の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：関数インデックスを作成する

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

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

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

関数インデックスのセッション変数固定メカニズムは、インデックスの作成時と使用時の計算セマンティクスが完全に一致することを保証し、セッション環境の変化による「インデックスヒットだが結果が誤っている」という問題を回避します。

動作原理

関数インデックスを作成する際、システムは自動的にインデックス式を解析し、その式がどのセッション変数（例：time_zone、nls_date_format、nls_timestamp_formatなど）に依存しているかを特定し、これらの変数の現在の値をインデックスメタデータに保存します。

クエリが関数インデックスにヒットした場合：

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

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

関数インデックスで固定をサポートするセッション変数は以下のとおりです：

システムはインデックス式に基づいて、固定が必要な変数を動的に推論します。例えば、インデックス式に SYSDATE、CURRENT_TIMESTAMP などの時間関数が含まれている場合、time_zone が固定されます。日付フォーマット関数が含まれている場合、対応するNLS形式変数が固定されます。文字列関数が含まれている場合、collation_connection が固定される可能性があります。

使用上の注意

変動する式への依存の回避

システムは自動的にセッション変数を固定しますが、インデックス内でセッションの影響を受ける関数の使用は可能な限り減らし、確定的な列を優先的に使用することを推奨します：

-- ❌ 不推奨（time_zoneに依存）
CREATE INDEX idx_sysdate ON logs ((TRUNC(SYSDATE)));

-- ✅ 推奨（確定的な列を使用）
CREATE INDEX idx_log_date ON logs ((TRUNC(log_time)));

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

システムはセッション変数の一貫性を自動的に処理するため、現在のセッション変数の変更が既に作成された関数インデックスの正確性に影響を与える心配はありません。クエリ時には、インデックス作成時に保存された変数のスナップショットが自動的に使用されます。

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

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メタデータを設定する必要があります。具体的な手順については、空間インデックスの作成 - 前提条件を参照してください。
• パーティションテーブルで空間インデックス制約を作成する場合は、空間インデックスの作成 - 制約を参照してください。

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およびインデックスの削除を参照してください。
• 唯一インデックスと非唯一インデックスの詳細については、唯一インデックスと非唯一インデックスを参照してください。
• ローカルインデックスとグローバルインデックスの詳細については、ローカルインデックスとグローバルインデックスを参照してください。