インデックスの作成

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

インデックスの概要

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

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

前提条件

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

• OceanBaseクラスタをデプロイし、Oracleモードのテナントを作成していること。詳細な操作手順については、クラスタインスタンスの作成およびテナントの作成をご参照ください。

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

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

• INDEX権限と、インデックスを追加する対象のテーブルに対するALTER権限を保有していることを確認してください。現在のユーザー権限の確認に関する詳細な操作情報については、テナントアカウント管理をご参照ください。

インデックス作成の制限

• OceanBaseデータベースでは、インデックス名はテーブルの範囲内で一意である必要があります。

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

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

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

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

• インデックスが対象とする列と用途を簡潔に表す名前を使用することを推奨します。例：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モードでは、関数インデックスの式に制限があり、一部のシステム関数の式を関数インデックスとして使用することは禁止されています。具体的な関数のリストについては、関数インデックスでサポートされるシステム関数のリストおよび関数インデックスでサポートされないシステム関数のリストに関するドキュメントをご参照ください。

以下の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 dx_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 |
   +-----------------+-----------------------+-------------+------------+------------+
   | DX_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
   

次のステップ

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

関連ドキュメント

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