バインド変数(Oracle)

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

IMG_1388

ゴールデンウイークも終わり、ときに夏を思わせるような暑い日もある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 ;
/

動的sqlリテラル

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 ;
/

動的sqlバインド変数

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

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

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

v$sql_統計情報

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

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

OVER句(Oracle)

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

IMG_1325

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

IMG_1352

日本には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;

86_1

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

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;

86_2

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

OVER句のROWS(SQL Server)

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

IMG_1284

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,メダル色;

sql1

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

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;

sql2_1
sql2_2

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

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

動的なデータマスキング(SQL Server 2016)

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

IMG_1132

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_1

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

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

CREATE USER BlogUser WITHOUT LOGIN;

GRANT SELECT ON 顧客マスタ TO BlogUser;

cre_user

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

EXECUTE AS USER = 'BlogUser';

SELECT * FROM dbo.顧客マスタ;

REVERT;

masking

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

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

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

bloguser

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

照合順序(SQL Server)

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

IMG_1048

IMG_1061

ちょっと前に、出雲神話で有名な島根県に行く機会があり、時間があったので、たくさんの銅鐸が出土した加茂岩倉遺跡と、銅剣がたくさん出土した荒神谷遺跡を見に行って来ました。上の写真が加茂岩倉遺跡、下が荒神谷遺跡です。
もともと神話など、古代の歴史に興味があったこともあり、出雲には何かあるような気がして、この考古学上の歴史的な発見であった2つの遺跡に是非行ってみたかったので、とてもいい機会でした。
アマテラスの弟で出雲族の祖とされ、ヤマタノオロチ退治で有名なスサノオノミコト、イナバノ白ウサギで有名な国造りを行ったオオクニヌシノミコト、そして出雲の国譲りなどいろいろな話しが残されています。古代の日本において、邪馬台国がどこにあったのか、そして大和朝廷がどのようにつくられていったのか、いろいろな説がありますが、とても興味深い内容ですね。いつか、真実が明らかにされる日が来るのを楽しみにしたいと思います。

いよいよ、今年も残すところあとわずかになりました。いつの間にか年末になってしまったという感じです。今年も1年間いろいろとありがとうございました。なかなか忙しく、記事をアップする頻度がとても少なくなってしまいましたが、来年も頑張って記事を書ければと思っていますので、どうぞよろしくお願いいたします!

<本日の題材>
照合順序(SQL Server)

SQL Serverで、データの文字の大小関係を比較する場合の基準となるものを、照合順序(Collation)と呼んでいます。例えば、アルファベットの「a」「A」、かなの「あ」「ア」「ア」を小さいほうから順に並べたらどう並ぶか、漢字の「川」「皮」ではどちらが大きいのかなどの、文字の大小関係を決めているものになります。
様々なシステムでは、データを名前順に並べるとか、データが一致するものを検索するなどの処理が多々存在します。この照合順序が異なれば、クエリの結果が違ってきてしまうため、この文字の大小関係を決める照合順序というものはとても重要な要素になります。

SQL Serverの照合順序には、SQL照合順序とWindows照合順序の2種類があるとのことですが、SQL照合順序はUnicode データ型をサポートしていなかった SQL Server 6.5 以前のバージョンとの互換性のみを目的としている照合順序であるため、基本的に照合順序と言えば、Windows照合順序のことと考えればよいようです。

それでは、例をあげて照合順序について確認してみます。
あるデータベースの照合順序を確認してみると、「Japanese_CI_AS」となっていますが、他にも、CS、KS、WSなどのオプションがあります。例えば、「Japanese_90_CS_AS_KS_WS_SC」。

それぞれを簡単に説明すると、

・Japanese:辞書順に並び変えた場合の並び順が、日本語辞書順であることを表しています。ただし、ソート順の定義で、
Japanese, Japanese_XJIS, Japanese_Bushu_Kakusu, Japanese_Unicode
などの種類があります。

・90:照合順序のバージョンを表しています。 90 はバージョン 9.0 である SQL Server 2005、100 はバージョン 10.0 である SQL Server 2008 を表しています。

・CS:大文字と小文字を区別します。このオプションを設定すると、大文字より小文字が先に並べ替えられます。

・AS:濁音・半濁音・アクセントの有無を区別します。このオプションを選択しないと、濁音・半濁音・アクセントが区別されません。

・KS:ひらがなとカタカナを区別します。このオプションを選択しないと、ひらがなとカタカナは同じものと見なされます。

・WS:全角文字と半角文字を区別します。このオプションを選択しないと、同じ文字の全角表記と半角表記は同じものと見なされます。

・SC:SQL Server 2012 以降で、辞書順に並べる場合に補助文字を認識するかどうかを区別します。

また、並び順は、辞書順ではなくバイナリ順 (ビット配列順、文字コード順、コードポイント順) もあります。バイナリ順の照合順序の名前は、Japanese_90_BIN や Japanese_90_BIN2 のように CS_AS_KS_WS の部分が BIN または BIN2 となります。

「Japanese_CI_AS」となっているデータベースで具体的に試してみます。「大小比較」というテーブルを作成し、そこに、下記のようにデータを登録し、データを抽出してみます。

insert into dbo.大小比較(no, 文字) values(1,'あ');
insert into dbo.大小比較(no, 文字) values(2,'ア');
insert into dbo.大小比較(no, 文字) values(3,'ア');
insert into dbo.大小比較(no, 文字) values(4,'皮');
insert into dbo.大小比較(no, 文字) values(5,'川');
insert into dbo.大小比較(no, 文字) values(6,'革');
insert into dbo.大小比較(no, 文字) values(7,'大');
insert into dbo.大小比較(no, 文字) values(8,'題');
insert into dbo.大小比較(no, 文字) values(9,'1');
insert into dbo.大小比較(no, 文字) values(10,'1');
insert into dbo.大小比較(no, 文字) values(11,'a');
insert into dbo.大小比較(no, 文字) values(12,'A');

select * from dbo.大小比較
order by no;

select1

select * from dbo.大小比較
order by 文字;

select2

次に、条件として、「1」に等しいものを抽出してみます。

select * from dbo.大小比較
where文字 = '1'
order by 文字;

select3

全角と半角が同じものとして認識されているのが確認できます。
同様に、条件として、「あ」に等しいものを抽出してみます。

select * from dbo.大小比較
where文字 = 'あ'
order by 文字;

select4

ひらがなとカタカナが同じものとして認識されているのが確認できます。

上記の内容を区別するためには、オプションの部分を変更する必要があります。

ここで、このテーブルを含む「TEST」データベースの照合順序を変更してみます。

ALTER DATABASE TEST COLLATE Japanese_CI_AS_KS_WS;

select5

データベースの照合順序を変更した状態で、先ほどのSQLを実行してみます。

select6

何故か、結果は同じです。これは、データベースの照合順序を変更しても、既存のテーブルの照合順序は変更されないためで、下記のように項目について照合順序を設定してテーブルを変更する必要があります。

ALTER TABLE dbo.大小比較
ALTER COLUMN 文字 varchar(20)
COLLATE Japanese_CI_AS_KS_WS;

select7

この状態で、再度、前回行ったSQLを実行してみます。

select8

select9

全角と半角文字、ひらがなとカタカナが区別されて、それぞれ該当する1行のみが抽出されるようになりました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

データベースメール(SQL Server)

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

せんがたき

 少し前に、山梨県の昇仙峡に妻と行って来ました。写真は、仙娥滝(せんがたき)と呼ばれる滝で、昇仙峡のシンボルである覚円峰(かくえんぼう)の麓にあり、駐車場から徒歩5分くらいのところにあります。この滝は日本の滝百選にも選ばれていて、落差は30mあり、地殻変動による断層によって生じたものとのことです。

学生時代に一度昇仙峡に行った記憶があり、岩の上から下を見て、高いなあと思った記憶があったのですが、今回は、渓谷に沿って紅葉を見ながら妻と歩くことにしました。
秋晴れの中、たくさんの観光客が遊歩道を歩き、渓流や紅葉を楽しんでいました。カップルが皆手をつないで歩いていたので、私も妻と手をつなぎながら、久しぶりに夫婦で自然を満喫することができました。

<本日の題材>
データベースメール(SQL Server)

会社の同僚が、お客様のシステムで、SQL Serverのデータベースメールの機能を利用して、必要なタイミングでいろいろな情報をメールで知らせる仕組みを作ったということを聞き、今回はこれを題材にしてみます。ちなみに、確認したデータベースのバージョンは、SQL Server2014です。(データベースメールの機能は、SQL Server2012からのようです)

設定の方法ですが、SQL Server Management Studioを起動し、オブジェクトエクスプローラーの「管理」の中にある「データベースメール」を選択し、右クリックを押します。

設定1

そこで表示されるメニューの「データベースメールの構成」をクリックすると、以下のようなデータベースメール構成ウィザードが表示されますので、「次へ」をクリックします。

設定2

「構成タスクの選択」画面が出るので、「次のタスクを実行してデータベースメールをセットアップする」を選択した状態で、「次へ」ボタンを押下します。

設定3

すると、設定が初めての場合には、以下のようなメッセージが出ます。「データベースメール機能は使用できません。有効にしますか?」
ここで「はい」を選択します。

設定4

次に、「新しいプロファイル」設定画面が出てくるので、「プロファイル名」を設定した後、SMTPアカウントの「追加」ボタンをクリックします。

設定5

ここで、SMTPサーバーの設定を行います。

設定6

設定ができたら、「OK」ボタンを押下します。
(SMTP認証の設定が必要な場合は、その部分も設定します)

設定7

新しいプロファイルの画面に戻り、アカウントが追加されたているのを確認して、「次へ」ボタンをクリックします。

設定8

プロファイルセキュリティの管理の画面が出るので、確認してから「次へ」を押下します。

設定9

システムパラメータの構成の画面が出るので、こちらも確認して、「次へ」を押下します。このとき、禁止する添付ファイルの拡張子など、追加したい場合には、追加します。

設定10

ウィザードの完了画面が出るので、「完了」を押下すると、構成を実行します。

設定11

「成功」となれば、「閉じる」で終了します。
次に、メール送信のテストを行ってみます。

設定12

SQL Server Management Studioの「管理」の「データベースメール」を選択し、右クリックを押して、「テスト電子メールの送信」を押下します。

設定13

宛先を設定し、「テスト電子メールの送信」ボタンを押下します。

設定14

これによって、宛先のメールアドレスにメールが送付されることを確認します。

設定15

以上の手順で、SQL Serverのデータベースメールの設定とテストまで簡単にできました。

ただ、SQLを使用するアプリケーションの中で、このデータベースメールの仕組みを使ってSQLの結果などをメールで送付したいというのが目的だったので、SQLのプロシージャを使ってこのデータベースメールを送ってみます。

システムで用意しているプロシージャ「sp_send_dbmail」を使って、下記のようなSQL文を作成して実行してみます。

--メール送信
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'メール送付'
,@recipients = 'testuser@newcomblog.jp'
,@subject = '処理完了通知'
,@body = 'データベースメールテスト。'
,@query = 'SELECT * FROM BLOG.dbo.DEPT'
,@attach_query_result_as_file = 1
;

設定16

ここで、@querry 変数にSQL文を指定すると、メールにSQLの結果を付けることができます。(このとき、テーブルはデータベース名から指定します)
また、@attach_query_result_as_file = 1 と指定することで SQL の結果を添付ファイルにして送ることができます。

宛先のメールアドレスに上記の内容のメールが届けば成功です。

設定17

添付ファイルの中身を見ると以下のようになっています。

設定18

データベースメールの仕組みを使って、SQLの結果をメールで送付できることが確認できました。

今日は以上まで

 

 

 

にほんブログ村 IT技術ブログへ
にほんブログ村

Management Studioでのデータ編集

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

IMG_0896

お久しぶりです。あっという間に10月の後半に入ってきました。最近急に寒くなってきましたが、お元気でお過ごしでしょうか?
しばらく開発の仕事がとても忙しく、ブログを書こうと思いながら、ずるずると時が経ってしまいました。申し訳ありません。

この間、いろいろなことがありましたが、大きく記憶に残っていることとして、高校野球で、なんと埼玉の花咲徳栄高校が埼玉県勢として初めて夏の高校野球での優勝旗を持ち帰ったことです。
実は、花咲徳栄高校は結構自宅から近いのと、知人のお子さんでも結構そこに通っているので、とても親近感が湧くのですが、まさか優勝するとは思っていませんでした。
投手2枚看板と打線も毎試合2桁安打で、とても強かったですね。嬉しい限りです。

また、国際情勢が緊迫してきている中、衆議院が解散されて、もうすぐ選挙ですね。高校3年生になって18歳の誕生日を迎えた3番目の娘も、選挙権があるということでカードが送られてきたので、初めての選挙に行くと張り切っていました。こういう時、子供が育つのは早いなと、つくづく思わされます。

それから、事例がまたアップされています。
dbSheetClientで内製化を実現
土木・建築業務における各種システムを構築し、データは統合DBに格納!
情報システム部と設計本部がシステム開発・管理を主導!ということで、株式会社鴻池組様の事例です。
興味のある方は以下をご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice/konoike_construction.html

<本日の題材>
Management Studioの使い方(SQL Server)

SQL Serverで、テーブルのデータを変更したいが、いちいちupdate文を書くのが面倒なとき、あるいは時間に余裕がないときに、ツールで簡単に変更できたらいいと思うことがよくありますよね。
SQL Server のManagement Studioというツールをデータの確認などでよく使用しますが、そこでは、テーブルを右クリックすると、「上位200行の編集」という機能があって、行が少ないときはそれで目的のデータの変更ができますが、テーブルの行数が多い場合には、目的のデータを一覧から探すのが面倒なので、そこでの変更はあきらめてしまうことも多いと思います。
それでいい方法がないかと調べてみたところ、Management Studioでうまくできるということなので、それを試してみたいと思います。

SQL Server Management Studioを起動し、データを変更したいデータベースのテーブルにカーソルを合わせた後、マウスの右クリックで表示されるメニューの「上位200行の編集(E)」を選択すると、データの編集ができることはご存知だと思います。

ManS_データ編集

その後、以下のようなデータの編集画面が表示されますが、

ManS_編集画面

ここで、さらに、メニューの「クエリーデザイナー」で表示される「ペイン」、さらに「SQL」を選択すると、下記のようなウィンドウが表示されます。

ManS_pain

この上側のSQLのところに、今回編集したいデータを絞るための条件を追加してみます。今回は「拠点」が「シカゴ」の社員データのみを変更したいので、「WHERE 拠点=’シカゴ’」を入れて、再度SQLを実行します。

ManS_pain_実行

すると、「拠点」が「シカゴ」の社員データのみが結果に表示され、データ編集も可能になるので、そこで値の変更を行います。

ManS_変更実施

こうすることで、行数が多い場合でも、条件を指定して絞ることで、Management Studioの「上位 n 行の編集」でデータの変更をすることができます。

なお、そもそも「上位200行の編集」となっているのを、もっと多い行数に変更することも可能です。

メニューの「ツール」>「オプション」>「SQL Server オブジェクト エクスプローラー」を選択すると、下記のような画面が出てきますので、「上位 <n> 行の編集コマンドの値」を「200」からもっと多い値に変更すればよいです。

ManS_上位n行編集

 
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

ビットマップインデックス(Oracle)

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

1395797133260_2

今月27日に再び発生した大規模なサイバー攻撃によって、世界各地で被害が出ています。今回のランサムウェアは専門家の間で「Petya」と呼ばれるウイルスが使われたとみられているそうです。WannaCryと同様に、WindowsのSMB(Server Message Block)の脆弱性(通称Eternal Blue)を利用しているとのことで、対策としては、Windowsを常に最新の状態にすること、アンチウィルスソフトウェアを最新のものにしておくこと、さらにメールにあるURLをクリックする際には十分な注意が必要ですね。また、いざというときにバックアップを取ることも推奨されています。
IPA独立行政法人情報処理推進機構セキュリティセンターが出している「情報セキュリティ10大脅威 2017」でも、個人向け、組織向けともに、ランサムウェアによる被害が2位に入っていますね。組織向けの1位は、こちらもよく話題になる標的型攻撃による情報流出です。様々な情報を扱う企業としては、社員への教育を含め様々なリスクを考慮した対策が必要ですね。
本当に、いつも危険と隣り合わせていることを自覚しながらPCを使わないといけない時代になってきたと感じます。

さて、dbSheetClientに新しい事例がまたアップされています。
dbSheetClientで内製化を実現、MS-Accessで開発した『総合管理システム』をdbSheetClientでWeb化し、抱えていた課題を全てクリア !!ということで、綜合エナジー株式会社様の事例です。
興味のある方は以下をご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice/sogo_energy.html

<本日の題材>
ビットマップインデックス(Oracle

Oracleで検索の性能を向上するためにインデックスを作成しますが、データウェアハウスなどを構築した際には、カーディナリティの値が低い(取りうる値が限られている)列に対しては、ビットマップインデックスを作成することで効果があるという話しを聞くことがよくありますが、今回、それを試してみたいと思います。

例)
性別、血液型などのような取りうる値が限られている列を持つ顧客マスタを作成し、そこに300万件の顧客データをテスト的に作成してみたいと思います。前回題材に上げたバルク処理を使って、データの登録処理時間を短縮したいと思います。

まず、顧客マスタテーブルを作成します。

CREATE TABLE t_customer(
 c_id number(7)
,c_gender  varchar2(4)          -- 性別
,c_blood_type varchar2(2)  -- 血液型
,c_age     number(3)                  -- 年齢
,constraint PK_t_customer Primary key (c_id));

次に、前回題材として取り上げたバルク処理を使って、テストデータを作成してみます。

 DECLARE
  TYPE c_id_t IS TABLE OF t_customer.c_id%TYPE
  INDEX BY PLS_INTEGER;
  c_id c_id_t;

  TYPE c_gender_t IS TABLE OF t_customer.c_gender%TYPE
  INDEX BY PLS_INTEGER;
  c_gender c_gender_t;

  TYPE c_blood_type_t IS TABLE OF t_customer.c_blood_type%TYPE
  INDEX BY PLS_INTEGER;
  c_blood_type c_blood_type_t;

  TYPE c_age_t IS TABLE OF t_customer.c_age%TYPE
  INDEX BY PLS_INTEGER;
  c_age c_age_t;

BEGIN
 FOR i IN  1..3000000  LOOP
   c_id(i) := i;
   c_gender(i) := CASE mod(c_id(i), 3) WHEN 0 THEN '男性' WHEN 1 THEN '女性' ELSE null END;
   c_blood_type (i) := CASE mod(c_id(i), 5) WHEN 0 THEN 'A' WHEN 1 THEN 'B' WHEN 2 THEN 'O' WHEN 3 THEN 'AB' ELSE null END;
   c_age(i) := 20+mod(c_id(i), 50);
 END LOOP;

 FORALL j IN 1..3000000
   INSERT INTO t_customer(c_id, c_gender, c_blood_type, c_age) VALUES(c_id(j), c_gender(j), c_blood_type(j), c_age(j));
END;
/

今回も、処理時間を測定したいので、sqlplus 上で以下を実行してから、上記の処理を実行します。
SET TIMING ON
上記のpl/sqlを実行します。

blog80_buld

24.89秒で処理が終了しています。これを、通常のLOOP処理で実行すると、3分22秒程度時間がかかりましたので、やはりバルク処理が有効だということを改めて確認しました。

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

SELECT * FROM t_customer
ORDER BY c_id;

blog80_select

件数も確認してみます。
Select count(*) from t_customer;

blog80_count

データが確かに300万件登録されているのが確認できます。

この t_customer テーブルのデータの中に、男性以外(NULLデータも含む)で、血液型が「A」型と「B」型の人が何人いるかを抽出してみます。そのときも実行計画も合わせて表示させてみます。

set autotrace on

select count(*) from t_customer
where c_gender <> '男性'
 and c_blood_type in ('A','B');

blog80_jiko1

実行計画を見ると、t_customerテーブルを「TABLE ACCESS FULL」で全レコードにアクセス(フルスキャン)して該当レコードの件数を確認していることがわかります。

この t_customer テーブルの性別、血液型項目に通常のインデックスを作成してみます。

CREATE INDEX cust_idx2 ON t_customer (c_gender);
CREATE INDEX cust_idx3 ON t_customer (c_age);

blog80_btree_ind

それぞれ、7.2秒、5.98秒と、多少時間がかかることがわかります。

この状態で、再度先ほどの男性以外で、血液型が「A」型と「B」型の人が何人いるかを、同じSQLで実行してみます。

blog80_jiko2

実行計画は、先ほどのインデックスがないときと同じで、特に追加したインデックスを使用しない動作になっています。
カーディナリティの値が小さい列へのBtreeのインデックスは作成してもあまり効果がないとも言われます(実行するSQLにも当然依ります)が、DBがフルスキャンを選択したことは今回のケースではBtreeインデックスは有効ではないことをがわかります。

今度は、先ほどのインデックスを削除し、性別、血液型項目にビットマップインデックスを作成してみます。

CREATE BITMAP INDEX cust_bm_idx2 ON t_customer (c_gender);
CREATE BITMAP INDEX cust_bm_idx3 ON t_customer (c_blood_type);

blog80_bitmap_ind

ビットマップインデックスの作成時間はかなり短いですね。

この状態で、再度先ほどの男性以外で、血液型が「A」型と「B」型の人が何人いるかを、同じSQLで実行してみます。

blog80_jiko3

今度は、作成したビットマップインデックスを使用した実行計画になり、処理の実行時間も0.17秒とかなり短縮されることがわかります。このように、データにnullが含まれている場合でも、カーディナリティが低い列であれば、ビットマップインデックスを作成することで、抽出処理が高速化することが確認できると思います。
※ただし、ビットマップインデックスを作成した状態での、データの登録や更新は、b-treeのインデックスがある場合に比べてかなり遅くなることも事実であり、データウェアハウスなどの抽出が中心のデータベースの場合などに絞ったほうがよいようです。
 
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

バルク処理(Oracle)

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

IMG_0682

5月もあと2日になりました。あっという間に日が過ぎて行きますね。今年もG/Wのときに、加須市の玉敷神社の藤まつりに行って来ました。上記はその写真です。たまに家族みんなで出かけるのもいいものです。
そう言えば、車に乗っているときに、家内が道端にきれいに咲いていた芝桜があったらしく、きれいね~と言ったので、桜ということで、思わず空のほうを見て桜を探してしまったところ、また笑われてしまいました。

芝桜と言えば、日本各地にきれいで有名なところはあると思いますが、宮崎県新富町に、目の見えなくなってしまった奥様に笑顔を取り戻したいということで、20年以上かけて庭に一面のピンクの芝桜を育てた黒木さんという方の庭の芝桜がきれいだということで有名みたいですね。フジテレビのMr.サンデーという番組でも放送され、とても感動的で反響が多かったそうです。黒木さんは毎年芝桜のシーズンになるとご自宅の庭をボランティアで開放、オープンガーデンとし、連日多くの見物客に花を楽しませてくださっておられるとのことです。そこに込められた夫婦愛が素晴らしいですね。そのように妻を愛せる夫になりたいものです。
http://www.pmiyazaki.com/etc/sibazakura/
ただ、一面に広がるシバザクラを皆さんに開放するのは、今年(2017年)で最後となるとのことです。芝桜の手入れはとても大変なようで、ご高齢ということもあり、残念ですが、本当に長い間お疲れ様でした。

さて、dbSheetClientに新しい事例がまたアップされています。dbSheetClientを使って全社をカバーする「予算実績管理システム」を構築! SAP(ERP)システムとのデータ連携も実現!ということで、クレスコ・イー・ソリューション株式会社様の事例です。興味のある方は以下をご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice/cresco_esol.html

<本日の題材>
バルク処理(Oracle

OracleでPL/SQLを使って、ループの処理を行うことはよくあることだと思います。そのループ文などのPL/SQLプログラムは、内部的にはSQLエンジンとPL/SQLエンジンの2つのエンジンが、それぞれSQL文とPL/SQL文の処理を担当して実行するため、ループの回数分エンジンの切替が発生しています。この制御の移行はコンテキスト・スイッチと呼ばれ、その都度オーバーヘッドが発生して、パフォーマンスが低下することになります。
このようなオーバーヘッドを削減できる機能として、バルク処理というものがあり、エンジンの切替を最小限に抑えることができるということです。今回は、このバルク処理について取り上げてみたいと思います。

例)
顧客マスタを作成し、そこに20万件の顧客データをテスト的に作成してみたいと思います。それを、今回は FOR LOOP文で作成してみますが、その際に、普通にLOOP処理を行うのと、バルク処理を行うので、処理時間も比較して見ます。

まず、顧客マスタテーブルを作成します。

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

次に、FOR LOOP文で、テストデータを作成します。

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;
/

今回は、処理時間を測定したいので、sqlplus 上で以下を実行してから、上記の処理を実行します。

SET TIMING ON

forloop

処理に8.25秒かかっていますね。データを念のため確認してみます。

SELECT * FROM customer
ORDER BY c_id;

blog79_select_cust

データが登録されていることが確認できます。

このようなINSERTの処理を、バルク処理で行う場合、FORALL文を指定して実行します。

DECLARE    -- ①
 TYPE c_id_t IS TABLE OF customer.c_id%TYPE
 INDEX BY PLS_INTEGER;
 c_id c_id_t;

 TYPE c_name_t IS TABLE OF customer.c_name%TYPE
 INDEX BY PLS_INTEGER;
 c_name c_name_t;

BEGIN
 FOR i IN  1..200000  LOOP        -- ②
   c_id(i) := i;
   c_name(i) := '顧客名_'||LPAD(TO_CHAR(i),8,'0');
 END LOOP;

  FORALL j IN 1..200000
   INSERT INTO customer(c_id, c_name) VALUES(c_id(j), c_name(j));       -- ③

END;
/

まず、①宣言部で、TABLE型のコレクション c_id, c_name を定義します。ここで、コレクション変数とは、同じデータ型の値を複数格納できる変数のことです。そして、②のFOR LOOP文でそのコレクションに200000行を代入します。その後、③のINSERT文のVALUES句にこのコレクションを指定し、200000行のデータを customer表に一括挿入します。このとき、DML分の直前に FORALL文を指定します。

構文は以下:
  FORALL <索引名> IN <下限値>..<上限値> <DML文>

この処理では、INSERT文はSQLエンジンで一度に処理できるため、エンジンの切替は1回で済むことになり、高速化されます。

実際に試してみようと思いますが、データを削除した後、パフォーマンスの比較を正確に行いたいので、一旦メモリをフラッシュします。

truncate table customer;

connect / as sysdba
ALTER SYSTEM FLUSH BUFFER_CACHE;

上記のpl/sqlを実行します。

blog79_bulk

通常のLOOP処理で8.25秒かかっていた20万件の登録処理が、0.88秒で終わったことが確認できます。

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

SELECT * FROM customer
ORDER BY c_id;

blog79_select_cust2

SELECT COUNT(*) FROM customer;

blog79_count

データが確かに20万件登録されているのが確認できます。
バルク処理を行うことで、処理はかなり高速化されることがわかりますね。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

実行計画の取得(Oracle)

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

DSC_0007

あっという間に桜は散ってしまい、かなりの日数がたってしまいました。もうGWですね。
上の写真は、桜が咲いていたときに、娘が近くの公園で夕方にきれいだったと撮ったものです。空が青くなって幻想的な雰囲気がしますね。
ちょうど満開のときの週末に雨が降ったので、お花見ができなかったのが残念でした。来年を期待しよ~っと!

さて、dbSheetClientに新しい事例が2つまたアップされています。1つは6年前にシステムを構築した後、改定を続けながらフル活用し、データベースもPostgreSQLにダウンサイジングしたSOLIZE Products株式会社様の事例、もう一つは、dbSheetClientを使って金型製作における基幹システムを構築、内製化した株式会社黒田製作所様の事例です。
興味のある方は以下をご参照ください。
http://www.newcom07.jp/dbsheetclient/usrvoice_sequel/solize_products.html
http://www.newcom07.jp/dbsheetclient/usrvoice/kurodaseisakusyo.html

<本日の題材>
実行計画の取得(Oracle

データベースがSQL Serverであれ、Oracleであれ、性能の問題が発生した場合には、実行計画を取得して、SQLがどのような手順で処理を行っているのかを確認して、対策を取るということが行われます。

SQL Serverの場合には、Management StudioでSQLを実行する際に、ツール上で推定実行プランの表示や実行計画を結果に含めるなどの設定ができるので、簡単に実行計画を取得できますが、Oracleの場合はどうだったか?とすぐにやり方が出て来ない場合も多いと思いますので、簡単にやり方を上げておきます。※SQLPlusやSQL Developerなどのツールを使って実行計画を取得する方法です。

以下の2つのやり方がよく利用されると思います。
①EXPLAIN PLAN文を使用する
②AUTOTRACE を ON にする

まず、①についてです。
①は、EXPLAIN PLAN FOR + SQL文によってオラクルのオプティマイザが選択した「実行計画(予定)」を取得するというものです。
※ EXPLAIN PLAN 文による実行計画の取得では、SQLの実際の実行は行なわれないため、実行結果やSQL統計情報は取得できません。

・事前準備
EXPLAIN PLAN FOR + SQL文によって、実行計画は、PLAN_TABLE という表に格納されますので、あらかじめそれを用意しておく必要があります。

sqlplusなどで、実行計画を取りたいSQLを実行するユーザにログインします。

sqlplus test/*****
接続されました。

Oracle側で用意している utlxplan.sql というスクリプトを実行します。これは、オラクルのホームディレクトリの下の、rdbms/adminフォルダの下にあるので、以下のようにしてスクリプトファイルを実行します。

SQL> @?/rdbms/admin/utlxplan.sql

utlxplan

 sqlplusにおいて「?」はOracle Homeディレクトリに置換されます。つまり、「@?/sqlplus/admin/plustrce.sql」は、「@$ORACLE_HOME/sqlplus/admin/plustrce.sql」の意味となります。

このスクリプトでは、実行計画の情報を格納する「PLAN_TABLE」表を作成しています。念のため、表の定義を見てみます。

DESC PLAN_TABLE

plan_table

表が作成されれば準備ができましたので、それでは、試してみます。以前、ブログで取り上げた「MINUS」という集合演算子を使ったSQLの実行計画を見てみます。

explain plan for
SELECT * FROM tbl_B
MINUS
SELECT * FROM tbl_A;

explain_plan_1

 ※tbl_A、tbl_B は同じ構造のテーブルです。

次に、実行計画を表示します。このとき、DBMS_XPLAN パッケージに用意されている表関数を使用します。

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain_plan_1_結果

上記の結果から、このSQLは、「TBL_B」に対する全表スキャンを行った後にソート処理(SORT UNIQUE)を行い、さらに「TBL_A」に対する全表スキャン、ソート処理(SORT UNIQUE)を行って、最後にその差分を取っていることがわかります。このことから、件数が増えてくればかなり性能的には遅くなる可能性のある処理であることがわかりますね。

これと同等の結果を出す処理を、EXISTS句を使って、別のSQLで実現し、それについても実行計画を取得してみます。

explain plan for
SELECT * FROM TBL_B
WHERE NOT EXISTS
(SELECT 'X' FROM TBL_A
   WHERE CD_A = TBL_B.CD_B);

explain_plan_2

実行計画を表示してみます。

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain_plan_2_結果

今回のSQLでは、「TBL_B」に対して全表スキャンを行い、それぞれの行に対して「TBL_A」に対しては索引一意スキャンを行って、ネステッドループ結合を行うかたちになっているのがわかります。先ほどの処理に比べれば、Costの値も低く、こちらのほうが性能がよいと言えます。

次は、②AUTOTRACE を ON にするについてです。

sqlplus などで次のコマンドを実行します。
set autotrace on

ただし、実行計画と統計情報は、動的パフォーマンス・ビューも参照する必要があるため、必要な権限がないと一般ユーザーには使用できません。その場合は、以下のようにエラーが出ます。

SP2-0618: セッション識別子が見つかりません。PLUSTRACEロールが有効かを確認してください。
SP2-0611: STATISTICSレポートを使用可能にするときにエラーが発生しました。

・事前準備
PLUSTRACEロールを作成して、対象のユーザに付与する必要があります。そのためには、SYSDBA 権限で接続した後に、plustrace.sql というスクリプトを実行します。

sqlplus / as sysdba
@?/sqlplus/admin/plustrce.sql

plustrace

次に、対象のユーザに、PLUSTRACE ロールを付与します

GRANT PLUSTRACE TO test;

grant_plustrace

対象のユーザに接続して、AUTOTRACE を ON にします。

CONNECT test/*****
SET AUTOTRACE ON

autotrace

先ほど試したSQLを実行します。

SELECT * FROM TBL_B
WHERE NOT EXISTS
 (SELECT 'X' FROM TBL_A
   WHERE CD_A = TBL_B.CD_B);

autotraceon_sql

すると、上記のように、SQLを実行した結果と、実行計画、及び統計情報が表示されるのが確認できます。
実行計画の表示を止めたい場合には、set autotrace off を実行します。

autotrace_off

 
今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

主にSQLについて書いていきたいと思います。