索引の分類
索引は、フィールド、スキーマ、エントリまたはそれらの組合せに基づいて分類できます。次に、それぞれについて説明します。
- フィールド
- 単一フィールド索引
- コンポジット索引
- スキーマ
- 固定スキーマ索引
- スキーマレス索引(JSON索引)
- エントリ
- 単純索引
- 複数キー索引
- 固定スキーマ索引
- 索引は、索引付けされるすべてのフィールドが強く型指定されたデータである場合、固定スキーマ索引と呼ばれます。強い型指定のデータの詳細は、「ワイルドカード・データ型」の項を参照してください。
- スキーマレス索引(JSON索引)
- 索引は、少なくとも1つのフィールドがJSONデータまたはJSONデータ内のフィールドである場合、JSON索引と呼ばれます。
図8-1 索引分類
単一フィールド索引
索引は、表の1つのフィールドにのみ作成される場合、単一フィールド索引と呼ばれます。
例8-2 単一フィールド索引
次に、単一フィールド索引の例を示します。索引は、UserInfo表のinfo JSONフィールドのaddressレコードに存在するcityフィールドに作成されます。
CREATE INDEX singlefieldindex1 ON UserInfo (
info.address.city AS ANYATOMIC
)コンポジット索引
索引は、表の複数のフィールドに作成されている場合、コンポジット索引と呼ばれます。
例8-3 コンポジット索引
次に、コンポジット索引の例を示します。索引は、UserInfo表のinfo JSONフィールドのaddressレコードに存在するstateおよびcityフィールドに作成されます。
CREATE INDEX compositeindex1 ON UserInfo (
info.address.state AS ANYATOMIC,
info.address.city AS ANYATOMIC
)固定スキーマ索引
固定スキーマ・データを持つフィールドに索引を作成できます。
例8-4 固定スキーマ索引
次に、固定スキーマ索引の例を示します。索引は、UsersInfo表の整数データ型を持つunameフィールドに作成されます。
CREATE INDEX fixedschemaindex1 ON UserInfo (uname)JSON索引
索引は、少なくとも1つのフィールドがJSONデータ内にある場合、JSON索引と呼ばれます。
JSONはスキーマレスであるため、索引付けされたJSONフィールドのデータ型は行間で異なることがあります。JSONフィールドに索引を作成するときに、JSONフィールドに必要なデータ型が不明な場合は、anyAtomicデータ型を使用できます。または、Oracle NoSQL Databaseアトミック・データ型のいずれかを指定できます。これを行うには、JSONフィールドへのすべての索引パスの横でASキーワードを使用してデータ型を宣言します。
例8-5 JSON索引
次に、JSON索引の例を示します。索引は、UserInfo表のinfo JSONフィールドに存在するincomeフィールドに作成されます。索引の作成時にincomeフィールドのデータ型を指定します。
CREATE INDEX jsonindex1 ON UserInfo (
info.income AS INTEGER
)宣言されたデータ型に違反するデータを持つ行が関連表に含まれている場合、JSON索引の作成は失敗します。同様に、JSON索引の作成後、新しい行がJSON索引で宣言されたデータ型に準拠していない場合、挿入/更新操作は失敗します。
たとえば、jsonindex1索引は、UserInfo表のすべての行のincomeフィールドが整数データ型の場合にのみ作成され、それ以外の場合はjsonindex1索引の作成は失敗します。同様に、UserInfo表にjsonindex1索引を作成した後は、incomeフィールドが整数データ型の行のみを挿入できます。たとえば、incomeフィールドが文字列データ型の行を挿入しようとすると、挿入文は失敗します。
- 数値
- 文字列
- ブール
ただし、この利点は領域およびCPUコストによって相殺されます。これは、索引付きフィールドの任意の種類の数値が、索引に格納される前に数値にキャストされるためです。このキャストにはCPU時間がかかり、結果として得られる数値の記憶域は、元の数値の記憶域より大きくなります。
例8-6 JSON索引の例
次に、JSON索引の例を示します。索引は、UserInfo表のinfo JSONドキュメントのaddressフィールドに存在するstreetフィールドに作成されます。索引の作成時にstreetフィールドにanyAtomicデータ型を指定することに注意してください。
CREATE INDEX jsonindex2 ON UserInfo (
info.address.street AS ANYATOMIC
)単純索引
索引は、表のデータ行ごとに1つのエントリが索引に作成される場合、単純索引と呼ばれます。索引は、アトミック・データ型の単一の値または任意の特別な値(SQL NULL、JSON NULL、EMPTY)を返します。基本的に、単純索引の索引パスには、.keys()、.values()または[]ステップを含めないでください。
例8-7 単純索引
次に、単純索引の例を示します。索引は、UserInfo表のinfo JSONフィールドに存在するincomeおよびageフィールドに作成されます。
CREATE INDEX simpleindex1 ON UserInfo (
info.income AS ANYATOMIC,
info.age AS ANYATOMIC
)配列は複数の索引値をレンダリングするため、単純な索引パスに配列を含めることはできません。たとえば、info.connections[]は、info.connections配列内の要素の完全なセットを返します。これは単純索引ではありません。
同様に、単純な索引パスには、.keys()または.values()を含むマップを含めないでください。たとえば、info.expenses.keys()は、expensesマップ内のキーの完全なセットを返します。これは単純な索引パスではありません。ただし、info.expenses.booksは単純な索引パスです。info.expensesがマップであっても、info.expenses.booksはアトミック値を返すためです。
例8-8 単純索引の例
次に、JSONフィールドのJSONドキュメントに作成される単純索引の例を示します。索引は、UserInfo表のinfo JSONフィールドにあるexpenses JSONドキュメントのbooks項目に作成されます。
CREATE INDEX simpleindex2 ON UserInfo (
info.expenses.books AS ANYATOMIC
)単純な索引パスの評価で空の結果が返された場合、特別な値EMPTYが索引エントリとして使用されます。前述の例では、expenses JSONドキュメントにbooksエントリがない場合、またはexpenses JSONドキュメントがない場合、特別な値EMPTYが索引付けされます。
複数キー索引
索引は、表のデータ行ごとに索引に複数のエントリが作成される場合、複数キー索引と呼ばれます。複数キー索引には、.keys()、.values()または[]ステップを使用する索引パスが少なくとも1つあります。このような索引パスはすべて、複数キー索引パスと呼ばれます。
複数キー索引では、表の行ごとに、索引付けされるマップ内の配列またはエントリのすべての要素に索引エントリが作成されます。評価で空の結果が返された場合は、特別な値EMPTYが索引エントリとして使用されます。重複する索引エントリは削除されます。
例8-9 複数キー索引
次に、複数キー索引の例を示します。索引は、UserInfo表のconnections[]配列に作成されます。ここでは、UserInfo表の各行のconnections[]配列のすべての要素が索引付けされます。
CREATE INDEX multikeyindex1 ON UserInfo (
info.connections[] AS ANYATOMIC
)ネストされた複数キー索引
索引は、別の配列内に存在する配列内に存在するフィールドに作成される場合、ネストされた複数キー索引です。
例8-10 ネストされた複数キー索引
次に、別の配列内に存在する配列にフィールドが存在する、ネストされた複数キー索引の例を示します。索引は、UserInfo表のinfo JSONにあるvehicles配列のvpass配列のissuedbyフィールドに作成されます。
CREATE INDEX multikeyindex2 ON UserInfo (
info.vehicles[].vpass[].issuedby AS ANYATOMIC
)コンポジット複数キー索引
索引は、索引が複数のフィールドに作成され、それらのフィールドの少なくとも1つが複数キーである場合、コンポジット複数キー索引と呼ばれます。
コンポジット複数キー索引には、複数キー索引パスと単純索引パスの組合せを使用できます。
例8-11 コンポジット複数キー索引
次に、1つの複数キー索引パスと1つの単純索引パスを持つコンポジット複数キー索引の例を示します。索引は、UserInfo表のinfo JSON列のincomeフィールドおよびareaフィールドに作成されます。
CREATE INDEX multikeyindex3 ON UserInfo (
info.income AS ANYATOMIC,
info.address.phones[].area AS ANYATOMIC
)コンポジット複数キー索引には、複数の複数キー索引パスを指定できます。コンポジット複数キー索引の可能性をいくつか、次に示します。
-
マップまたはJSONの
.keys()ステップと.values()ステップの両方を一緒に使用できます。次に、JSONドキュメントの
.keys()ステップと.values()ステップの両方が一緒に使用されるコンポジット複数キー索引の例を示します。索引は、UserInfo表のexpensesJSONドキュメントのkeysおよびvaluesに作成されます。CREATE INDEX multikeyindex4 ON UserInfo ( info.expenses.keys(), info.expenses.values() as ANYATOMIC ) -
array/record/maps-viewed-as-recordsの複数のフィールドを一緒に使用できます。ただし、ここでの制限は、フィールドを単一の
CREATE INDEX文内で配列とマップの両方として処理できないことです。次に、配列の複数のフィールドが一緒に使用されるコンポジット複数キー索引の例を示します。索引は、
UserInfo表のvpass配列のpassidおよびissuedbyフィールドに作成されます。CREATE INDEX nestedindex1 ON UserInfo ( info.vehicles[].vpass[].passid AS ANYATOMIC, info.vehicles[].vpass[].issuedby AS ANYATOMIC )図8-2 コンポジット複数キー索引
-
array/record/maps-viewed-as-recordsのフィールドと、それらのarray/record/maps-viewed-as-recordsの内部配列内に存在するフィールドを一緒に使用できます。ただし、ここでの制限は、そのようなすべてのフィールドの直属の親配列が同じである必要があることです。
次に、配列のフィールドと内部配列のフィールドが一緒に使用されるコンポジット複数キー索引の例を示します。索引は、
vehicles配列のvidフィールドおよびUserInfo表のvehicles配列にあるvpass配列のpassidフィールドに作成されます。CREATE INDEX nestedindex2 ON UserInfo ( info.vehicles[].vid AS ANYATOMIC, info.vehicles[].vpass[].passid AS ANYATOMIC )図8-3 コンポジット複数キー索引
次に、配列のフィールドと内部配列のフィールドが一緒に使用されるコンポジット複数キー索引の例を示しますが、2つのフィールドの直属の親配列は同じではありません。無効な索引作成文を次に示します。索引は、UserInfo表のvehicles配列のpassidおよびserviceidフィールドに作成されます。vehicles配列内では、passidはvpass配列内にあり、serviceidはvservice配列内にあることに注意してください。
CREATE INDEX invalidindex1 ON UserInfo (
info.vehicles[].vpass[].passid AS ANYATOMIC,
info.vehicles[].vservice[].serviceid AS ANYATOMIC
)図8-4 コンポジット複数キー索引(無効)
複数キー索引の仕様および制限
次の仕様および制限が複数キー索引パスに適用されます。
.keys()、.values()および[]ステップには述語式または境界式を指定できません。述語式および境界式の詳細は、それぞれ「マップフィルタ・ステップ式」および「配列スライス・ステップ式」の項を参照してください。- 複数キー索引パスが表の行で評価される場合、ゼロ以上のアトミック値を返す必要があります。値が返されない場合、特別な値EMPTYが使用されます。
keys()はデフォルトの文字列データ型であるため、.keys()ステップではデータ型の宣言は許可されません。.keys()ステップは、索引パスの最後のステップとしてのみ指定できます。- たとえば、
info.expenses.keys()は有効な索引パスですが、info.expenses.keys().booksは無効な索引パスです。 .keys()および.values()は、強い型指定のデータのマップであるフィールドの後にのみ指定できます。.keys()および.values()ステップは、アトミック値またはJSONデータ内のJSONドキュメントであることが予想されるフィールドの後に指定できます。- フィールドがアトミック値の場合は、特別な値EMPTYが返されます。
- フィールドがjsonドキュメントの場合は、対応するキーまたは値が返されます。
- 強い型指定のデータの配列であるフィールドの後に、
[]ステップを指定する必要があります。 - JSONデータ内の配列であることが予想されるフィールドの後に、
[]ステップを指定する必要があります。 - JSONデータ内の配列であることが予想されるフィールドの後に
[]ステップを指定しない場合、それらのフィールドは配列にできません。- 既存の表の行にJSONデータ内の配列が含まれている場合、索引の作成は失敗します。
- ただし、JSONデータ内の配列が含まれている既存の表に行がない場合、索引は正常に作成されます。ただし、そのJSONデータの配列を含む新しい行を既存の表に挿入することはできません。
- たとえば、次の
badindex1では、[]ステップはvehicles配列では使用されません。2つのシナリオがあります:CREATE INDEX badindex1 ON UserInfo(info.vehicles.vid AS ANYATOMIC)vehiclesフィールドが配列であるUserInfo表に既存のデータ行がある場合、索引の作成は失敗します。UserInfo表にvehicles配列を持つ既存のデータ行がない場合、索引は正常に作成されます。ただし、vehicles属性が配列であるデータの新しい行を挿入しようとすると、行の挿入は失敗します。
- 強い型指定のデータの非配列フィールドの後に
[]ステップを指定できません。 - JSONデータのアトミック値およびJSONドキュメントの後に
[]ステップを指定できます。その場合、そのアトミック値またはJSONドキュメントが使用されます。 - 配列に別の配列が直接含まれている場合、内部配列の要素を索引付けするには、対応する数の
[]ステップが必要です。たとえば、vehicles内に2つの内部配列がある場合は、info.vehicles[][].vidを使用する必要があります。
JSONコレクション表の索引
JSONコレクション表のフィールドに索引付けできます。型定義に索引付き要素の名前とANYATOMICを指定する必要があります。または、強い型指定索引の場合は、索引付けされるフィールドのJSON型を指定できます。
JSONコレクション表のフィールドの索引付けは、JSON索引の作成に似ています。フィールドにパス式を指定する必要があります。ドキュメントの最上位JSONフィールドに索引付けする場合、フィールド名はそのパス式です。要素がJSONオブジェクトに深くネストされている場合は、完全なパス名を指定します。いずれの場合も、すべての索引のデータ型を指定する必要があり、JSONコレクション表ではANYATOMICを使用することをお薦めします。
ショッピング・アプリケーション用に作成されたJSONコレクション表について考えてみます。
例8-12 JSONコレクション表の索引の作成
create index myindex on usersJSON(notify as ANYATOMIC)前述の文では、買物客のデータのnotifyフィールドに型指定なしの索引を作成しています。
例8-13 JSONコレクション表のコンポジット索引の作成
create index idx_ntfy_cty on storeAcct (address.city as ANYATOMIC, notify as ANYATOMIC)notifyフィールドは最上位フィールドで、cityフィールドはstoreAcct表のaddressフィールドにネストされています。この文では、これら2つのフィールドを使用してコンポジット索引を作成します。
ノート:
ネストされたJSONフィールドに索引を作成する場合、そのフィールドは表のすべての行に存在する必要があります。それ以外の場合は、エラーが表示されます。例8-14 JSONコレクション表の強い型指定の索引の作成
create index myindex2 on usersJSON(notify as string)前述の文では、買物客のデータの最上位のnotifyフィールドに文字列索引を作成しています。
宣言されたデータ型に違反するデータを持つ行が表に含まれていると、強い型指定の索引の作成は失敗します。また、この索引が正常に作成された後、notifyフィールドに挿入できるのは文字列データのみです。強い型指定の索引を使用すると、JSONフィールドで索引と型制約の両方として機能させることができます。
DROP INDEX文を使用すると、JSONコレクション表の索引を削除できます。詳細は、「DROP INDEX文」を参照してください。
関数の索引
1つ以上のSQL組込み関数の値に対して索引を作成できます。
索引付けできる関数のリスト:
「組込み関数」の次のサブセットに索引付けできます。
- year
- month
- day
- hour
- minute
- second
- millisecond
- microsecond
- nanosecond
- week
- 長さ
- replace
- reverse
- substring
- trim
- ltrim
- rtrim
- lower
- upper
- modification_time
- expiration_time
- expiration_time_millis
- row_storage_size
組込み関数とその使用方法の詳細は、「組込み関数」を参照してください。
関数の索引の作成例
1つ以上のSQL組込み関数の値に対して索引を作成できます。
行プロパティ関数の索引の作成
- modification_time
- expiration_time
- expiration_time_millis
- row_storage_size
CREATE INDEX文では、引数なしでこれらの関数を指定する必要があります。索引付けする行は、関数への入力として暗黙的に考慮されます。
例:
Users表の行に最新の変更時間で索引付けする索引を作成します:CREATE INDEX idx_modtime ON users(modification_time())modification_timeを持つ問合せで使用されます。SELECT * FROM Users $u WHERE
modification_time($u) > "2022-08-01T10:45:00"この問合せでは、最新の変更時間が2022-08-01T10:45:00より後のすべての行が返されます。前述のidx_modtime 索引を使用します。これを確認するには、show queryコマンドを使用して問合せ計画を表示します。
関数の索引の作成例
ノート:
JSON以外の場合、データ型が表の定義と同じであるため、パス・タイプは必要ありません。JSONデータの場合、タイプANYATOMICを使用して、JSONのすべての有効なタイプに対応できます。
- 索引付けできるすべての組込み関数は、最初の引数として最大1つの項目が必要です。
- YEAR、MONTHなどの時間関連関数は、入力が複数の項目を持つ順序であり、length、reverseなどの文字列関連関数がNULLを返した場合、例外をスローします。
- 入力索引パスに複数キーがある場合、関数は複数キー・パスによって返される値ごとに個別に評価されます。結果の項目の順序は、索引定義内の他の索引パスの値(ある場合)と組み合され、索引エントリが作成されます。
スクリプトacctstream_loaddata.sqlをダウンロードして、次に示すように実行します。このスクリプトでは、例で使用するstream_acct表が作成され、その表にデータがロードされます。
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstoreloadコマンドを使用して、スクリプトを実行します。load -file acctstream_loaddata.sqlstream_acct表に索引を作成し、居住国の最初の3文字でユーザーに索引付けします。CREATE INDEX idx_country ON stream_acct(substring(acct_data.country as string, 0,3))- 前述の例では、acct_data JSONのcountryフィールドを文字列として宣言しています。
- substring関数を使用して国の最初の3文字を抽出し、これを索引キーとして使用します。
例2: 複数キー索引パスの使用
stream_acct表のユーザーに対して、視聴している番組のIDと、番組が視聴された日付の年および月で索引付けします。CREATE INDEX idx_showid_year_month ON
stream_acct(acct_data.contentStreamed[].showId AS INTEGER,
substring(acct_data.contentStreamed[].seriesInfo[].episodes[].date AS STRING,0, 4),
substring(acct_data.contentStreamed[].seriesInfo[].episodes[].date AS STRING,5, 2))SELECT count(*) FROM stream_acct s1 WHERE EXISTS
s1.acct_data.contentStreamed[$element.showId = 16].seriesInfo.
episodes[substring($element.date, 0, 4) = "2022"]idx_showid_year_monthを使用します。これを確認するには、show queryコマンドを使用して問合せ計画を表示します。show query SELECT count(*) FROM stream_acct s1 WHERE EXISTS
> s1.acct_data.contentStreamed[$element.showId = 16].seriesInfo.episodes[substring($element.date, 0, 4) = "2022"]
{
"iterator kind" : "GROUP",
"input variable" : "$gb-1",
"input iterator" :
{
"iterator kind" : "RECEIVE",
"distribution kind" : "ALL_SHARDS",
"distinct by fields at positions" : [ 1 ],
"input iterator" :
{
"iterator kind" : "SELECT",
"FROM" :
{
"iterator kind" : "TABLE",
"target table" : "stream_acct",
"row variable" : "$$s1",
"index used" : "idx_showid_year_month",
"covering index" : true,
"index row variable" : "$$s1_idx",
"index scans" : [
{
"equality conditions" : {"acct_data.contentStreamed[].showId":16,"substring#acct_data.contentStreamed[].seriesInfo[].episodes[].date@,0,4":"2022"},
"range conditions" : {}
}
]
},
"FROM variable" : "$$s1_idx",
"SELECT expressions" : [
{
"field name" : "Column_1",
"field expression" :
{
"iterator kind" : "CONST",
"value" : 1
}
},
{
"field name" : "acct_id_gen",
"field expression" :
{
"iterator kind" : "FIELD_STEP",
"field name" : "#acct_id",
"input iterator" :
{
"iterator kind" : "VAR_REF",
"variable" : "$$s1_idx"
}
}
}
]
}
},
"grouping expressions" : [
],
"aggregate functions" : [
{
"iterator kind" : "FUNC_COUNT_STAR"
}
]
}