CREATE INDEX文

create index文は、索引を作成し、指定された表の現在の行から計算されたエントリを索引に移入します。現在、Oracle NoSQL Databaseのすべての索引はBツリーとして実装されています。

構文

create_index_statement ::= 
   CREATE INDEX [IF NOT EXISTS] index_name 
   ON table_name "(" path_list ")" [WITH NO NULLS][comment]

index_name ::= id
path_list ::= index_path ("," index_path)*
index_path ::= 
   name_path [path_type] | 
   keys_expression | 
   values_expression [path_type] | 
   brackets_expression [path_type]
name_path ::= field_name ("." field_name)*
field_name ::= id | DSTRING
keys_expression ::= name_path "." KEYS "(" ")"
values_expression ::= 
   name_path "." VALUES "(" ")" ["." name_path]
brackets_expression ::= 
   name_path "[" "]" ["." name_path]
path_type ::= AS 
   (INTEGER | LONG | DOUBLE | STRING | 
   BOOLEAN | NUMBER | POINT | GEOMETRY)

セマンティクス

デフォルトでは、指定された索引が存在する場合、この文は失敗します。ただし、オプションのIF NOT EXISTS句が存在し、索引が存在し、索引指定が文の索引指定と一致する場合、エラーはレポートされません。索引が作成されると、関連表で行が挿入、削除または更新されると、Oracle NoSQL Databaseによって自動的に索引が更新されます。

索引は、索引の名前、索引を付ける表の名前、および1つ以上の索引パス式(または簡潔にするために索引パスのみ)のリストによって指定されます。この索引パス式は、次に説明するように、索引付けする表列またはネストされたフィールドを指定し、索引の実際のコンテンツを計算するためにも使用されます。JSONデータに索引付けするには、JSONデータへのパスの横にタイプを指定する必要があります(JSONの索引付けの項を参照)。索引名は、同じ表に作成された索引間で一意である必要があります。create index文には、索引メタデータの一部となる索引レベル・コメントを解釈されないテキストとして含めることができます。COMMENT文字列は、"DESCRIBE"文の出力に表示されます。最後に、create index文にはWITH NO NULLS句を含めることができます。これは、索引付きフィールドのNULLまたはEMPTY値を含む行に索引付けしないことを示します(これについては、この章の後半で説明します)。

索引には、多数の索引エントリが格納されます。索引エントリは、N + Kフィールド(Nはpath_list内のパスの数、Kは主キー列の数)を含む共通レコード・タイプ(索引スキーマ)を持つレコード項目として表示できます。最後のKのフィールドには、基礎となる表の行を指す主キーを構成する値が格納されます。最初のN個の各フィールド(索引フィールドと呼ばれる)には、索引付け可能な型の1つであるアトミック型(数値型、文字列、ブール、タイムスタンプまたは列挙)があります。索引フィールドには、NULL、json nullまたはEMPTYという特別な値のいずれかを格納することもできます(EMPTYはこの項の後半で説明します。json nullはJSONデータの索引に対してのみ可能です)。ただし、WITH NO NULLS句を使用した場合、NULLおよびEMPTY値はどの索引エントリにも表示されません。索引スキーマでは、索引フィールドはpath_listの対応する索引パスと同じ順序で表示されます。

索引エントリは昇順でソートされます。2つのエントリ間の相対位置は、フィールド値(文字列内で文字の役割を果たすフィールド値)を辞書順に(文字列と同様に)比較することによって決定されます。3つの特別な値は、他の値より大きいとみなされます(つまり、索引で最後にソートされます)。これら3つの値の中で、相対的な順序はEMPTY < json null < NULLです。

索引は、単純索引または複数キー索引(配列またはマップに索引付けする)として特徴付けることができます。どちらの場合も、表の行ごとに索引パス式が評価され、索引パス式から返された値に基づいて1つ以上の索引エントリが作成されます。これについては、次の項で詳しく説明します。

構文的には、問合せ内のパス式の構文のサブセットを使用して索引パスが指定されます(パス式を参照)。次の注釈/制限が索引パスに適用されます。

問合せパス式には入力式を含める必要がありますが、デフォルトでは入力は索引付けされている表の行であるため、索引パスにはこれは許可されません。
単一のname_pathは、1つ以上のドットで区切られたフィールド名のリストです。これらのフィールド名にはスキーマ・フィールド、つまり関連表の作成に使用されるCREATE TABLE文に表示されるフィールド名を使用できます。CREATE TABLE文の項のサンプル表を使用すると、ageおよびlastNameはUsers表の索引の有効な索引パスです。ただし、name_pathsにはマップのフィールド名も含まれる場合があります。たとえば、expenses.booksは有効な索引パスです。このパスでは、booksはスキーマ・フィールドではなく、各ユーザーにある場合とない場合がある経費カテゴリの名前です。
name_pathが表の行上(通常は1つの入力項目上)で評価される場合、最大で1つの項目を返す必要があります。また、評価中に配列を超えることはできません。これらの2つの制限を名前とパスの制限と呼びます。たとえば、otherNames.lastは、有効な問合せパス式であっても、索引パスとして使用できません(otherNamesは配列フィールドであるため)。ただし、同等のotherNames[].lastパス(単一のname_pathではない)をかわりに使用できます。
索引作成のコンテキストでは、name_pathが空の結果を返す場合、かわりに特別な値EMPTYが結果として使用されます。たとえば、パスexpenses.booksでは、書籍の経費カテゴリがないユーザーに対しては空の結果が返されますが、この空の結果はEMPTYに変換されます。そのため、実際には、各name_pathは正確に1つの項目を返します。
name_pathには、タイプがJSONのフィールドが含まれる場合があります。この場合、索引パスはjson索引パスであり、索引全体はjson索引になります。この項の残りの部分では、json以外の索引についてのみ説明します。json索引については、JSONの索引付けの項で説明します。CREATE INDEX文の構文のpath_type生成は、json索引パスにのみ必要です。型指定されたデータの索引パスには使用しないでください。
CREATE INDEX文の構文によって示されるように、フィルタリングは許可されません。つまり、.keys()、.values()および[]ステップでは、フィルタリング条件を使用できません。さらに、そのようなステップは、各索引パスに多くても1つのみ含まれます。
.keys()ステップはマップにのみ適用できます(レコードには適用できません)。

json索引については次の項で説明しているため、現在の項の残りの部分では、住所列がJSONタイプではなく、レコード・タイプのUsers表を使用します。具体的には、表定義は次のとおりです。

CREATE TABLE Users2 (
    id INTEGER,
    income INTEGER,
    address RECORD(
        street STRING,
        city STRING,
        state STRING,
        phones ARRAY(
            RECORD( 
                area INTEGER, 
                number INTEGER, 
                kind STRING
            )
        )
    ),
    connections ARRAY(INTEGER),
    expenses MAP(INTEGER),
    PRIMARY KEY (id)
);

また、Users2には、次のサンプル行(JSON形式で表示)が入力されていると想定します。次のjsonドキュメントのNULL値は、ドキュメントが表の行にマップされるときにSQL NULLに変換されることに注意してください。

INSERT INTO Users2 VALUES (
    0,
    1000,
    {
        "street" : "somewhere",
        "city": "Boston",
        "state" : "MA",
        "phones" : [ 
            { "area":408, "number":50, "kind":"work" },
            { "area":415, "number":60, "kind":"work" },
            { "area":NULL, "number":52, "kind":"home" }
        ]
    },
    [ 100, 20, 20, 10, 20],
    { "housing" : 1000, "clothes" : 230, "books" : 20 }
);

INSERT INTO Users2 VALUES (
    1,
    NULL,
    {
        "street" : "everywhere",
        "city": "San Fransisco",
        "state" : "CA",
        "phones" : [ 
            { "area":408, "number":50, "kind":"work" },
            { "area":408, "number":60, "kind":"home" }
        ]
    },
    [],
    { "housing" : 1000, "travel" : 300 }
);

INSERT INTO Users2 VALUES (
    2,
    2000,
    {
        "street" : "nowhere",
        "city": "San Jose",
        "state" : "CA",
        "phones" : [ ]
    },
    NULL,
    NULL
);