SQL問合せのチューニングおよび最適化

問合せ最適化は、SQL文を最も効率的に実行する方法を選択する全体的なプロセスです。

SQL問合せを最適化して、正確かつ高速なデータベース結果を取得します。

問合せの最適化での索引の使用

索引付けは、問合せの処理時に必要なディスク・アクセスの数を最小限に抑えて、データベースのパフォーマンスを最適化する方法です。

Oracle NoSQL Databaseでは、問合せプロセッサは、使用可能な索引のうち問合せに有効なものを識別し、その索引を利用するように問合せをリライトできます。索引の使用は、そのエントリの連続した部分範囲をスキャンしたり、その部分範囲内のエントリに対してさらにフィルタリング条件を適用したり、関連する表の行を抽出して返すために存続する索引エントリに格納されている主キーを使用することを意味します。スキャンする索引エントリの部分範囲はWHERE句の条件によって決定され、その一部は索引の検索条件に変換される場合があります。索引エントリの(できれば小さい)サブセットのみが検索条件を満たす場合、個々の表の行にアクセスせずに問合せを評価できるため、大量に発生する可能性があるディスク・アクセスを抑制できます。

Oracle NoSQL Databaseでは、主キー索引がデフォルトで常に作成されます。この索引は、表の主キー列を表の行の物理的な位置にマップします。また、使用可能な他の索引がない場合は、主索引が使用されます。つまり、純粋な表スキャン・メカニズムはありません。表スキャンは主キー索引を使用したスキャンと同等です。索引および問合せについては、問合せプロセッサが次の2つの質問に回答する必要があります。

索引は問合せに適用可能ですか。つまり、この索引を使用して表にアクセスするほうが、(主索引を使用して)全表スキャンを実行するよりも効率的ですか。
適用可能な索引の中で、どの索引または索引の組合せを使用するのが最適ですか。

表の列内の値の数と分布に関する統計はありません。そのため、問合せプロセッサは、適用可能な索引の選択において、単純な経験則に依存するしかありません。また、SQL for Oracle NoSQL Databaseでは、問合せに索引ヒントを含めることができます。索引ヒントを使用すると、問合せで特定の索引の使用を強制できます。

索引を使用する問合せの例

単純な問合せを記述して、索引の使用方法を理解できます。

問合せ1:

2つの値の範囲を満たすチケット番号の乗客の手荷物詳細をフェッチします。

SELECT fullname, ticketNo,bag.bagInfo[].tagNum,
bag.bagInfo[].routing 
FROM BaggageInfo bag WHERE 1762340000000 < ticketNo 
AND ticketNo < 1762352000000

前述の例では、問合せに2つの索引条件が含まれています。ここでは、ticketNoが主キーとして使用されているため、主キー索引を使用します。主キー索引の場合、1762340000000 < ticketNoは開始条件で、ticketNo < 1762352000000は停止条件です。

問合せ計画の一部を次に示します。使用されている主索引を確認できます。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" : 
[
  {
    "equality conditions" : {},
    "range conditions" : { "ticketNo" : { "start value" : 1762340000000, 
       "start inclusive" : false, 
       "end value" : 1762352000000, 
       "end inclusive" : false } }
  }
]

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ2:

チケット番号が2つの値範囲のいずれかを満たす乗客の手荷物詳細をフェッチします。

SELECT fullname, ticketNo,bag.bagInfo[].tagNum,
bag.bagInfo[].routing 
FROM BaggageInfo bag 
WHERE ticketNo > 1762340000000 OR 
ticketNo < 1762352000000

前述の例で、問合せには、WHERE式全体である1つの索引条件が含まれています。ここでは、ticketNoが主キーとして使用されているため、主キー索引を使用します。条件はフィルタリング条件です。

問合せ計画の一部を次に示します。使用されている主索引および索引フィルタリング条件を確認できます。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" : 
[
  {
    "equality conditions" : {},
    "range conditions" : {}
  }
],
"index filtering predicate" :
{
  "iterator kind" : "OR",
  "input iterators" : 
  [
    {
      "iterator kind" : "GREATER_THAN",
      "left operand" :
        {
          ---
        },
      "right operand" :
        {
          ---
        }
    },
    {
      "iterator kind" : "LESS_THAN",
      "left operand" :
        {
          ---
        },
      "right operand" :
        {
          ---
        }
    }
  ]
}

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ3:

特定の予約コードの手荷物詳細をフェッチします。

SELECT fullName,bag.ticketNo, bag.confNo, 
bag.bagInfo[].tagNum,bag.bagInfo[].routing 
FROM BaggageInfo bag WHERE bag.confNo="FH7G1W"

前述の例で、2つの索引は適用可能なcompindex_tckNoconfNoおよびfixedschema_confです。

問合せ計画の一部を次に示します。ticketNoに対する単一の索引であるため、fixedschema_confが使用されます。索引スキャンは等価条件を使用して実行されます。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "fixedschema_conf",
"covering index" : false,
"index scans" : 
[
  {
    "equality conditions" : {"confNo":"FH7G1W"},
    "range conditions" : {}
  }
]

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ4:

すべての男性乗客の名前と経路詳細をフェッチします。

SELECT fullname,bag.bagInfo[].routing FROM BaggageInfo bag 
WHERE gender!="F"

前述の例では、性別に関する情報を持つ索引がないため、索引条件はありません。

問合せ計画の一部を次に示します。使用できる索引がないため、主キー索引のみが使用されます。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" : 
[
  {
    "equality conditions" : {},
    "range conditions" : {}
  }
]

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ5:

すべての乗客の名前と電話番号をフェッチします。

SELECT bag.contactPhone, bag.fullName FROM BaggageInfo bag 
ORDER BY bag.fullName

前述の例では、索引compindex_namephoneのみが適用可能です。ORDER BY式が問合せで使用される索引の最初のフィールドと一致するため、ソート(order by句の場合)は索引ベースとなります。このケースでは、SELECT句に必要な氏名および連絡先の電話番号情報が索引の中にあります。その結果、表にアクセスすることなく、問合せ全体に索引のみから回答できます。そのため、この例では、索引compindex_namephoneがカバー索引になります。問合せプロセッサは、この最適化を適用します。

問合せ計画の一部を次に示します。索引compindex_namephoneが使用されることと、これがカバー索引であることがわかります。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "compindex_namephone",
"covering index" : true,
"index row variable" : "$$bag_idx",
"index scans" : 
[
  {
    "equality conditions" : {},
    "range conditions" : {}
  }
]

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ6:

到着日が指定した値より後である乗客の名前、チケット番号および到着日をフェッチします。

SELECT fullName, bag.ticketNo, bag.bagInfo[].bagArrivalDate 
FROM BaggageInfo bag WHERE EXISTS 
bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]

前述の例で、EXISTS条件は、実際にはフィルタリング条件に変換されます。1つのフィルタリング条件(つまりWHERE式全体)が存在します。

問合せ計画の一部を次に示します。この例では、索引simpleindex_arrivalが使用されています。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "simpleindex_arrival",
"covering index" : false,
"index row variable" : "$$bag_idx",
"index scans" : 
[
  {
    "equality conditions" : {},
    "range conditions" : {}
  }
],
"index filtering predicate" :
{
  "iterator kind" : "GREATER_OR_EQUAL",
  "left operand" :
    {
      ---
    },
  "right operand" :
    {
      ---
    }
}

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ7:

すべての乗客の予約コードと手荷物の数をフェッチします。

SELECT bag.confNo, count(bag.bagInfo) AS TOTAL_BAGS 
FROM BaggageInfo bag GROUP BY bag.confNo

前述の例では、2つの索引fixedschema_confおよびcompindex_tckNoconfNoを適用できます。

問合せ計画の一部を次に示します。1つの列confNoのみを持つ単一の索引であるため、索引fixedschema_confが使用されます。この問合せでは、グループ化は索引ベースです。集計count関数を使用して手荷物数を決定するためにbagInfo全体の詳細が必要であるため、ここでの索引はカバー索引ではありません。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "fixedschema_conf",
"covering index" : false,
"index scans" : 
[
  {
    "equality conditions" : {},
    "range conditions" : {}
  }
]

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ8:

指定された名前リストにある乗客のフルネームとタグ番号をフェッチします。

SELECT bagdet.fullName, bagdet.bagInfo[].tagNum 
FROM BaggageInfo bagdet 
WHERE bagdet.fullName IN 
("Lucinda Beckman", "Adam Phillips",
"Zina Christenson","Fallon Clements")

前述の例では、索引compindex_namephoneのみが適用可能です。

問合せ計画の一部を次に示します。索引compindex_namephoneが使用されます。compindex_namephoneに対して索引スキャンが実行され、4つの等価述語が評価されます。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bagdet",
"index used" : "compindex_namephone",
"covering index" : false,
"index scans" : 
[
  {
    "equality conditions" : {"fullName":"Lucinda Beckman"},
    "range conditions" : {}
  },
  {
    "equality conditions" : {"fullName":"Adam Phillips"},
    "range conditions" : {}
  },
  {
    "equality conditions" : {"fullName":"Zina Christenson"},
    "range conditions" : {}
  },
  {
    "equality conditions" : {"fullName":"Fallon Clements"},
    "range conditions" : {}
  }
]

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ9:

特定のチケット番号および予約コードを持つ乗客のチケット詳細(チケット番号、予約コード、タグ番号およびルーティング)を選択します。

SELECT fullName,bag.ticketNo, bag.confNo, 
bag.bagInfo[].tagNum,bag.bagInfo[].routing 
FROM BaggageInfo bag WHERE
bag.ticketNo=1762311547917 
AND bag.confNo="FH7G1W"

前述の例では、索引compindex_tckNoconfNoを使用できますが、主索引(ticketNo)のみが使用されます。主索引に対して索引スキャンが実行され、WHERE式が評価されます。

問合せ計画の一部を次に示します。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" : 
[
  {
    "equality conditions" : {"ticketNo":1762311547917},
    "range conditions" : {}
  }
]

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。

問合せ10:

乗客手荷物の出発地とすべての乗客の手荷物の数をフェッチし、出発地別にデータをグループ化します。

SELECT $flt_src as SOURCE, count(*) as COUNT 
FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src
GROUP BY $flt_src

前述の例では、fltRouteSrcフィールドに対する索引はありません。したがって、グループ化は一般的な方法で行われます。SELECT文で生成されたレコードを反復処理する内部変数が作成されます。

問合せ計画の一部を次に示します。主索引が使用されています。

"iterator kind" : "TABLE",
"target table" : "BaggageInfo",
"row variable" : "$bag",
"index used" : "primary index",
"covering index" : false,
"index scans" : 
[
  {
    "equality conditions" : {},
    "range conditions" : {}
  }
]

問合せの実行方法の詳細は、「問合せ実行計画」を参照してください。