2021年09月22日

SQL ヒント句を利用して検索時間を短縮する


通常から重かった検索がついに10分を超え、タイムアウトが発生してしまった。
ヒント句を利用し、改善を図る。


ヒント句はどのような場合に利用するか

通常はOracleが最適な実行計画を選択してくれている。(注1)
ただし、複雑なSQLの場合(7つ以上の表を結合するなど)は最適な実行計画とならないことがある。
そのような場合に実行計画に対するヒントを利用する。

(注1)
複数のテーブルを結合するSQLを実行すると、ORACLEはネステッドループ、ハッシュ、マージの3種類のいずれかの結合方法を選択し実行計画を立てる。


HASH結合とネステッドループ結合を比較した場合のパフォーマンス

一般的に結合する件数が多くなるほどHASH結合のほうが早くなる可能性が高い。
最も大きな要因は「ネステッドループ結合は物理読み込みが多いとパフォーマンス劣化が激しい」ため。


ヒント句の書き方


SELECT /*+ ヒント句 */ 項目,… FROM 〜


・コメントの記号「/*」の直後に「+」を付けるとヒント句となる
・「SELECT」の後に記載する


一見すると普通にコメントの様に見える。実際にヒントの書き方が間違っていたとしても、コメントとして無視されるためエラーにならない。したがって、ヒントを書いたら思い通りの実行計画になったか確認すること。


テーブル名の指定方法

ヒントの対象となるテーブルを指定する場合、FROM句に書かれているテーブル名をそのまま指定する。
よって、別名を定義している場合はその別名を書くこと。※別名を定義していない場合はテーブル名を指定する。


SELECT /*+ USE_HASH(B) */ *
FROM xxxxx A
INNER JOIN yyyyy B
ON A.zzz = B.zzz



今回試した主なヒント

USE_NL
 指定したテーブルをネステッドループ結合にてスキャンさせる
 <使用例>
   /*+ USE_NL(A B) */


USE_HASH
 指定したテーブルをハッシュ結合にてスキャンさせる
 <使用例>
   /*+ USE_HASH(A B) */


FULL
 指定したテーブルに対して、フルスキャンさせる
 <使用例>
   /*+ FULL(A) FULL(B) */
   ※1テーブルずつ分けて書くこと(「FULL(A B)」のようにまとめて書かない)


ORDERED
 FROM句に記述した順番通りに結合する
 <使用例>
   /*+ ORDERED */



結果、10分以上かかっていた検索が13秒前後へ落ち着いた。
まだ改善は見込めそうだが、本事象はタイムアウトが発生し検索が出来ない、そのことでの影響が大きい。
リリースまでのスピードが優先されるため、今回はこれで良しとする。

2019年02月21日

SQL カンマ区切りで出力

テーブルから複数カラムを抽出し、結果をカンマ区切りで出力する。

【SQL】
SELECT
 文字列1 ||','|| 文字列2
FROM
 テーブル名;

【結果】
文字列1,文字列2


「||」 演算子は、任意の文字列を連結するときに使用。

SELECT句のカラムの間をカンマ(,)ではなく、2つのパイプ(||)で連結させ、
更にその間に区切り文字を入れる。「||','||」

※シングルクォートの間にカンマを入力すればカンマ区切りとなり、
タブを入力すればタブ区切りとなる。

2018年06月26日

SQL カラムの削除が出来ない

カラムの削除を行おうとしたところ、下記エラーが表示された。

メッセージ 5074、レベル 16、状態 1、行 1
オブジェクト 'DF_〇〇〇_〇〇〇' は 列 '〇〇〇' に依存しています。
メッセージ 4922、レベル 16、状態 9、行 1
ALTER TABLE DROP COLUMN 〇〇〇 は失敗しました。1 つ以上のオブジェクトがこの列を参照しています。

この場合、制約を削除してから再度カラムの削除を行う。
ALTER TABLE [テーブル名] DROP CONSTRAINT DF_〇〇〇 ;

2018年06月25日

SQL 条件式で分かれているはずの命令文が正しく実行されない場合

BEGIN…END構文を利用することで命令文をブロック化しひとまとめにできる

【EX. IF文で利用する場合】
IF 条件式
 BEGIN
     条件式がTRUEの場合に実行する命令文
 END
ELSE
 BEGIN
     条件式がFALSEの場合に実行する命令文
 END

SQL ロック処理

SELECT * FROM [テーブル名] WITH ([ロック種類],[ロック単位]) WHERE 〜

ロックの基本
データベース上の同一データに対して書込み要求が同時に発生した場合、トランザクションが逐次化されていないと結果に矛盾が生じる。ロックを適切に実装することでこの問題は解決する。

データを保護するロックの仕組み
キャプチャ.JPG


【引数】
キャプチャ2.JPG

SQL DECLAREの利用

DECLAREはローカル変数の宣言と、「カーソル」を宣言する際に使用する

【ローカル変数の宣言】
  DECLARE (変数名)(データ型)

DELCLAREで宣言する変数はローカル変数なので、ストアドプロシージャの実行が終わった段階で
自動的に使用できなくなる



【カーソル利用方法】
  カーソル:SELECT文の実行結果を1行ずつ取り出すことができる

■カーソルの宣言
   DECLARE [カーソル名] CURSOR FOR [カーソルを使用するSELECT文];

■カーソルを開く
   OPEN [カーソル名];

■1行ごとにデータを取り出す指示
   FETCH NEXT FROM [カーソル名] INTO [変数名];

   カーソルからデータを取り出すためには、次のような作業が必要となる

   1.「LOOP」文または「WHILE」文を使って、ループ処理を実行する
   2.ループ処理の中で「FETCH」文を実行し、カーソルから1行取り出す
   3.最後の行を取り出したらループ処理を終了する

   変数名は、SELECT文で取り出される列の数と同じである必要がある。
   ※3つの列を取り出すSELECT文をDECLARE CURSORで宣言した場合は、3つの変数を指定する。

■データの行数分ループ処理を実行する
   WHILE @@FETCH_STATUS = 0

   BEGIN

     (ループ内の処理)

     FETCH NEXT FROM [カーソル名]
     INTO [変数名];
   END;

   ※@@FETCH_STATUS:最後の行を超えたときは-2が設定される

■カーソルを終了
   CLOSE カーソル名;
   DEALLOCATE カーソル名;

2018年06月22日

SQL 例外処理

SQL ステートメントのグループを TRY ブロックで囲み、TRY ブロック内でエラーが発生すると、CATCH ブロックで囲まれた別のステートメントのグループに制御が渡される

【構文】
BEGIN TRY
  (処理ロジック)
END TRY
BEGIN CATCH
  (ERROR 処理ロジック)
END CATCH

2018年06月20日

SQLの実行順番

SQLを書くときに迷う原因の1つに、実行される順番と並び順の違いがあげられる。
SQLが実行される順番は下記。実行順番を意識する。
SQL実行順番.JPG
その買うを、もっとハッピーに。|ハピタス
\ハピタスの紹介キャンペーンです/
最大2000ポイントが貰えます
⇒登録後に行う「ハピタス検定」の答えはこちら

※ハピタスをご存じないかたは他で検索してみてください

ブログ内を検索
ファン
カテゴリ
よく倒されますが、へこたれながら起き上がるSEの備忘録です

Windows(20)
SQL(8)
JAVA(4)
VBScript(13)
VBA(13)
Other(13)
書籍(1)
記事ランキング
  1. 1. no img 確率の収束に必要な試行回数
  2. 2. no img VBScript マウスポインタを自動的に移動させクリックするスクリプト
  3. 3. no img Windows ディスク使用率100%を改善させる
  4. 4. no img Eclipse ホットコード置換に失敗しました
  5. 5. VBScript タスクスケジューラのメッセージを最前面に表示するスクリプト
広告

クラウド型レンタルサーバー サイト運用しない方には不要です‥

ドメイン名取得サービス 超人気ドメイン「.com」「.net」
国内最安値1円です!!

Amazon.co.jpで購入する
画像をクリックするとAmazonのサイトにジャンプします