SQLコマンドの使用

単一のフィールド索引の作成:

例: 乗客予約コードに対する索引を作成します。

CREATE INDEX fixedschema_conf ON baggageInfo(confNo)

これは単一列の固定スキーマ索引の例です。索引は、baggageInfo表のstringデータ型を持つconfNoフィールドに対して作成されます。

コンポジット索引の作成:

例: 乗客のフルネームと電話番号に対する索引を作成します。

CREATE INDEX compindex_namephone ON baggageInfo(fullName,contactPhone)

これはコンポジット索引の例です。索引は、baggageInfoスキーマの2つのフィールド(フルネームと連絡先電話番号)に対して作成されます。

ノート:

この索引の1つ以上のフィールドを固定スキーマ列として指定できます。

JSON索引の作成:

索引は、少なくとも1つのフィールドがJSONデータ内にある場合、JSON索引と呼ばれます。JSONはスキーマレスであるため、索引付けされたJSONフィールドのデータ型は行間で異なることがあります。JSONフィールドに索引を作成するときに、JSONフィールドに必要なデータ型が不明な場合は、anyAtomicデータ型を使用できます。または、Oracle NoSQL Databaseアトミック・データ型のいずれかを指定できます。これを行うには、JSONフィールドへのすべての索引パスの横でASキーワードを使用してデータ型を宣言します。

例1: 乗客の手荷物のタグ番号に対する索引を作成します。

CREATE INDEX jsonindex_tagnum ON baggageInfo(bagInfo[].tagnum as INTEGER)

これはJSON索引の例です。索引は、baggageInfo表のbaginfo JSONフィールドに存在するtagnumフィールドに対して作成されます。索引の作成時にtagnumフィールドのデータ型を指定します。

宣言されたデータ型に違反するデータを持つ行が関連表に含まれている場合、JSON索引の作成は失敗します。同様に、JSON索引の作成後、新しい行がJSON索引で宣言されたデータ型に準拠していない場合、挿入/更新操作は失敗します。

例2: 乗客のルートに対する索引を作成します。

CREATE INDEX jsonindex_routing ON baggageInfo(bagInfo[].routing as ANYATOMIC)

anyAtomicとしてのJSON索引パスの宣言には、索引付けされたJSONフィールドに様々なデータ型の値を格納できるという利点があります。索引エントリは昇順でソートされます。これらの値が索引に格納されると、次のようにソートされます。

数値
文字列
ブール

ただし、この利点は領域およびCPUコストによって相殺されます。これは、索引付きフィールドの任意の種類の数値が、索引に格納される前に数値にキャストされるためです。このキャストにはCPU時間がかかり、結果として得られる数値の記憶域は、元の数値の記憶域より大きくなります。

JSONコレクション表に対する索引の作成

JSONコレクション表のフィールドの索引付けは、JSON索引の作成に似ています。型定義には名前(パス式とともに)とANYATOMICを指定する必要があります。または、強い型指定の索引の場合は、索引付けするフィールドのJSONタイプを指定できます。

ドキュメントの最上位JSONフィールドに索引付けする場合、フィールド名はパス式です。要素がJSONオブジェクトに深くネストされている場合は、完全なパス名を指定します。いずれの場合も、すべての索引のデータ型を指定する必要があります。柔軟性を高めるために、JSONコレクション表でANYATOMICを使用することをお薦めします。

例: ショッピング・アプリケーション用に作成されたJSONコレクション表にコンポジット索引を作成します。

CREATE INDEX idx_ntfy_cty on storeAcct (address.city as ANYATOMIC, notify as ANYATOMIC)

前述のコンポジット索引では、notifyフィールドはstoreAcct表の最上位フィールドであり、パスとしてフィールド名を指定することで索引付けできます。cityフィールドはaddressフィールドにネストされており、パス式を使用して索引付けする必要があります。

ノート:

ネストされたJSONフィールドに索引を作成する場合、そのフィールドは表のすべての行に存在する必要があります。それ以外の場合は、エラーが表示されます。

単純索引の作成:

索引は、表のデータ行ごとに1つのエントリが索引に作成される場合、単純索引と呼ばれます。索引は、アトミック・データ型の単一の値または任意の特別な値(SQL NULL、JSON NULL、EMPTY)を返します。基本的に、単純索引の索引パスは、配列、マップまたはネストされたデータ型を返すことはできません。

例: 手荷物が最後に確認された日時、最後に確認されたステーションおよび到着日時という3つのフィールドに対する索引を作成します。

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つ存在あります。複数キー索引では、表の行ごとに、索引付けされる配列のすべての要素に対して索引エントリが作成されます。

例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: コンポジット複数キー索引:

索引は、索引が複数のフィールドに作成され、それらのフィールドの少なくとも1つが複数キーである場合、コンポジット複数キー索引と呼ばれます。コンポジット複数キー索引には、複数キー索引パスと単純索引パスの組合せを使用できます。

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句を使用した索引の作成

オプションのWITH NO NULLS句を使用して索引を作成できます。その場合、索引付きフィールドのNULLまたはEMPTY (あるいはその両方)の値を持つ行は索引付けされません。

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句の結果が少なくなります。

関数の索引の作成例:

例1: 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コマンドを使用して問合せ計画を表示します。

例2: 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))

この索引を使用する問合せの例を次に示します。この問合せでは、2022年のshow 16のエピソードを視聴したユーザーの数がカウントされます。

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"
    }
  ]
}