[DB][SQL] 設計と運用

☆VIEWを利用すべきケース
セキュリティとしての利用
→VIEWで必要な情報だけを取り出す
→参照権限だけを付与して誤った更新を防止する
プログラミングを単純化させるための利用
→複雑SQLをVIEWというオブジェクトにしてプログラムで共通利用する

☆VIEWを使った場合でもINDEXは有効
simpleなviewであればindexは有効利用される

☆実行計画の読み方
実行計画を確認しながらprogrammingすべき

コスト=最適化エンジンが見積もる処理負担=CPUコスト+I/Oコスト=低い程性能が良い
OPTIMIZER_MODE=ALL_ROWS, FIRST_ROWS(n)

検索方法
・TABLE ACCESS FULL(全表スキャン)
対象オブジェクト(表, VIEW)の全行を走査し、1行ずつWHERE条件に合致するか判断する
INDEX RANGE SCAN(インデックス範囲検索)
インデックスを利用して、WHERE条件に合致する複数行を抽出する
INDEX UNIQUE SCAN(インデックス・ユニーク検索)
インデックスを利用して、WHERE条件に合致する一意な行を抽出する

テーブル結合
・NESTED LOOPS(ネステッド・ループ結合)
二つのTBLに親子関係を持たせ、親TBLの先頭行から順に結合列と一致する子TBLの行を検索する
親TBLに有効なINDEX列があって、対象行が少なく、子TBLの結合列に有効なINDEXがある時に効果あり
・HASH JOIN(ハッシュ結合)
二つのTBLをそれぞれスキャンし、その結果で一時的なハッシュ・テーブルに圧縮してメモリに展開する
等価結合("="での結合)で使われる
・MERGE JOIN(マージ結合)
二つのTBLをある順でソートし、一方のTBLから順に他と突き合わせて一致する行を検査する
NEEDS MORE COST THAN HASH JOIN
USED WHEN
-既に2TBLがSORT済みで更なるSORTを必要としない
-結果セットをSORTして取り出す
-結合条件が非等価結合である
・デカルト結合(@Oracle10g)
2つのTBLの結合条件が指定されないSQLに対し各TBLから1つずつSELECTした全ROWの組み合わせを作成する
※使わないTBLをFROM句にしてるとデカルト結合になりうる→性能を落とす

☆NESTED LOOPSとHASH JOINの選択基準
・NESTED LOOPS
親TBLのデータがLITTLEのCASEに有効
・HASH JOIN
大量のデータ同士をJOINするCASEや小規模TBLのJOINに使われる

☆オプティマイザは統計情報を自動的に取得する
Oracle10gではオプティマイザ統計は、「GATHER_STATUS_JOB」というジョブにより自動的に収集される

☆インデックス検索実行の閾値は明確でない
"○○%以下ならINDEX検索"と一概には言えない

☆ガーディナリティの低い列にINDEXを設定しても有効なCASEはある
ガーディナリティ=列の値の種類が行数に比べて多いか少ないかを表す
PK=ガーディナリティは高い列である∵UNIQUEだから
使い方を想定できるなら、INDEXを設定するのが有効な時もある

☆ガーディナリティの低い列にはBIT MAP INDEXが有効
BIT MAP INDEX @Oracle
=INDEX値に一致するかどうかをBITで持つもの
=通常のINDEXよりDISC/CACHE使用量を削減できる
=検索条件が等式か論理演算だけなら性能もBETTER
ガーディナリティの低い列は、ビットマップ・インデックスを使う方が良さそう

☆ガーディナリティが高い列にはBIT MAP INDEXは効果がない
ガーディナリティがある程度大きい場合は、ビットマップ・インデックスを使わない方が良い

☆データ分布に偏りがある場合はBIT MAP INDEXが有効、は嘘
ビットマップ・インデックスの使用判断は偏りよりもガーディナリティにある

☆テーブルの結合列にはINDEXを張るべき
結合列にINDEXを設定すると効果が大きい

☆外部結合と内部結合とで処理速度に差はない
INNER JOIN=両TBLに存在するデータだけ返す
OUTER JOIN=両TBLに存在するデータに加えて片方にしかないデータも返す

☆オプティマイザのコストは信用できる

☆オプティマイザへのヒントは無視されがち
ヒント=人が実行計画を指定するための機能でコメントとしてSQLに埋め込むもの
近頃のOPTIMIZERは賢く人の支持は聞かない

Oracleの主なヒント
/*+ ALL_ROWS */=スループット優先モード
/*+ FIRST_ROWS */=レスポンス優先モード
/*+ FULL {TBL_NAME} */=全表スキャン
/*+ ROWID {TBL_NAME} */=ROWIDを使った検索
/*+ INDEX {TBL_NAME INDEX_NAME} */=使用するINDEXを指定
/*+ ORDERED */=FROM句に指定された順序でTBLを結合
/*+ USE_HASH {TBL_NAME} */=ハッシュ結合を指定
/*+ USE_MARGE {TBL_NAME} */=ソートマージ結合を指定
/*+ USE_NL {TBL_NAME} */=ネステッド・ループ結合を指定

☆INDEXを張ると更新処理の性能に影響し得る
インデックスの更新はコストがかかる

☆「複合INDEXはカーディなりティの低い列を第1キーにした方が検索効率が良い」は必ずしも真でない
複合INDEXのどれを第1キーにすべきかは、単純に速度だけで比較せずに、他の検索条件に対する融通性もよく吟味して決めるべき

Oracle 9i以降では「第1キーを検索上に入れない検索は複合INDEXを使わない」は嘘
第2キーだけを検索条件としても、場合次第でINDEXを有効利用する

☆参照整合性制約が更新処理に与える影響はINDEXより小さい
UPDATE文ではINDEXよりは影響度が小さい

参照整合性制約=参照整合性を設定したTBL間で守る規則

親TBLにないRECORDは子TBLにINSERTできない

Oracleの「索引構成表」が有効なのは単純ソートだけ
Oracleでは索引構成表なるINDEXがある
ソートがあるからといってその索引構成表がいつも有効とは限らない

☆RDBMS内の過程
SQL文を受け取る
②パーサーが妥当性を観る(SQL文法、TBL/COL名、権限等)
QUERY TRANSFORMERが性能向上のためSQLを書き換える
④ESTIMATERがDICTIONARY/CATALOGUEを参照してプランを見積もる
PLAN GENERATORが実行計画を決定する
④と⑤を繰り返して実行計画が決定される

XXX p.60-

ref:
日経ソフトウェア DB開発の実践力, 200608

tag : SQL PLAN GENERATOR CATALOGUE QUERY Oracle INNER JOIN OUTER INDEX

2014-06-18 23:17 : 開発 : コメント : 0 : トラックバック : 0 :
コメントの投稿
非公開コメント

« next  ホーム  prev »

search

ad



counter


tag cloud

category cloud