インデックスの作成

この記事では、インデックスを作成する方法について説明します。

背景

OceanBaseデータベースは、パーティションテーブルとパーティションテーブル以外の両方にインデックスを作成できます。インデックスはローカルインデックスまたはグローバルインデックス、一意インデックスまたは非一意インデックス、関数ベースのインデックスにすることができます。

この記事では主にパーティションテーブル以外のテーブルにインデックスを作成する方法について説明します。パーティションテーブルへのインデックス作成については、パーティションテーブルのインデックス作成を参照してください。

注意事項

インデックスを作成する際には、OceanBaseデータベースにおいてインデックス名がスキーマ範囲内で重複してはならないことに注意する必要があります。

通常インデックスの作成

CREATE INDEX ステートメントを使用して通常インデックスを作成できます。構文は以下のとおりです：

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name );

関連パラメータの説明は以下のとおりです：

• index_name：追加するインデックスの名前を指定します。

• UNIQUE：オプション。一意インデックスを示します。一意インデックスを作成する場合は、このキーワードを追加する必要があります。

• table_name：インデックスを追加するテーブルの名前を指定します。

• column_list：どの列にインデックスを付けるかを指定します。複数の列を指定する場合は、各列を英字のカンマで区切ります。

テーブル tbl2 を作成した後、インデックス tbl2_idx1 を作成する例は以下のとおりです：

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

   CREATE TABLE tbl2(id NUMBER,name VARCHAR2(20));
   

2. テーブル tbl2 にインデックス tbl2_idx1 を作成します。

   CREATE INDEX tbl2_idx1 ON tbl2(name);
   

3. インデックス情報を確認します。

   SELECT index_name,index_type,table_owner,table_name,uniqueness FROM user_indexes WHERE table_name='TBL2';
   

   結果は次のとおりです：

   +------------+------------+-------------+------------+------------+
   | INDEX_NAME | INDEX_TYPE | TABLE_OWNER | TABLE_NAME | UNIQUENESS |
   +------------+------------+-------------+------------+------------+
   | TBL2_IDX1  | NORMAL     | SYS         | TBL2       | NONUNIQUE  |
   +------------+------------+-------------+------------+------------+
   1 row in set
   

関数ベースのインデックスの作成

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

例えば、データ型が date の列 date_col を持つテーブル sale があるとします。

CREATE TABLE sale(date_col date, sales_col int, name_col varchar2(10));

3月のデータを取得する必要がある場合、to_char 関数を使用してデータを照会できます。

SELECT * FROM sale WHERE to_char(date_col,'MM') = 3;

このシナリオでは、データベースはテーブル内の各行に対して to_char(date_col,'MM') を計算し、to_char(date_col,'MM') = 3 に一致しない行をフィルタリングする必要があります。頻繁に月情報を使用してデータをフィルタリングする必要がある場合、毎回 to_char(date_col,'MM') を再計算する必要があり、これにより大量のオーバーヘッドが発生します。そのため、date_col に関数ベースのインデックスを作成し、to_char(date_col,'MM') をインデックステーブルに格納することで、クエリを高速化できます。

CREATE INDEX idx1 ON sale((to_char(date_col,'MM')));

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

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

関数ベースのインデックスの作成

CREATE INDEX ステートメントを使用して関数ベースのインデックスを作成できます。関数ベースのインデックスは一意インデックスにすることもできます。構文は以下のとおりです：

CREATE [UNIQUE] INDEX index_name ON table_name (expr);

関連パラメータの説明は以下のとおりです：

• [UNIQUE]：オプション。一意インデックスを示します。一意インデックスを作成する場合は、対応するキーワードを追加する必要があります。

• index_name：作成する関数ベースのインデックスの名前。

• table_name：インデックスを作成するテーブルの名前を指定します。

• expr：有効な関数ベースのインデックス式。ブール式は使用できません。例えば、c1=c1 はサポートされていません。

テーブルに c1+1 を定義する関数ベースのインデックス tbl2_func_idx1 を作成します。例：

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

   CREATE TABLE tbl2_func(c1 int, c2 int);
   

2. 関数ベースのインデックス tbl2_func_idx1 を作成します。

   CREATE INDEX tbl2_func_idx1 ON TBL2_FUNC ((c1+1));
   

3. 作成が成功したら、作成された関数ベースのインデックスを確認できます。

   SELECT index_name,index_type,table_owner,table_name,uniqueness FROM user_indexes WHERE table_name='TBL2_FUNC';
   

   結果は次のとおりです：

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

カラムストアインデックスの作成

CREATE INDEX ステートメントを使用してカラムストアインデックスを作成できます。構文は以下のとおりです：

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name ) [STORING(column_name, ...)] WITH COLUMN GROUP([all columns, ]each column);

関連パラメータの説明は以下のとおりです：

• index_name：追加するインデックスの名前を指定します。

• UNIQUE：オプション。一意のインデックスを示します。一意のインデックスを作成する場合は、このキーワードを追加する必要があります。

• table_name：インデックスを追加するテーブルの名前を指定します。

• column_list：どの列にインデックスを付けるかを指定します。複数の列を指定する場合は、各列を英字のカンマで区切ります。

• STORING(column_name, ...)：オプション。インデックステーブル上の冗長列を指定します。column_name 列がインデックスフィールドでなくても、カラムストア形式で冗長ストレージされます。

• WITH COLUMN GROUP([all columns, ]each column)：インデックスのカラムストア属性を指定します。

  • WITH COLUMN GROUP(all columns, each column)：行ストアとカラムストアの冗長インデックスを作成することを指定します。
  • WITH COLUMN GROUP(all columns)：行ストアインデックスを作成することを指定します。
  • WITH COLUMN GROUP(each column)：カラムストアインデックスを作成することを指定します。

例：

テーブル tbl3 を作成した後、カラムストアインデックス idx1_tbl3_cg を作成します。

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

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

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

   CREATE INDEX idx1_tbl3_cg ON tbl3(name) STORING(age) WITH COLUMN GROUP(each column);
   

空間インデックスの作成

CREATE INDEX ステートメントを使用して空間インデックスを作成できます。構文は以下のとおりです：

CREATE INDEX index_name ON table_name(column_g_name) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

関連パラメータの説明は以下のとおりです：

• index_name：追加するインデックスの名前を指定します。
• table_name：インデックスを作成するテーブルの名前を指定します。
• column_g_name：どの列にインデックスを付けるかを指定します。複数の列を指定する場合は、各列を英字のカンマで区切ります。空間インデックスを追加する列の型は、空間データ型である必要があります。

例：

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

   CREATE TABLE tbl4_g (col1 NUMBER PRIMARY KEY, col2 VARCHAR2(32), col3 SDO_GEOMETRY SRID 4326);
   

2. テーブルに空間インデックス tbl4_g_idx1 を作成します。

   CREATE INDEX tbl4_g_idx1 ON tbl4_g(col3) INDEXTYPE IS MDSYS.SPATIAL_INDEX;