索引の作成および管理
索引は、データベース表からデータを効率的に取得できるようにするデータベース構造です。索引を使用すると、検索対象のキーが索引に含まれている場合に表の行にすばやくアクセスできます。
- NULL
- EMPTY
- json null (JSON索引にのみ適用可能)
例のとおりに操作する場合は、スクリプトbaggageschema_loaddata.sqlおよびacctstream_loaddata.sqlをダウンロードし、次に示すように実行します。このスクリプトにより、例で使用する表が作成され、表にデータがロードされます。
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstoreloadコマンドを使用して、スクリプトを実行します。load -file baggageschema_loaddata.sql
load -file acctstream_loaddata.sql索引の分類
索引は、フィールド、スキーマ、エントリまたはそれらの組合せに基づいて分類できます。
単一フィールド索引: 索引は、表の1つのみのフィールドに対して作成される場合、単一フィールド索引と呼ばれます。
コンポジット索引: 索引は、表の複数のフィールドに対して作成される場合、コンポジット索引と呼ばれます
ノート:
データ型は、ワイルドカード・タイプのいずれでもない場合は正確とみなされます。正確な型を持つ項目は、厳密に型指定されます。スキーマレス索引(JSON索引): 索引は、少なくとも1つのフィールドがJSONデータまたはJSONデータ内のフィールドである場合、JSON索引と呼ばれます。
単純索引: 索引は、表のそれぞれのデータ行に対して索引に1つのエントリが作成される場合、単純索引と呼ばれます。
複数キー索引: 索引は、表のそれぞれのデータ行に対して索引に複数のエントリが作成される場合、複数キー索引と呼ばれます。
1つ以上のSQL組込み関数の値に対して索引を作成できます。
索引付けできる関数のリスト:
組込み関数の次のサブセットに索引付けできます。
- year
- month
- day
- hour
- minute
- second
- millisecond
- microsecond
- nanosecond
- week
- length
- replace
- reverse
- substring
- trim
- ltrim
- rtrim
- lower
- upper
- modification_time
- expiration_time
- expiration_time_millis
- row_storage_size
組込み関数とその使用方法の詳細は、組込み関数を参照してください。
索引の作成
索引は、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)これは1つの複数キー索引パスと1つの単純索引パスを持つコンポジット複数キー索引の例です。索引は、stream_acct表のacct_data JSON列のepisodes[]配列にあるcountryフィールドに対して作成されます。
複数キー索引の制限について学習するには、複数キー索引の仕様および制限に関する項を参照してください。
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を含めることはできないという通知を受信します。重複した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表のユーザーに対して、視聴しているshowのIDと、showが視聴された日付の年および月で索引付けします。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"
}
]
}APIの使用による索引の作成:
/**
* Create a table and set the desired table capacity
*/
private static void createTab(NoSQLHandle handle) throws Exception {
String createTableDDL = "CREATE TABLE IF NOT EXISTS " + tableName +
"(acct_Id INTEGER," +
"profile_name STRING," +
"account_expiry TIMESTAMP(1) ," +
"acct_data JSON, " +
"PRIMARY KEY(acct_Id))";
TableLimits limits = new TableLimits(20, 20, 1);
TableRequest treq = new TableRequest()
.setStatement(createTableDDL).setTableLimits(limits);
TableResult tres = handle.tableRequest(treq);
/* The request is async,
* so wait for the table to become active.
*/
tres.waitForCompletion(handle, 60000, /* wait 60 sec */
1000); /* delay ms for poll */
System.out.println("Table " + tableName + " is active");
}
/**
* 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");
}# Create a table and set the table limits
def create_table(handle):
statement = '''create table if not exists stream_acct (acct_Id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
primary key(acct_Id))'''
request = TableRequest().set_statement(statement).set_table_limits(TableLimits(20, 10, 1))
table_result = handle.do_table_request(request, 40000, 3000)
table_result.wait_for_completion(handle, 40000, 3000)
if (table_result.get_state() == State.ACTIVE):
print('Created table: stream_acct')
else:
raise NameError('Table stream_acct is in an unexpected state ' + str(table_result.get_state()))
#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')// Creates a table
func createTable(client *nosqldb.Client, err error, tableName string)(){
stmt := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s ("+
"acct_Id INTEGER," +
"profile_name STRING," +
"account_expiry TIMESTAMP(1) ," +
"acct_data JSON, " +
"PRIMARY KEY(acct_Id))",tableName)
tableReq := &nosqldb.TableRequest{
Statement: stmt,
TableLimits: &nosqldb.TableLimits{
ReadUnits: 20,
WriteUnits: 20,
StorageGB: 1,
},
}
tableRes, err := client.DoTableRequest(tableReq)
if err != nil {
fmt.Printf("cannot initiate CREATE TABLE request: %v\n", err)
return
}
// The create table request is asynchronous, wait for table creation to complete.
_, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
if err != nil {
fmt.Printf("Error finishing CREATE TABLE request: %v\n", err)
return
}
fmt.Println("Created table ", tableName)
return
}
//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
}//creates a table
async function createTable(handle) {
const createDDL = `CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (acct_Id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
primary key(acct_Id))`;
console.log('Create table: ' + createDDL);
let res = await handle.tableDDL(createDDL, {
complete: true,
tableLimits: {
readUnits: 20,
writeUnits: 20,
storageGB: 1
}
});
console.log('Table created: ' + TABLE_NAME);
}
//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);
}// Create a table
private static async Task createTable(NoSQLClient client){
var sql =
$@"CREATE TABLE IF NOT EXISTS {TableName}(acct_Id INTEGER,
profile_name STRING,
account_expiry TIMESTAMP(1),
acct_data JSON,
primary key(acct_Id))";
Console.WriteLine("\nCreate table {0}", TableName);
var tableResult = await client.ExecuteTableDDLAsync(sql,
new TableDDLOptions{
TableLimits = new TableLimits(20, 20, 1)
});
// Wait for the operation completion
await tableResult.WaitForCompletionAsync();
Console.WriteLine(" Table {0} is created", tableResult.TableName);
Console.WriteLine(" Table state: {0}", tableResult.TableState);
}
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);
}索引の表示
データベース内の索引を表示できます。
SHOW INDEXES
SHOW INDEXES文は、指定した表に存在する索引のリストを提供します。出力をJSON形式にする場合、オプションのAS JSONを指定できます。
BaggageInfo表に対する索引をリストします。SHOW INDEXES ON baggageInfo
indexes
jsonindex_routing
jsonindex_tagnum
simpleindex_arrival
nonull_phoneBaggageInfo表に対する索引をJSON形式でリストします。SHOW AS JSON INDEXES ON baggageInfo
{"indexes" :
["jsonindex_routing","jsonindex_tagnum","simpleindex_arrival"]}DESCRIBE INDEX
DESCRIBE INDEX文は、表の指定された索引を定義します。出力をJSON形式にする場合、オプションのAS JSONを指定できます。
- 索引が定義される表の名前。
- 索引の名前。
- 索引のタイプ。索引が主索引であるか2次索引であるか。
- 索引が複数キーかどうか。索引が複数キーの場合は、「Y」が表示されます。それ以外の場合は、「N」が表示されます。
- 索引が定義されるフィールドのリスト。
- 索引の宣言された型。
- 索引の説明。
multikeyindex3を記述します。DESCRIBE INDEX multikeyindex3 ON stream_acct
+-------------+----------------+-----------+----------+-----------------------------------+--------------+-------------+
table | name | type | multiKey | fields | declaredType | description
+-------------+----------------+-----------+----------+-----------------------------------+--------------+-------------+
stream_acct | multikeyindex3 | SECONDARY | Y | acct_data.country | ANY_ATOMIC | | | |
| | | | acct_data.contentStreamed[].seriesInfo[].episodes[]| ANY_ATOMIC |
+-------------+----------------+-----------+----------+-----------------------------------+--------------+-------------+例2: JSON形式の索引idx_showid_year_monthを記述します。
DESCRIBE AS JSON INDEX idx_showid_year_month ON stream_acct
{
"name" : "idx_showid_year_month",
"type" : "secondary",
"fields" : ["acct_data.contentStreamed[].showId", "substring#acct_data.contentStreamed[].seriesInfo[].episodes[].date@,0,4", "substring#acct_data.contentStreamed[].seriesInfo[].episodes[].date@,5,2"],
"types" : ["INTEGER", "STRING", "STRING"],
"withNoNulls" : false,
"withUniqueKeysPerRow" : false
}
索引の削除
不要になった索引をデータベースから削除できます。
DROP INDEXは、指定された索引をデータベースから削除します。指定した名前の索引が存在しない場合、文は失敗し、エラーがレポートされます。オプションのIF EXISTS句がDROP INDEX文で使用され、同じ名前の索引が存在しない場合、その文は実行されず、エラーはレポートされません。
multikeyindex1を削除します。DROP INDEX multikeyindex1 ON stream_acctAPIの使用による索引の削除:
/* Drop the index acct_episodes*/
private static void dropIndex(NoSQLHandle handle) throws Exception {
String dropIndexDDL = "DROP INDEX acct_episodes ON " + tableName;
TableRequest treq = new TableRequest().setStatement(dropIndexDDL);
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 dropped");
}#drop the index
def drop_index(handle):
statement = '''DROP INDEX acct_episodes ON stream_acct'''
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 dropped')
//drops an index from a table
func dropIndex(client *nosqldb.Client, err error, tableName string)(){
stmt := fmt.Sprintf("DROP INDEX acct_episodes ON %s",tableName)
tableReq := &nosqldb.TableRequest{
Statement: stmt,
}
tableRes, err := client.DoTableRequest(tableReq)
if err != nil {
fmt.Printf("cannot initiate DROP INDEX request: %v\n", err)
return
}
// The drop index request is asynchronous, wait for drop index to complete.
_, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
if err != nil {
fmt.Printf("Error finishing DROP INDEX request: %v\n", err)
return
}
fmt.Println("Dropped index acct_episodes on table ", tableName)
return
}//drops an index
async function dropIndex(handle) {
const dropindDDL = `DROP INDEX acct_episodes ON ${TABLE_NAME}`;
let res = await handle.tableDDL(dropindDDL);
console.log('Index acct_episodes is dropped from table: ' + TABLE_NAME);
}private static async Task dropIndex(NoSQLClient client){
var sql = $@"DROP INDEX acct_episodes on {TableName}";
var tableResult = await client.ExecuteTableDDLAsync(sql);
// Wait for the operation completion
await tableResult.WaitForCompletionAsync();
Console.WriteLine(" Index acct_episodes is dropped from table Table {0}",
tableResult.TableName);
}