インデックスの作成

本記事では、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つのパーティションしか持ちません。

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

• グローバルインデックスのパーティションルールと主テーブルのパーティションルールが同じで、パーティション数も同じ場合は、ローカルインデックスを作成することを推奨します。その理由の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：関数インデックスを作成する

テーブルの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)
   

例8：全文インデックスを作成する

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

   obclient> CREATE TABLE tbl1(
       col1 INT,
       col2 CHAR(50),
       col3 VARCHAR2(500));
   

2. フルテキストインデックス ft_idx1_tbl1 を作成します。

   obclient> CREATE INDEX ft_idx1_tbl1 ON tbl1(col2)
       INDEXTYPE IS CTXSYS.CONTEXT
       PARAMETERS('LEXER SPACE')
       LOCAL;
   

3. フルテキストインデックス ft_idx2_tbl1 を作成します。

   obclient> CREATE INDEX ft_idx2_tbl1 ON tbl1(col3)
       INDEXTYPE IS CTXSYS.CONTEXT
       PARAMETERS('LEXER SPACE')
       LOCAL;
   

4. テーブル tbl1 にテストデータを追加します。

   obclient> INSERT INTO tbl1 (col1, col2, col3) VALUES 
       (1, 'Hello World', 'This is a test'),
       (2, 'OceanBase', 'OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team'),
       (3, 'Database Management', 'Learn about SQL and database administration'),
       (4, 'Full Text Searching', 'Master the art of full text searching');
   

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

   Query OK, 4 rows affected
   Records: 4  Duplicates: 0  Warnings: 0
   

5. CONTAINS クエリを実行します。

   obclient> SELECT *
       FROM tbl1
       WHERE CONTAINS(col2, 'OceanBase', 1) > 0;
   

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

   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
   | COL1 | COL2                                               | COL3                                                                                                                |
   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
   |    2 | OceanBase                                          | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team |
   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

6. CONTAINS クエリを実行し、SCORE でソートします。

   obclient> SELECT col1, col2, col3, SCORE(1)
       FROM tbl1
       WHERE CONTAINS(col2, 'Database', 1) > 0
       AND col1 >= 2
       ORDER BY SCORE(1) DESC;
   

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

   +------+----------------------------------------------------+---------------------------------------------+------------------------+
   | COL1 | COL2                                               | COL3                                        | SCORE(1)               |
   +------+----------------------------------------------------+---------------------------------------------+------------------------+
   |    3 | Database Management                                | Learn about SQL and database administration | 1.259496819494492E+000 |
   +------+----------------------------------------------------+---------------------------------------------+------------------------+
   1 row in set
   

7. 複数のクエリを実行し、score_label を使用して SCORE と組み合わせて結果を出力します。

   obclient> SELECT col1, col2, col3, SCORE(10), SCORE(20) 
       FROM tbl1
       WHERE CONTAINS (col2, 'Database', 10) > 0
       OR CONTAINS (col3, 'OceanBase', 20) > 0 
       ORDER BY SCORE(10), SCORE(20);
   

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

   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------------------+-------------------------+
   | COL1 | COL2                                               | COL3                                                                                                                | SCORE(10)              | SCORE(20)               |
   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------------------+-------------------------+
   |    2 | OceanBase                                          | OceanBase Database is a native, enterprise-level distributed database developed independently by the OceanBase team |                      0 | 1.1331643117640413E+000 |
   |    3 | Database Management                                | Learn about SQL and database administration                                                                         | 1.259496819494492E+000 |                       0 |
   +------+----------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+------------------------+-------------------------+
   2 rows in set
   

次のステップ

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

関連ドキュメント

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