インデックスの作成

このセクションでは、インデックスの作成方法について説明します。

背景

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

sale というテーブルがあり、そのテーブルに date 型の列 date_col があると仮定します：

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;
   

フルテキストインデックスの作成

OceanBaseデータベースのOracleモードでは、テーブル作成後に CREATE INDEX ... INDEXTYPE IS CTXSYS.CONTEXT ... ステートメントを使用してフルテキストインデックスを作成できます。フルテキストインデックスの詳細については、フルテキストインデックスを参照してください。

フルテキストインデックスの制限事項と注意点

• 以下のタイプのテーブルでフルテキストインデックスを作成できます：

  • 通常テーブル
  • パーティションテーブル（Rangパーティション、Hashパーティション、Listパーティション、複合パーティション）
  • レプリケーションテーブル
  • ヒープ組織化テーブル（ヒープテーブル）
  • カラムストアテーブル

• 現在のバージョンでは、フルテキストインデックスは CHAR、VARCHAR、VARCHAR2、NCHAR、NVARCHAR2、および CLOB 型の列にのみ適用できます。

• 現在のバージョンでは、ローカル（LOCAL）フルテキストインデックスのみをサポートしています。

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

• 複数列に対するフルテキストインデックスの共同作成はサポートされていません。

• 生成列に対するフルテキストインデックスの作成はサポートされていません。

• ビューに対するフルテキストインデックスの作成はサポートされていません。

• フルテキストインデックスと通常インデックスの名前が同じであることはサポートされていません。

• フルテキストインデックスを持つ列の削除はサポートされていません。

• フルテキストインデックス列の型を整数型に変更することはサポートされていません。

フルテキストインデックス作成構文

CREATE INDEX ... INDEXTYPE IS CTXSYS.CONTEXT ... ステートメントを使用して既存のテーブルにフルテキストインデックスを作成する構文は以下のとおりです：

CREATE INDEX [schema.]index_name ON [schema.]table_name(column_name)
    INDEXTYPE IS CTXSYS.CONTEXT
    [PARAMETERS('LEXER parser_name')]
    LOCAL;

parser_name:
    SPACE
    | NGRAM
    | NGRAM2
    | IK
    | BENG

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

• schema.：オプションです。他のスキーマのテーブルにインデックスを作成する必要がある場合は、そのスキーマ名を追加する必要があります。schema. を省略した場合、デフォルトでそのインデックス/テーブルは現在のスキーマに存在します。

• index_name：作成するインデックス名を指定します。

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

• column_name：インデックスを作成する列を指定します。

• PARAMETERS('LEXER parser_name')：オプションです。フルテキストインデックスのトークナイザーを指定します。PARAMETERS('LEXER parser_name') を明示的に指定しない場合、トークナイザーはデフォルトで SPACE となり、英語などスペースで意味が区切られる言語に適用されます。parser_name はトークナイザー名を表し、取り得る値は以下のとおりです：

  • SPACE：デフォルト値で、スペースによるトークン化を表します。
  • NGRAM：N-Gram（中国語）に基づくトークン化方式を表します。
  • NGRAM2：テキストを設定範囲の連続文字に分割することを表します。
  • IK：IK（中国語）に基づくトークン化方式を表します。
  • BENG：Beng（基本英語）に基づくトークン化方式を表します。

  トークナイザーの選択戦略の詳細については、フルテキストインデックス の フルテキストインデックスのトークナイザー セクションを参照してください。

• LOCAL：ローカルインデックスを作成することを指定します。

例：

tbl5 テーブルを作成した後、全文インデックス ft_idx1_tbl5 を作成します。

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

   obclient> CREATE TABLE tbl5(col1 INT, col2 VARCHAR2(50));
   

2. テーブル tbl5 に全文インデックス ft_idx1_tbl5 を作成し、全文インデックスのトークナイザーを IK に指定します。

   obclient> CREATE INDEX ft_idx1_tbl5 ON tbl5(col2)
       INDEXTYPE IS CTXSYS.CONTEXT
       PARAMETERS('LEXER IK')
       LOCAL;
   

関連ドキュメント

• CREATE INDEX 構文の詳細については、CREATE INDEXを参照してください。
• 全文インデックスを使用したデータのクエリに関する情報については、全文検索を参照してください。