ITコーディネータのシュウです。

先月、会社の同僚と海釣りに行ってきました。
横浜の金沢八景の近くの海で、釣り船を予約して楽しんできました。その日はとてもよい天気で、アジ、サバ、イシモチなどが結構たくさん釣れましたね。私は最初なかなか釣れなくて、悪戦苦闘していたのですが、隣にいた釣り好きの先輩においては、竿を投げればすぐに引きが来る感じで、本当にびっくりです。同じようにやっているつもりなのに、どこかが違うんですね。やはり釣りは奥が深い！それでも、後半は多少釣れたこともあり、楽しむことができました。
上の写真は、船から八景島シーパラダイスのジェットコースターを撮ったものです。
釣れた魚は、その日のうちにさばいて刺身にして食べました。アジがぷりぷりしてとっても美味しかった。魚好きの妻も、美味しいと喜んでくれたので、よかった。＼(＾▽＾)／

さて、今年も残りわずかとなりました。風邪など引かないで、よい年を迎えられますことをお祈りいたします。来年も、よろしくお願いいたします。

＜本日の題材＞
計算列（SQL Server）

SQL Serverで、計算列というものがあるということなので、試してみたいと思います。

例）
以前、身長と体重からBMI（肥満指数）をストアド・ファンクションを使って求めたことがありましたが、今回は計算列でこれを行ってみたいと思います。

CREATE TABLE dbo.syain_health(
id      INT
,height  DECIMAL(5,1)
,weight  DECIMAL(5,1)
,bmi     AS (
      weight /(height/100 * height/100))
,CONSTRAINT PK_syain_health PRIMARY KEY (id));

テーブル作成後に、テーブルの定義を、SQL Server Management Studio で確認すると、

最後の「bmi」項目が計算列になります。
それでは、データを登録してみます。このとき、計算列にはInsertしません。

INSERT INTO dbo.syain_health(id, height, weight) VALUES(1, 165.1, 61.2);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(2, 174.4, 65.6);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(3, 182.3, 71.3);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(4, 168.8, 63.1);
INSERT INTO dbo.syain_health(id, height, weight) VALUES(5, 172.0, 84.9);

計算列にデータを登録しようとすると、下記のようにエラーが出ます。

INSERT INTO dbo.syain_health(id, height, weight, bmi) VALUES(6, 176.0, 73.5, 25.3);

 それでは、データをSELECTしてみます。

SELECT * FROM dbo.syain_health
ORDER BY id;

計算列で指定した「bmi」列は、自動的に計算されて抽出されていることが確認できます。

また、計算列にはインデックスを作成することもできます。
データを100万件ほど作成して、インデックスの有無によるレスポンスを比較してみます。

身長は、140センチ以上200センチ以内、体重は40キロ以上100キロ以内でランダムに作成してみます。このとき、RAND関数を利用してみます。RAND関数は、0～1までの範囲の乱数をfloat型で取得するものです。

DECLARE
  @v_count INT = 5;     -- id は既に1～5は作成済なので初期値を5

WHILE @v_count < 1000000
BEGIN
    SET @v_count = @v_count + 1;
 
   INSERT INTO dbo.syain_health(id, height, weight)VALUES
    (@v_count, ROUND(140+RAND() * 600/10,1), ROUND(40+RAND() * 600/10,1));
END;

再度データを確認してみます。

SELECT * FROM dbo.syain_health
ORDER BY id;

 件数を確認すると、

SELECT COUNT(*) FROM dbo.syain_health;

それでは、bmi の値が 20 ～ 25 の人の件数を確認します。
時間を計測するため、以下のコマンドを実行します。

SET STATISTICS TIME ON

SELECT COUNT(*) FROM dbo.syain_health
WHERE bmi BETWEEN 20 AND 25;

メッセージタブで表示されている時間は、
SQL Server 実行時間:
、CPU 時間 = 671 ミリ秒、経過時間 = 673 ミリ秒。

次に、インデックスを作成してみます。

CREATE INDEX IX_syain_bmi ON dbo.syain(bmi);

一度、データバッファキャッシュをクリアします。
DBCC DROPCLEANBUFFERS

再度、先ほどの処理を実行してみます。
SELECT COUNT(*) FROM dbo.syain_health
WHERE bmi BETWEEN 20 AND 25;

このときの時間を確認すると、
SQL Server 実行時間:
、CPU 時間 = 16 ミリ秒、経過時間 = 26 ミリ秒。

インデックスを作成することで、処理は速くなっていることが確認できました。

また、計算列は物理的にデータを保存することもできるということで、その場合には、テーブル作成時に、計算列の後ろにPRESISTED を付けます。

CREATE TABLE dbo.syain_health2(
id      INT
,height  DECIMAL(5,1)
,weight  DECIMAL(5,1)
,bmi     AS (
      weight /(height/100 * height/100)) PERSISTED
,CONSTRAINT PK_syain_health2 PRIMARY KEY (id));

インデックスも追加します。
CREATE INDEX IX_syain_health_bmi2 ON dbo.syain_health2(bmi);

その後、同様にデータを100万件作成して、先ほどと同様のことを行ってみます。

SQL Server 実行時間:
、CPU 時間 = 16 ミリ秒、経過時間 = 17 ミリ秒。

物理的にデータが保存されている場合とそうでない場合での処理時間の違いについては、それほど違いは無いようですが、多少速い感じでしょうか。

今日は以上まで

ITコーディネータのシュウです。

先日、妻の実家のある富山のほうに行く機会があり、そこできれいな夕焼けが見れたので、撮った写真です。車に乗っていた娘が、きれいだよ！というので、慌てて車の中から撮ったものです。
そのときも、本当に昼間は暑い日が続いていて、これは大変だと思っていましたが、その後もさらに暑さが増して、今年は本当に危険な暑さになってますよね。埼玉の熊谷では、41.1℃という、国内最高気温を更新するという状況になりました。クーラーが効いていないところでは、本当に大変かと思います。
皆さん、本当にお体には注意して頂きたいと思います。

＜本日の題材＞
インデックス再構築・再構成（SQL Server）

前回、SQL Serverの動的管理ビューを使用することで、パフォーマンスに影響があるクエリーの確認や不足しているインデックスなどを確認できることをブログで取り上げましたが、今回もその続きということで、取り上げてみたいと思います。
データベースはインデックスをうまく設定することで、抽出処理のレスポンスを効果的に向上させることができますが、当初それほど遅くなかった抽出処理が、いつの間にかとても遅くなっていることに気づくことがあります。その原因の一つに、インデックスの断片化というものがあります。

以前、大手企業の分析用のシステムを見ていたときがありました。日々基幹システム側から日次のデータが分析用のデータベースに送られてくるので、削除・登録などの処理を夜間に行って、分析用データベースを本番環境の前日終わり時点のデータにしていました。毎日、大量のトランザクションデータがあるため、夜間に送られてくるデータもかなりのボリュームがあり、夜間バッチ処理も結構な時間がかかっていました。
そして、分析用に作成したクエリーやプロシージャなどの処理が、作成した当初はそれほど遅くなかったのに、とても遅いと感じるようになることがありました。
原因を調べてみると、インデックスの断片化がかなりの割合で発生しており、ページ密度が大きく低下し、読み取りのページ数もその分多くなって、効率が悪くなっていたことが大きな要因でした。
当時は、SQL Server 2000を使っていたので、DBCC SHOWCONTIG コマンドで断片化を確認し、DBCC DBREINDEXコマンドで再構築を行ったり、 DBCC INDEXDEFRAGコマンドで再構成を行ったりということを、不定期に行っていました。

今は、SQL Server のバージョンも上がり、動的管理ビューを使用することで、インデックスの断片化も確認できます。以下がそのSQLの例になります。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.page_count,s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), NULL, NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id
WHERE s.avg_fragmentation_in_percent > 10
ORDER BY avg_fragmentation_in_percent DESC

ここでの、主な項目の意味としては、
page_count                                                 ページ数
fragment_count                                       断片化しているページ数
avg_fragmentation_in_percent      断片化の割合(%)

上記の結果では、断片化の割合が10%以上のものを抽出していますが、結構高い割合のものがあることが確認できます。

また、抽出SQLのdm_db_index_physical_stats 関数の5番目の引数であるスキャンモードには、LIMITED, SAMPLED, DETAILEDの3種類があります。（上記の例は、LIMITED モード）
それぞれのスキャンモードの意味は以下になります。

LIMITED           
        最も高速なモードで、スキャンするページ数は最小

SAMPLED         
        SAMPLED モードでは、インデックスまたはヒープの全ページの 1% のサンプルに基づく統計情報が返されます。インデックスまたはヒープのページが 10,000 ページに満たない場合は、SAMPLED モードの代わりに DETAILED モードが使用されます。
 
DETAILED       
        DETAILED モードではすべてのページがスキャンされ、すべての統計が返されます。

それで、断片化を解消するには、以下の3つの方法があります。1)     インデックスのオフライン再構築
2)     インデックスのオンライン再構築（Enterprise エディション のみで利用可能）
3)     インデックスの再構成

1)     インデックスのオフラインでの再構築のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REBUILD

インデックスの再構築（オフライン）は、新しい領域へインデックスを再作成し、古いインデックスを削除することで、断片化を解消します。したがって、インデックスの再構築中は、インデックス全体がロックされるので、再構築が完了するまでは、ユーザー操作は待ち状態になり、注意が必要です。

例)
ALTER INDEX PK_T_USER_LOG ON dbo.T_USER_LOG REBUILD

再構築の後で、再度このテーブルについての断片化を見てみます。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.page_count, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), OBJECT_ID(N'T_USER_LOG'), NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
  and i.index_id = s.index_id;

上記の結果から、断片化の割合は 0% になり、断片化が解消されたことがわかります。

2)     インデックスのオンライン再構築のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REBUILD WITH ONLINE = ON

3)     インデックスの再構成のコマンドは以下になります。

ALTER INDEX インデックス名 ON テーブル REORGANIZE

例)
先ほどの、断片化の一覧で、断片化が35%程度であった、「T_USER_LOG_V6」について、再構成を行ってみます。最初に、現状の断片化を再度確認してみます。

SELECT DB_NAME(s.database_id), OBJECT_NAME(s.object_id), s.index_type_desc, i.name, s.index_id, s.alloc_unit_type_desc, s.page_count, s.fragment_count, s.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('dbsdb'), OBJECT_ID(N'T_USER_LOG_V6'), NULL, NULL, 'LIMITED') s
INNER JOIN sys.indexes i ON i.object_id = s.object_id
and i.index_id = s.index_id;

再構成を実施してみます。

ALTER INDEX PK_T_USER_LOG_V6 ON dbo.T_USER_LOG_V6 REORGANIZE;

件数が多少多かったこともあり、少し時間がかかった感じがありました。

断片化を再度確認してみます。先ほどと同じSQLを実行してみます。

先ほど35%程度あったところは、2.4% ほどに変わりました。断片化がかなり解消されたことがわかります。

★インデックスの再構築と再構成の違いについて簡単に記します。
〇再構築
・ひとつのトランザクションでインデックスを完全に再作成。
　そのため、キャンセルすればそのトランザクションはロールバ
　ックされ、断片化した状態に戻る。
・インデックスを作り直すので、テーブルのレコード数が増えな
　い限り、大きく処理時間が変わることはない。
・一時的には、断片化した古いインデックスと再作成した新しい
　インデックスがふたつ存在する状況となり、それらを格納する
　ための容量、及びソート用の領域が必要になる。

〇再構成
・処理単位ごと (処理単位はページ) にトランザクションが分割さ
　れていて、リーフページ間でインデックス行を移動させること
　で行を前に詰めて断片化を解消している。
　キャンセルしても、既に完了しているトランザクションの結果
　は維持される。
・前のページに空きがある場合に後ろのページから前のページへ
　行を移動するという処理であるため、断片化の度合が大きいほ
　ど処理時間が長くなる。
・追加の領域は基本的には必要としない。

実際の運用では、上記のような違いを考慮してどちらを選択するかを決定する必要があると思います。

今日は以上まで

ITコーディネータのシュウです。

いよいよ梅雨の季節が近づいてきました。上の写真は、少し前に娘がスマホで撮って送ってくれたきれいな夕方の空です。少し神秘的ですね。梅雨になるとこういう空もなかなか見れなくなっちゃうな。
さて、最近のスポーツでは、サッカーのワールドカップがいよいよ始まります。2か月前に監督の交代劇があり、このところの強化試合も負け続けているので、難しいと思っちゃいますが、どうか奇跡よ起きて～！　2002年の日韓共催のワールドカップのベスト16を超えてほしい！
また、卓球では先日の荻村杯・ジャパンオープンで、男子は張本智和選手、女子では伊藤美誠選手が優勝しました。接戦を最後まで粘って勝ち切った精神力は本当にすごい、感動します。日本選手が頑張っていると、元気が湧いてきます。よーし、仕事も頑張るぞー！

＜本日の題材＞
動的管理ビュー（SQL Server）

SQL Serverを使用していて、パフォーマンスがいまいちよくない場合など、最もCPUを多く使っているクエリーは何か？、最も多くのI/Oを発生させているクエリは何か？、不足しているインデックスはないか？などを確認したくなることがあると思います。そんなときに、知っていると便利な動的管理ビューについて、今回は取り上げてみたいと思います。

SQL Serverというと、パフォーマンスモニターや利用状況モニターなど、GUIのツールでいろいろと調べるというイメージが強く、逆にOracleは、V$表といわれる動的パフォーマンスビューなどを駆使していろいろと調べるというイメージが強いですね。（最近はOracleもEnterprise ManagerなどでGUIでも見れるかたちにしてきていますが）
ただ、SQL Serverでも、動的管理ビューというものから、いろいろと上記のような内容を調査することができます。
ちなみに、動的管理ビュー（DMV：Dynamic Management View）は、SQL Server2005で追加された機能で、パフォーマンス・チューニングを手掛けるエンジニアにとっては画期的といえるものでした。それまで、ほとんどドキュメント化されず、その状態を参照する方法も知らされていなかったSQL Serverの内部が、広く可視化されたからです。
それでは、以下に例を挙げてみましょう。

●最もCPUを多く使っているクエリーの上位10位までを確認する

SELECT TOP 10
  total_worker_time/execution_count AS avg_cpu_cost,
  execution_count,
  SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset 
         WHEN -1 THEN DATALENGTH(st.text) 
        ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text,
  query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY avg_cpu_cost DESC;

上記は、「sys.dm_exec_query_stats」という動的管理ビューから抽出できる情報で、「total_worker_time」（プランの実行で使用されたcpu時間の合計）を「execution_count」（プランが実行された回数）で割った値を平均のcpuコストとして、それの多い順に10件を表示しています。ただし、実行頻度（execution_count）が少なければ、それほど重要ではないと思われます。
（※注意点としては、現在キャッシュされているクエリに対する集計情報のみを示していること。メモリが多くなければ、過去に行ったコストの高いクエリがキャッシュから削除されている可能性もあるので、定期的にこの抽出を行えば、コストの高いクエリを特定できる可能性が高くなると思われます。）

なお、上記のSQLでは、前回取り上げた「CROSS APPLY」を使用しています。また、「querry_plan」の列を最後に表示していますが、これをクリックすると、以下のように「querry_text」で表示されたSQLの実行時の実行プランが表示できます。（ただし、一緒に表示するとその分抽出処理が重たくなると思われるので、後から plan_handleを元に実行プランを抽出することも可能です。）

参考：https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql?view=sql-server-2017

●最も多くのI/Oを発生させているクエリの上位10位までを確認する

SELECT TOP 10
  (qs.total_logical_reads/qs.execution_count) AS avg_logical_reads,
  (qs.total_logical_writes/qs.execution_count) AS avg_logical_writes,
  (qs.total_physical_reads/qs.execution_count) AS avg_phys_reads,
  execution_count,
  SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset 
         WHEN -1 THEN DATALENGTH(st.text) 
        ELSE qs.statement_end_offset 
        END - qs.statement_start_offset)/2) + 1) AS query_text,
  query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY (total_logical_reads + total_logical_writes) DESC;

 こちらは、「total_logical_reads」（論理読み取りの合計数）と「total_logical_writes」（論理書き込みの合計数）の合計の多いものから順に上位10件を表示しています。

●不足していると思われるインデックスの一覧

SELECT gs.avg_user_impact AS [予測されるクエリパフォーマンス改善率],
       gs.avg_total_user_cost AS [削減できたクエリの平均コスト],
       gs.last_user_seek AS [最後にシークした時間],
       id.statement AS [テーブル名] ,
       id.equality_columns AS [等値述語に使用できる列],
       id.inequality_columns AS [不等値述語に使用できる列] ,
       id.included_columns AS [包括列として必要な列],
       gs.unique_compiles AS [コンパイルおよび再コンパイルの数],
       gs.user_seeks AS [クエリによって発生したシーク数]
FROM  sys.dm_db_missing_index_group_stats AS gs
       INNER JOIN sys.dm_db_missing_index_groups AS ig
                                          ON gs.group_handle = ig.index_group_handle
       INNER JOIN sys.dm_db_missing_index_details AS id
                                          ON ig.index_handle = id.index_handle
-- WHERE id.[database_id] =DB_ID(‘db名’)
   -- コメントをはずすと指定したデータベースでの抽出となる
Order By gs.avg_user_impact DESC;

 上記は、「sys.dm_db_missing_index_group_stats」「sys.dm_db_missing_index_groups」「sys.dm_db_missing_index_details」という動的管理ビューから抽出できる情報で、インデックスが存在していたなら改善されるであろうパフォーマンス改善率などを表示するものになっています。
上記の動的管理ビューの結果からインデックスを追加作成するとした場合には、[equality_columns（等値述語に使用できる列）]を最初に指定し、その次に、[inequality_columns（不等値述語に使用できる列）]を指定し、INCLUDE句に[included_columns（包括列として必要な列）]を指定するかたちになります。

※参考：https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2008-r2/ms345405(v=sql.105)

例えば、1番目の結果からインデックスを追加するとしたら、

CREATE NONCLUSTERED INDEX [KEY1_製品区分別積上] ON [dbo].[製品区分別積上]
(
              [年度], [月], [バージョン], [部門コード], [製品区分], [製品項目]
)
INCLUDE (
[金額], [粗利率], [粗利額]
);

レスポンス改善を行いたいときに、結構有効かも知れませんね。

今日は以上まで

ITコーディネータのシュウです。

ゴールデンウイークも終わり、ときに夏を思わせるような暑い日もある5月も後半になりました。
上の写真は、ゴールデンウイークに家族で群馬県館林市にあるつつじが岡公園のつつじまつりに行ってきたときのものです。
樹齢800年を超えるヤマツツジの巨樹群が自然形のままで保存され、歴史的価値も高いとのことです。時期的には少し遅かったので、見頃の時期は過ぎてしまっていましたが、それでもまだ咲いているつつじは結構あって、たくさんのお客様で賑わっていました。海外の人も結構いましたね！
天気がよくて気持ちよかったです。ちょっとでも自然に触れるのはいいものですね。
なかなか家族サービスができない現状だけど、少しは喜んでくれたかな(^^!

＜本日の題材＞
バインド変数（Oracle）

SQLでパフォーマンスを改善させる方法、及びセキュリティの観点からSQLインジェクションを防ぐ方法ということで、バインド変数を使うということがよく話題に上がります。今回は、Oracle環境でのバインド変数について取り上げてみたいと思います。
バインド変数とは、SQL文に埋め込む変数のことで、実際の値を後から設定できるようにSQL文の一部を変数にしたもので、これを利用することで、SQL文の記述を統一する（データベースからみて同一のものとみなせる）ことができます。

OracleがSQLを実行するステップは、大きくは以下のようになっています。
・解析処理（PARSE）
・実行処理（EXECUTE）
・データの取り出し処理（FETCH：SELECT文のときのみ）

解析処理には、構文チェック、表・列の定義チェック、権限チェック、実行計画の生成などが含まれ、一度解析したSQLは実行計画とともにSGAの共有プールのライブラリキャッシュにキャッシュされます。
SQLが発行されると、Oracleはライブラリキャッシュに同一のSQLの解析結果がキャッシュされていないかを確認し、キャッシュされていればそれ以降の解析処理をスキップして、キャッシュ上の実行計画を使ってSQLを実行します。（このことをSOFT PARSEと呼びます）
同一の解析結果が存在しない場合、上記の解析処理を実行しますが、このHARD PARSEが比較的重たい処理のために、これを省くことができればスループットの向上に繋がります。

そして、この共有プールにキャッシュされた情報は、SQLが完全に一致するときのみ再利用されるため、条件値が異なったり、SQL文の大文字・小文字や改行位置、スペースの個数など一部でも異なると違うSQLとして新しく解析されてしまいます。こういう仕組みのため、バインド変数を使うことで同一のSQLとみなせれば、HARD PARSEを回避できるというメリットがあります。
（ちなみに、SQLのソースコードの中に、直接記述した数値や文字列のことをリテラルと呼びます）

具体的に試してみます。
以前、バルク処理というところで作成した顧客テーブル（20万件）を使って、SELECT文の処理速度をリテラルの場合とバインド変数を使った場合で比較してみます。

テーブル定義は以下：

CREATE TABLE customer(
 c_id number(8)
,c_name varchar2(20)
,Constraint PK_customere Primary key(c_id));

テストデータを作成します。

BEGIN
 FOR i IN 1..200000 LOOP
   INSERT INTO customer(c_id, c_name) VALUES(i, '顧客名_'||LPAD(TO_CHAR(i),8,'0'));
 END LOOP;
END;
/

このデータを、バインド変数を使わずに、主キーである c_id 列の値で SELECTしてみます。以前、実行の度にSQLが変わる場合について、動的SQLのEXECUTE IMMEDIATE文を使用する方法について取り上げたことがありましたが、今回もそれを使ってみます。

時間を計測したいので、時間の計測を可能にします。
SET TIMING ON

BEGIN
   FOR I IN 1..200000 LOOP
    EXECUTE IMMEDIATE 'SELECT * FROM customer WHERE c_id = ' || TO_CHAR(I);
   END LOOP;
END ;
/

3分2秒かかりました。

それでは、次は c_id = の条件のところを、バインド変数を使って行ってみます。
処理時間を厳密に確認するために、一旦、バッファキャッシュ、共有プールの情報をクリアします。

ALTER SYSTEM FLUSH  BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

先ほどのSELECTの処理を、バインド変数を使って実行します。
 
BEGIN
    FOR I IN 1..200000 LOOP
        EXECUTE IMMEDIATE 'SELECT * FROM customer WHERE c_id =:C1' USING I;
             -- :C1 のところがバインド変数 、USING I で変数 I を使用
    END LOOP;
END ;
/

5秒あまりで処理が終了しました。全然違いますね。

実際に、for loop 処理で生成したSELECT文について、共有SQL領域についての統計情報を示すV$SQLを抽出して確認してみます。 

select count(*) from V$SQL where SQL_TEXT like 'SELECT * FROM customer WHERE c_id%';

結果は1件と出ましたので、20万回のSELECT文がすべて同じ1回のSQL文を共有したということがわかります。実行計画を生成する HARD PARSEは1回のみであり、残りはすべて共有プール上のその実行計画を使って行ったということですね。

今回は、バインド変数を使うことで処理が非常に高速化しましたが、そうならないケースもあるようです。それについては、今回は特には記述はしませんが、また機会があれば確認してみたいと思います。

今日は以上まで

ITコーディネータのシュウです。

4月も終盤を迎え、もうすぐゴールデンウイークですね。
先日、埼玉県の埼玉県比企郡滑川町にある、国営武蔵丘陵森林公園に行ってきました。長年埼玉にいながら、実は行ったことがなかったのですが、東京ドーム65個分の広さがあり、雑木林にはたくさんの木があって、ストレス解消、森林浴にはもってこいの場所だと思いました。上の写真はハーブガーデンというハーブがいろいろと植えられているところの写真です。
下の写真は、ネモフィラという花が咲いている花畑です。

日本には17個くらいの国営の公園があるのですが、ここが一番最初に整備されたところだそうです。
季節によっていろいろと見れる花もあり、サイクリングコースや子供が遊べるキッズコーナー、日本一大きなエアートランポリンなど、いろいろと楽しめそうです。ゆっくり歩いたので、一部しか回れませんでしたが、今度また家族で行こうかと思いました。
埼玉にも、探せばいろんないいところがありますね。今まで忙しさにかまけて行かなかったのが本当に残念！
子供が小さいときに、もっと自然と触れ合う機会を持つようにすればよかったね、と妻と話をすることが増えた今日この頃です。

＜本日の題材＞
OVER句（Oracle）

前回、SQL Serverに関して、OVER句のROWSというものを題材に取り上げてみましたが、Oracleでも同様のことはできるのか？ということを調べてみたところ、以前からできるようなので、今回はOracleで確認してみたいと思います。

OracleでもOVER句と一緒に以下のような句が使用できます。

前後の行の値を取得する
LAG
--- 前の行
LEAD
--- 後の行

前回の例でも見た移動平均や移動累計の取得
ROWS n PRECEDING
--- 現在の行からn行前を含めて対象とする

最初の行、最後の行、n番目の行
FIRST_VALUE
--- パーティションの最初の行
LAST_VALUE
--- パーティションの最後の行
NTH_VALUE
--- パーティションのN番目の行

※上記で、IGNORE NULLS という句を付けると、NULL以外で最初、最後などの指定ができる

OVER句やPARTITION BY句を絡めた書き方としては、

LAG(項目名)
OVER (PARTITION BY 項目名1
     ORDER BY 項目名2
     )

項目名1ごとに、項目名2の並び順で、項目名の前の行の値(LAG)、項目名の後の行の値(LEAD)を抽出します。

LAG(項目名)のところは、以下のような設定が可能
LEAD(項目名)
FIRST_VALUE(項目名)
LAST_VALUE(項目名)

※LAG、LEADの代わりにSUM(項目名)、MAX(項目名)なども使えます。

例として、前回SQL Serverで行ったオリンピックのメダル数の抽出の例をOracleでも試してみます。

テーブルは以下の定義：
CREATE TABLE olympic_medal(
 season VARCHAR2(4)
,year   DECIMAL(4)
,color  VARCHAR(2)
,counts     INT
,CONSTRAINT pk_olympic_medal PRIMARY KEY (season, year, count));

このテーブルに、夏季、冬季の日本が取ったメダル数を登録してみます。（前回と同様にデータをInsertします。途中省略）

INSERT INTO olympic_medal VALUES('夏季',1912,'金',0);
INSERT INTO olympic_medal VALUES('夏季',1912,'銀',0);
INSERT INTO olympic_medal VALUES('夏季',1912,'銅',0);
…
INSERT INTO olympic_medal VALUES('冬季',2018,'金',4);
INSERT INTO olympic_medal VALUES('冬季',2018,'銀',5);
INSERT INTO olympic_medal VALUES('冬季',2018,'銅',4);

夏季、冬季それぞれの、各年毎、メダル色毎のメダル数（前回、次回も含めて）と、直近3回のメダル色毎の合計数、平均数を抽出し、年の新しい順に抽出してみます。

--まず出力結果のフォーマットを設定します。
col 季節 format a4
col 年 format 9999
col 色 format a4
col 今回 format 9999
col 前回 format 9999
col 次回 format 9999
col 直近3回合計 format 9999
col 直近3回平均 format 9999

--以下がSQL
SELECT
 season 季節
,year 年
,color 色
,counts 今回
,LAG(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ) AS 前回
,LEAD(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ) AS 次回
,SUM(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   　　-- 現在の行から2行前から現在行までの3行分
     ) AS 直近3回合計
,AVG(counts)
   OVER (PARTITION BY season, color
     ORDER BY year
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
　　 -- 現在の行から2行前から現在行までの3行分
     ) AS 直近3回平均
FROM olympic_medal
ORDER BY season, year DESC, color;

次に、金、銀、銅で色を分けずに、季節、年毎のメダル数の合計について、最初からの累計の合計個数と平均個数を抽出し、冬季、夏季の年の新しい順に表示してみます。

col 季節 format a4
col 年 format 9999
col 色 format a4
col 今回 format 9999
col 累計の合計 format 9999
col 累計の平均 format 9999

WITH C_YEAR_MEDAL(season, year, counts) AS
(SELECT season, year, SUM(counts)
   FROM olympic_medal
GROUP BY season, year
)
SELECT
season
,year
,counts
,SUM(counts)
   OVER (PARTITION BY season
     ORDER BY year
     ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW　　   -- 最初の行から現在行まで
     ) AS 累計の合計counts
,AVG(counts)
   OVER (PARTITION BY season
     ORDER BY year
     ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW   　　-- 最初の行から現在行まで
     ) AS 累計の平均counts
FROM C_YEAR_MEDAL
ORDER BY season DESC, year DESC;

上記の結果から、SQL Serverと同様に、OVER句を使用することで、各行に対しての直近の数回や累計の合計などを抽出することができることが確認できました。

今日は以上まで

ITコーディネータのシュウです。

2018年も、あっという間に3月の最後の週を迎えました。
ここしばらく、開発の案件が忙しく、ブログの原稿を作る時間が取れませんでした。
多少落ち着いてきたので、久しぶりに書いています。上の写真は、休みの日に息子と神奈川のほうに行く機会があり、桜が満開になっていたので写真を撮ってきました。やはり埼玉に比べると、少し神奈川のほうが暖かい気がしますし、桜も開花が早いですね！
久しぶりに長男と二人きりで電車に乗ったり、歩いたりして過ごしました。今度高校に入学しますが、いつの間にか成長している息子を見て、無事に育ってくれてとても感謝の気持ちが湧いてきました。これからいろいろとぶつかる壁や課題も出てくると思うけれど、頑張って乗り越えてほしいです。

それから、dbSheetClientの呉信用金庫様の事例がアップされています。
Excel I/Fの各種業務をdbSheetClientを使って、
圧倒的な処理スピードの向上を実現！（10分以上が瞬時に）
トップマネジメントを含めて全社員（約650名）が利用！
興味のある方は以下をご参照ください。
https://www.newcom07.jp/dbsheetclient/usrvoice/kure_shinkin.html

 ＜本日の題材＞
OVER句のROWS（SQL Server）

以前、このブログでも「RANK, DENSE_RANK」という題目で、ランキング関数の中でOVER句を使った内容を題材として取り上げましたが、OVER句と一緒に使用する引数に、ROWS句というものがあり、行の計算対象を指定することができるという機能がありましたので、今回、それを取り上げてみます。これは、SQL Server 2012からの機能で、以下のような記述で範囲を指定します。

OVER (PARTITION BY 項目名1
     ORDER BY 項目名2
     ROWS BETWEEN A AND B
     )

上記の書き方で、項目名1毎にグループ化しながら、項目名2の並び順で、AからBの範囲内での抽出結果を表示することができます。（PARTITION BY句、ORDER BY句、ROWS句は必要なときのみ指定します）

このときの、A、Bの記述の仕方には、以下のようなものがあります。

CURRENT ROW
  --- 現在の行
n PRECEDING
  --- 現在の行からn行前
n FOLLOWING
  --- 現在の行から n 行後
UNBOUNDED PRECEDING
  --- パーティションの最初の行
UNBOUNDED FOLLOWING
  --- パーティションの最後の行

例）今回は、平昌オリンピックで日本中が盛り上がったことから、オリンピックで日本が取ったメダル数をデータにしたものを使用します。

テーブルは以下の定義：
CREATE TABLE DBO.メダル数(
季節  VARCHAR(4)
,年    DECIMAL(4)
,メダル色 VARCHAR(2)
,個数     INT
,CONSTRAINT pk_メダル数 PRIMARY KEY (季節,年,メダル色));

このテーブルに、夏季、冬季の日本が取ったメダル数を登録してみます。

INSERT INTO dbo.メダル数 VALUES('夏季',1912,'金',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1912,'銀',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1912,'銅',0);
 
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'金',0);
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'銀',2);
INSERT INTO dbo.メダル数 VALUES('夏季',1920,'銅',0);
…
--途中省略
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'金',1);
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'銀',4);
INSERT INTO dbo.メダル数 VALUES('冬季',2014,'銅',3);

INSERT INTO dbo.メダル数 VALUES('冬季',2018,'金',4);
INSERT INTO dbo.メダル数 VALUES('冬季',2018,'銀',5);
INSERT INTO dbo.メダル数 VALUES('冬季',2018,'銅',4);

夏季、冬季それぞれの、各年毎、メダル色毎のメダル数と、直近3回のメダル色毎の合計数、平均数を抽出し、年の新しい順に抽出してみます。

SELECT
  季節
,年
,メダル色
,個数
,SUM(個数)
    OVER (PARTITION BY 季節,メダル色
      ORDER BY 年
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  
 　　-- 現在の行から2行前から現在行までの3行分
      ) AS 直近3回の合計個数
,AVG(個数)
    OVER (PARTITION BY 季節,メダル色
      ORDER BY 年
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
 　　 -- 現在の行から2行前から現在行までの3行分
      ) AS 直近3回の平均個数
FROM dbo.メダル数
ORDER BY 季節,年 DESC,メダル色;

次に、金、銀、銅で色を分けずに、季節、年毎のメダル数の合計について、最初からの累計の合計個数と平均個数を抽出し、冬季、夏季の年の新しい順に表示してみます。

WITH C_YEAR_MEDAL(季節, 年, 個数) AS
(SELECT 季節, 年, SUM(個数)
    FROM dbo.メダル数
  GROUP BY 季節, 年
)
SELECT
  季節
 ,年
 ,個数
,SUM(個数)
      OVER (PARTITION BY 季節
           ORDER BY 年
           ROWS BETWEEN UNBOUNDED PRECEDING 
                           AND  CURRENT ROW
　　                         -- 最初の行から現在行まで
           ) AS 累計の合計個数
,AVG(個数)
       OVER (PARTITION BY 季節
           ORDER BY 年
           ROWS BETWEEN UNBOUNDED PRECEDING
                            AND CURRENT ROW
   　　                      -- 最初の行から現在行まで
           ) AS 累計の平均個数
FROM C_YEAR_MEDAL
ORDER BY 季節 DESC, 年 DESC;

上記の結果から、OVER句 + ROWS句を使用することで、各行に対しての直近の数回や累計の合計などを抽出することができることが確認できました。

今日は以上まで

ITコーディネータのシュウです。

2018年を迎えて、皆様如何お過ごしでしょうか？　本年もよろしくお願いいたします！

この1月は、非常に寒いという印象ですが、実際、先週の1/22には関東地方に大雪が降りました。「南岸低気圧」というのが原因とのことです。前日は晴れていて、天気予報が言うように、本当に次の日雪が降るのかな？と思っていたところ、見事に大雪となり、帰りは電車の遅延がひどく、普段1時間ちょっとで行けるところが、3時間以上かかって帰りました。それも、少し早めに帰宅したにもかかわらずです。
次の日は、朝早く起きて雪かきをしましたが、寒い日が続く中、しばらく雪が溶けずにアイスバーンのようになって、自転車や歩くのにもとても気を使いました。
この「南岸低気圧」、4年前にも関東に大雪を降らせたということですが、今後とも、この言葉に注意して行く必要があるようです。

それから、dbSheetClientのシャープ様の事例がアップされています。
Excel I/Fの新PSIシステムを構築！
各営業部門と生産企画部門における情報一元化を実現！
生産投入の精度向上と経営課題である在庫削減にも貢献！
約600名がシステムを利用、更に新PSIシステムは進化を継続中！
興味のある方は以下をご参照ください。
https://www.newcom07.jp/dbsheetclient/usrvoice/sharp.html

＜本日の題材＞
動的なデータマスキング（SQL Server 2016）

SQL Server 2016の新機能で、動的なデータマスキングの機能があるということで、ちょっとだけ試してみました。

動的データ マスク (DDM) は、特権のないユーザーに対して個人情報などデリケートなデータをマスクし、データの公開を制限するものです。DDMによって、クエリの結果にマスクルールが適用され、データを変更せずにマスキングすることができます。DDMを使用すると、アプリケーションのセキュリティの設計とコーディングを大幅に簡略化することができるということです。

それでは、簡単な例で確認してみます。
テーブル「顧客マスタ」を作成するときに、マスキングをかけたい列にマスクルールを設定しますが、次の4種類のマスクを利用できるようです。

・既定(default)：指定のフィールドのデータ型に応じたフルマスク
　例えば文字列データ型 (char、 nchar、 varchar、 nvarchar、 text、 ntext) の場合は、XXXX またはそれ未満の数の X を使用。他にも、数値型や日付/時刻型にもそれぞれに対する設定あり。

・Email(email)：メールアドレスの最初の 1 文字と定数サフィックスの ".com" をメールアドレスのフォームで公開するマスク方法。

・ランダム(random)：ランダムマスク関数は任意の数字型に使用でき、指定した範囲内で生成したランダムな値でオリジナルの値をマスクします。

・カスタム文字列(partial)：間にカスタム埋め込み文字列を追加し、最初と最後の文字を公開するマスク方法。
prefix,[padding],suffix

上記を使ってテーブルを作成します。

CREATE TABLE 顧客マスタ 
 (顧客NO varchar(7) PRIMARY KEY,
   氏名 nvarchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL, 
  カナ nvarchar(100) MASKED WITH (FUNCTION = 'partial(0,"XXXXXXX",1)') NULL, 
  電話番号 varchar(13) MASKED WITH (FUNCTION = 'default()') NULL, 
  Eメール varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
   身長 numeric(4,1) MASKED WITH (FUNCTION = 'random(100, 200)'));

データを登録します。（架空のデータです）

insert into 顧客マスタ values('100001', '山田　昭男', 'ヤマダ　アキオ', '03-1234-5678', 'a-yamada@abc.co.jp', 182);
insert into 顧客マスタ values('100345', '小林　栄治', 'コバヤシ　エイジ', '080-9876-5431', 'eiji-koba@xyz.co.jp', 165);
insert into 顧客マスタ values('103025', '高橋　洋子', 'タカハシ　ヨウコ', '06-0001-9999', 't-yoko@test.ne.jp', 152);

作成したユーザでデータをSELECTします。

Select * from 顧客マスタ;

上記のように通常通りの結果が抽出されます。

次に、顧客マスタに対してSELECT の権限だけを与えたユーザー「BlogUser」を作ります。

CREATE USER BlogUser WITHOUT LOGIN;

GRANT SELECT ON 顧客マスタ TO BlogUser;

 この「BlogUser」で顧客マスタを検索したときに、動的データマスキングが有効になっていることを確認します。

EXECUTE AS USER = 'BlogUser';

SELECT * FROM dbo.顧客マスタ;

REVERT;

抽出結果を見ると、以下のようにマスキングされていることが確認されました。

・氏名は頭1文字のみが正しい値で、残りは「X」でマスキング
・カナは、最後の1文字のみが正しい値で、残りは「X」でマスキング
・電話番号は、全て「xxxx」でマスキング
・Eメールは、最初の1文字が正しい値で、途中は「X」、最後は「.com」でマスキング
・身長は、100～200の間でランダムな値にマスキング

ちなみに、このユーザー「BlogUser」について、SQL Server Management Studioで確認すると、以下のように、顧客マスタへの「選択」の許可のみがあることが確認できます。

今日は以上まで