APIを使用した索引の作成
SQLコマンドの使用
索引は、CREATE INDEXコマンドを使用して作成できます。
単一のフィールド索引の作成:
CREATE INDEX fixedschema_conf ON baggageInfo(confNo)これは単一列の固定スキーマ索引の例です。索引は、baggageInfo表のstringデータ型を持つconfNoフィールドに対して作成されます。
コンポジット索引の作成:
CREATE INDEX compindex_namephone ON baggageInfo(fullName,contactPhone)ノート:
この索引の1つ以上のフィールドを固定スキーマ列として指定できます。JSON索引の作成:
インデックスは、少なくとも1つのフィールドが JSONデータ内にある場合、JSONインデックスと呼ばれます。JSONはスキーマレスであるため、索引付けされたJSONフィールドのデータ型は行間で異なることがあります。JSONフィールドに索引を作成するときに、JSONフィールドに必要なデータ型が不明な場合は、anyAtomicデータ型を使用できます。または、Oracle NoSQL Databaseアトミック・データ型のいずれかを指定できます。これを行うには、JSONフィールドへのすべての索引パスの横でASキーワードを使用してデータ型を宣言します。
CREATE INDEX jsonindex_tagnum ON baggageInfo(bagInfo[].tagnum as INTEGER)これはJSON索引の例です。索引は、baggageInfo表のbaginfo JSONフィールドに存在するtagnumフィールドに対して作成されます。索引の作成時にtagnumフィールドのデータ型を指定します。
宣言されたデータ型に違反するデータを持つ行が関連表に含まれている場合、JSON索引の作成は失敗します。同様に、JSON索引の作成後、新しい行がJSON索引で宣言されたデータ型に準拠していない場合、挿入/更新操作は失敗します。
CREATE INDEX jsonindex_routing ON baggageInfo(bagInfo[].routing as ANYATOMIC)- 数値
- 文字列
- ブール値
ただし、この利点は領域およびCPUコストによってオフセットされます。これは、索引付きフィールドの任意の種類の数値が、索引に格納する前に数値にキャストされるためです。このキャストにはCPU時間がかかり、結果として得られる数値の記憶域は、元の数値の記憶域より大きくなります。
単純索引の作成:
インデックスは、テーブル内の各データ行に対してインデックスに1つのエントリが作成される場合、単純インデックスと呼ばれます。索引は、アトミック・データ型の単一の値または任意の特別な値(SQL NULL、 JSON NULL、 EMPTY)を返します。基本的に、単純索引パスは、配列、マップまたはネストされたデータ型を返すことはできません。
CREATE INDEX simpleindex_arrival ON baggageInfo(bagInfo[].lastSeenTimeGmt as ANYATOMIC,
bagInfo[].bagArrivalDate as ANYATOMIC, bagInfo[].lastSeenTimeStation as ANYATOMIC)これはJSONフィールドのJSONドキュメントに対して作成される単純索引の例です。索引は、lastSeenTimeGmt、bagArrivalDateおよびlastSeenTimeStationに対して作成されます。これらはすべて、baggageInfo表のinfo JSONフィールドのbagInfo JSONドキュメントからのフィールドです。単純な索引パスの評価で空の結果が返された場合、特別な値EMPTYが索引エントリとして使用されます。前述の例で、bagInfo JSONドキュメントにlastSeenTimeGmt、bagArrivalDateまたはlastSeenTimeStationエントリがない場合、あるいはbagInfo JSON配列がない場合は、特殊な値EMPTYが索引付けされます。
複数キー索引の作成:
インデックスは、テーブル内の各データ行に対してインデックスに複数のエントリが作成される場合、マルチキーインデックスと呼ばれます。複数キー索引には、配列またはネストされたデータ型を使用する索引パスが少なくとも1つ存在します。複数キー索引では、表の行ごとに、索引付けされる配列のすべての要素に対して索引エントリが作成されます。
CREATE INDEX multikeyindex1 ON stream_acct (acct_data.contentStreamed[].seriesInfo[] AS ANYATOMIC)索引は、stream_acct表のseriesInfo[]配列に対して作成されます。ここでは、stream_acct表の各行に含まれるseriesInfo[]配列のすべての要素が索引付けされます。
例2:ネストされた複数キー索引: ストリーミング・アカウント・アプリケーションのエピソード詳細配列に対する索引を作成します。
CREATE INDEX multikeyindex2 ON stream_acct (
acct_data.contentStreamed[].seriesInfo[].episodes[] AS ANYATOMIC)これは別の配列内に存在する配列にフィールドが存在する、ネストされた複数キー索引の例です。索引は、stream_acct表のacct_data JSONのseriesInfo[]配列にあるepisodes[]配列に対して作成されます。
例3:コンポジット複数キー索引:
CREATE INDEX multikeyindex3 ON stream_acct (acct_data.country AS ANYATOMIC,
acct_data.contentStreamed[].seriesInfo[].episodes[] AS ANYATOMIC)The above is an example of a composite multikey index having one multikey index path and one simple index path. The index is created on the country field and episodes[]array in the acct_data JSON column of the stream_acct table.
複数キー索引の制限については、複数キー索引の仕様および制限に関する項を参照してください。
NO NULLS句を使用した索引の作成
CREATE INDEX nonull_phone ON baggageInfo (contactPhone) WITH NO NULLS- 前述の問合せでは、乗客の電話番号に対する索引が作成されます。一部の乗客に電話番号がない場合、それらのフィールドは索引に含まれません。
- WITH NO NULLS句を使用して作成された索引は、索引付けされたフィールドにNULL値またはEMPTY値(あるいはその両方)が大量に含まれている場合に便利です。これにより、索引付け時の時間と領域のオーバーヘッドが削減されます。
- ただし、問合せによるこのような索引の使用は制限されています。WITH NO NULLS句を指定して索引を作成する場合、IS NULL条件とNOT EXISTS条件は、その索引の索引条件として使用できません。
- 実際には、そのような索引は、問合せの索引付きフィールドごとに索引条件がある場合にのみ、問合せで使用できます。
行ごとに一意キーを使用した索引の作成
CREATE INDEX idx_showid ON
stream_acct(acct_data.contentStreamed[].showId AS INTEGER)
WITH UNIQUE KEYS PER ROW前述の問合せでは、索引はshowIdに対して作成され、単一のcontentStreamed配列に重複するshowIdは存在できません。これにより、問合せプロセッサは、ストリーミング・ユーザーの場合、contentStreamed配列に同じshow IDを持つ2つ以上のショーを含めることはできないという通知を受信します。重複したshow IDが存在する場合は索引に含まれないため、この制限が必要となります。1つの contentStreamed配列に、同じ showIdを持つ2つ以上の項目が含まれる行を挿入すると、エラーが発生し、挿入操作は成功しません。
問合せ実行時間の最適化:
行ごとに一意キーを持つ索引を作成すると、索引に含まれるエントリはcontentStreamed配列の要素数より少なくなります。この索引を使用する効率的な問合せを記述できます。このようなインデックスをクエリーで使用すると、インデックスが使用されなかった場合よりもFROM句の結果が少なくなります。
関数の索引の作成例:
BaggageInfo表の行に最新の変更時間で索引付けする索引を作成します:CREATE INDEX idx_modtime ON BaggageInfo(modification_time())modification_timeを持つ問合せで使用されます。SELECT * FROM BaggageInfo $u WHERE
modification_time($u) > "2019-08-01T10:45:00"この問合せでは、最新の変更時間が2019-08-01T10:45:00より後のすべての行が返されます。前述のidx_modtime索引を使用します。これを確認するには、show queryコマンドを使用して問合せ計画を表示します。
BaggageInfo表の行にルーティング・フィールドの長さで索引付けする索引を作成します。CREATE INDEX idx_routlen ON BaggageInfo (length(bagInfo[].routing as string))lengthを持つ問合せで使用されます。SELECT * from BaggageInfo $bag where length($bag.bagInfo[].routing) > 10この問合せでは、ルーティング・フィールドの長さが10より大きいすべての行が返されます。前述のidx_routlen索引を使用します。これを確認するには、show queryコマンドを使用して問合せ計画を表示します。
例3:複数キーの索引パスの使用
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"
}
]
}TableRequest APIの使用
TableRequest APIを使用して、NoSQL表に索引を作成できます。
TableRequestクラスは、表に索引を作成するために使用されます。このリクエストで指定された操作の実行は非同期です。これらは長時間実行される可能性があります。TableRequest操作からTableResultが返され、表の状態がカプセル化されます。TableRequestクラスとそのメソッドの詳細は、Oracle NoSQL Java SDK APIリファレンスを参照してください。
/**
* Create an index acct_episodes in the stream_acct table
*/
private static void crtIndex(NoSQLHandle handle) throws Exception {
String createIndexDDL = "CREATE INDEX acct_episodes ON " + tableName +
"(acct_data.contentStreamed[].seriesInfo[].episodes[] AS ANYATOMIC)";
TableRequest treq = new TableRequest().setStatement(createIndexDDL);
TableResult tres = handle.tableRequest(treq);
tres.waitForCompletion(handle, 60000, /* wait 60 sec */
1000); /* delay ms for poll */
System.out.println("Index acct_episodes on " + tableName + " is created");
}borneo.TableRequestクラスは、表に索引を作成するために使用されます。borneo.NoSQLHandle.table_request()へのすべてのコールは非同期であるため、結果を確認し、borneo.TableResult.wait_for_completion()をコールして操作が完了するまで待機する必要があります。table_requestおよびそのメソッドの詳細は、Oracle NoSQL Python SDK APIリファレンスを参照してください。
#create an index
def create_index(handle):
statement = '''CREATE INDEX acct_episodes ON stream_acct (acct_data.contentStreamed[].seriesInfo[].episodes[] AS ANYATOMIC)'''
request = TableRequest().set_statement(statement)
table_result = handle.do_table_request(request, 40000, 3000)
table_result.wait_for_completion(handle, 40000, 3000)
print('Index acct_episodes on the table stream_acct is created')TableRequestクラスは、表に索引を作成するために使用されます。TableRequestで指定された操作の実行は非同期です。これらは長時間実行される可能性があります。このリクエストは、Client.DoTableRequest()操作の入力として使用され、表が目的の状態になるまでポーリングに使用できるTableResultを返します。TableRequestクラスの様々なメソッドの詳細は、Oracle NoSQL Go SDK APIリファレンスを参照してください。
//create an index on a table
func createIndex(client *nosqldb.Client, err error, tableName string)(){
stmt := fmt.Sprintf("CREATE INDEX acct_episodes ON %s "+
"(acct_data.contentStreamed[].seriesInfo[].episodes[] AS ANYATOMIC)",tableName)
tableReq := &nosqldb.TableRequest{
Statement: stmt,
}
tableRes, err := client.DoTableRequest(tableReq)
if err != nil {
fmt.Printf("cannot initiate CREATE INDEX request: %v\n", err)
return
}
// The create index request is asynchronous, wait for index creation to complete.
_, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
if err != nil {
fmt.Printf("Error finishing CREATE INDEX request: %v\n", err)
return
}
fmt.Println("Created Index acct_episodes on table ", tableName)
return
}tableDDLメソッドを使用して、表に索引を作成できます。このメソッドは非同期で、TableResultのPromiseを返します。TableResultは、表の状態をカプセル化するプレーンなJavaScriptオブジェクトです。メソッドの詳細は、NoSQLClientクラスを参照してください。
//creates an index
async function createIndex(handle) {
const crtindDDL = `CREATE INDEX acct_episodes ON ${TABLE_NAME}(acct_data.contentStreamed[].seriesInfo[].episodes[] AS ANYATOMIC)`;
let res = await handle.tableDDL(crtindDDL);
console.log('Index acct_episodes is created on table:' + TABLE_NAME);
} 表に索引を作成するには、メソッドExecuteTableDDLAsyncまたはExecuteTableDDLWithCompletionAsyncのいずれかを使用します。これらのメソッドはどちらもTask<TableResult>を返します。TableResultインスタンスには、TableStateなどのDDL操作および表スキーマのステータスが含まれます。これらのメソッドの詳細は、Oracle NoSQL Dotnet SDK APIリファレンスを参照してください。
// Creates an index on a table
private static async Task createIndex(NoSQLClient client){
var sql =
$@"CREATE INDEX acct_episodes ON {TableName}(acct_data.contentStreamed[].seriesInfo[].episodes[] AS ANYATOMIC)";
var tableResult = await client.ExecuteTableDDLAsync(sql);
// Wait for the operation completion
await tableResult.WaitForCompletionAsync();
Console.WriteLine(" Index acct_episodes is created on table Table {0}",
tableResult.TableName);
}