1年の終わりに

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

IMG_2088_Original

今年もいよいよ残り2日。
会社も休みに入り、1年間を振り返り、反省する時期になりました。時間を取って1年を総括し、来年に向けてしっかりと準備していければと思いますが、今年は正月に実家にも帰って、成長した孫の姿を親に見せたいと思います。
写真は、会社で7月に行った、飯能市のムーミンバレーパークで撮った写真です。家族も連れて行ってもいいということで、子供たちも写真を撮りまくっていましたね。感謝です。

さて、今年のブログは、ほんの数回しか上げることができなかったのですが、データベーストリガーについて、参考になったという方がいましたので、次は早いうちにSQL Server版も取り上げていけたらと思っています。

来年も、皆様にとって良い年となりますように、心からお祈りいたします。

今日は以上まで

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

照合順序(SQL Server)の異なるデータベース間のジョイン

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

 出雲大社

今年もあっという間にあと数日というところまで来てしまいました。今年の後半は仕事もかなり忙しくなり、なかなか時間も取れず、ブログの更新ができませんでしたが、気力を振り絞って書いています。
写真は、今年田舎に帰省した時に寄った出雲大社です。久しぶりに父親と一緒にお参りしたのですが、父親が子供のころは、正月に自転車で数時間をかけてお参りに行ったという話を聞き、今は車で1時間もかからずに行けるので、昔は大変だったんだなと改めて感じました。中を歩くと、旧暦の10月に全国から出雲大社に集う八百万の神様がお泊りになるという十九社という社があるのも初めて知りました。出雲地方以外、旧暦の10月のことを普通は神無月といいますが、出雲地方では神在月と言います。おもしろいですね。

さて、今年は元号が新しく令和になった記念すべき年でしたね。私としては、特にラグビーワールドカップでは、ONE TEAMという言葉がとても心に残りました。思い出せばいろいろなことがありましたが、大晦日に1年を振り返ってよかったと言えるように、最後まで頑張ろうと思います。皆さんも頑張りましょう!

<本日の題材>
照合順序(SQL Server)の異なるデータベース間のジョイン

SQL Serverの照合順序について、以前取り上げましたが、今回は、異なる照合順序のデータベース間でのデータのジョインをしたいケースが発生した場合に、可能なのかどうかについて確認してみました。これは、新しく作成したデータベースから、元々運用していた基幹のデータベースのテーブルを参照して抽出を行おうとしたら、照合順序が違うためにエラーが発生したという問合せを受けたことがあったので、自分でも一度試してみたいと思ったためです。照合順序とは、データの文字の大小関係を比較する場合の基準となるものをいうということで、前回説明しましたように、複数のテーブル間のジョインを行う際にも、重要な要素になります。

例)
照合順序が「Japanese_CI_AS」の既存のデータベース「BLOG」とは別に、「Japanese_90_BIN」という新しいデータベース「BLOG2」を作成します。

データベース作成時の照合順序設定

 ここで、元々「Japanese_CI_AS」の照合順序のデータベース「BLOG」にあるテーブル「BUMON_M」と同じレイアウトのテーブルを、新しく作成した照合順序が「Japanese_90_BIN」のデータベース「BLOG2」に作成して、データも登録します。

その後、「SYAIN_M」と「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

まず、「BLOG」データベース内でジョインする場合は、

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM dbo.SYAIN_M s
JOIN dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD
ORDER BY b.BUMON_CD, s.SYAIN_CD;

sql1

 上記を、「BLOG」データベースの「SYAIN_M」と先ほど作成した「BLOG2」データベースの「BUMON_M」とをジョインしてデータを抽出する処理を行ってみます。

すると、下記のようなエラーが返ってきます。
「メッセージ 468、レベル 16、状態 9、行 4
equal to 操作の "Japanese_90_BIN" と "Japanese_CI_AS" 間での照合順序の競合を解決できません。」

sqlエラー

上記の場合に正しく結果を出す方法は、データベースの照合順序を同じになるように、データベースを作り直すという方法もありますが、ジョインする項目について、COLLATE句を使用する方法があります。

SELECT b.BUMON_CD, b.BUMON_NAME, s.SYAIN_CD, s.SYAIN_NAME
FROM BLOG.dbo.SYAIN_M s
JOIN BLOG2.dbo.BUMON_M b
ON s.BUMON_CD = b.BUMON_CD COLLATE Japanese_CI_AS
ORDER BY b.BUMON_CD, s.SYAIN_CD;

ジョイン成功

エラーなく、結果が抽出されたことが確認できます。ただし、ジョインなどで必要な項目については全て、SQLの中でCOLLATE句の設定が必要になるため、面倒な部分もあります。

ちなみに、データベース自体の照合順序を変更する場合は、前回のときに取り上げたように、ただデータベースの照合順序を変更するだけではなく、既存のテーブルの各項目について、COLLATE文で照合順序を指定してALTER TABLE文を実行する必要がありますので、照合順序を正しく設定した空の新規データベースを作成した後に、テーブルの作成と、既存データの登録を行ったほうが早いケースもあると思います。

今日は以上まで

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

データベーストリガー2(Oracle)

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

11216014_51

今年は、例年になく暑い5月でしたね。26日には北海道で5月としての全国の観測史上最高気温となる39・5度を観測するなど、異常な暑さでした。今からこの分だと、今年の夏はどうなるか?と心配してしまいます。

5月の中頃(5/19)はまだそれほどではなかったので、知人のご家族と一緒にバーベキューに行ってきましたが、天気も良く快適でした。
写真は埼玉県の羽生スカイスポーツ公園というところで、公園の名前にもあるように、スカイスポーツへの関心をもってもらおうという目的のもと整備された公園で、毎週土日祝日にはグライダーの飛行活動も行っていて、そこのクラブに入会すれば、実際にグライダーに乗ってスカイスポーツを楽しむことも出来るようです。

当日は、母の日も近かったので、日頃苦労している妻に感謝しようということで、準備は全てお父さんと子供たちで行って、妻には食べて楽しんでもらおうという企画で行いました。サプライズの花のプレゼントも用意して、妻もとても喜んでくれました。
たまにはごまをすっておかないと!

<本日の題材>
データベーストリガー2(Oracle)

ORACLEでのデータベーストリガーについて、前回取り上げましたが、前回は代表的なものとして、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動する「DMLトリガー」について取り上げてみました。
今回は、DDL操作(CREATE, ALTER, DROPなど)により起動する「DDLトリガー」について、試してみたいと思います。

例)
今回は、Oracleのデータベースについて、CREATE、DROP、ALTERという操作を行ったときに、どのユーザーが、どのオブジェクトに対して、いつ操作を行ったかをログに記録するというトリガーを作成してみます。
このとき、データベースに対してのトリガーを作成するためには、ADMINISTER DATABASE TRIGGER権限が必要です。
今回は、DBAロールを持つ、「test_admin」というユーザーでトリガーを作成します。

test_adminでログインした後、まず、処理のログを登録する「database_ddl_log」テーブルを作成します。

CREATE TABLE database_ddl_log (
user_name VARCHAR2(30),
activity VARCHAR2(20),
obj_name NVARCHAR2(30),
obj_type VARCHAR2(20),
event_date DATE
);

create_log

次に、トリガーを作成します。

CREATE OR REPLACE TRIGGER db_ddl_record
AFTER CREATE OR DROP OR ALTER ON DATABASE
DECLARE
   V_SYSEVENT   VARCHAR2(20);
   V_OBJ_OWNER   VARCHAR2(30);
   V_OBJ_NAME   VARCHAR2(30);
   V_OBJ_TYPE   VARCHAR2(20);
BEGIN
   V_SYSEVENT   := ora_sysevent;         -- イベントの取得
   V_OBJ_OWNER := ora_dict_obj_owner;   -- オブジェクトの所有者の取得
   V_OBJ_NAME   := ora_dict_obj_name;   -- オブジェクト名の取得
   V_OBJ_TYPE   := ora_dict_obj_type;   -- オブジェクトタイプの取得

INSERT INTO database_ddl_log
(user_name, activity, obj_name, obj_type, event_date)
VALUES (V_OBJ_OWNER, V_SYSEVENT, V_OBJ_NAME, V_OBJ_TYPE, SYSDATE);
END;
/

2行目に、AFTER CREATE OR DROP OR ALTER ON DATABASE と記載していますように、DATABASE全体において、CREATE、DROP、ALTER 文が実行された後に、起動するトリガーになります。そして、9行目から12行目までは、ora_ で始まるイベント属性関数を参照することで、必要な情報を一旦変数に格納し、最後にテーブルに登録します。

それでは、「BLOG_TEST」ユーザーでログインして、(1)新しくテーブルを作成、(2)定義の変更、(3)テーブルの削除をしてみます。

connect_blog_test

(1)   テーブルの作成
CREATE TABLE test_tab(
id INT
,name NVARCHAR2(30)
,CONSTRAINT PK_test_tab PRIMARY KEY (id));

cre_test_tab

(2)   テーブル定義の変更
ALTER TABLE test_tab
MODIFY name NVARCHAR2(50);

alter_tab

(3)   テーブルの削除
DROP TABLE test_tab;

drop_tab

それでは、上記の操作がログとして残っていることを確認します。
「test_admin」ユーザーで接続して、「database_ddl_log」テーブルを確認してみます。

connect_admin

SET LIN 120
COL user_name FORMAT A15
COL activity FORMAT A15
COL obj_name FORMAT A20
COL obj_type FORMAT A10
COL event_date FORMAT A20

SELECT
user_name, activity, obj_name, obj_type
, TO_CHAR(event_date, 'YYYY/MM/DD HH24:MI:SS') event_date
FROM database_ddl_log
ORDER BY event_date;

select_log

ログテーブルを抽出したところ、先ほどの処理(テーブル作成、テーブル定義変更、テーブル削除)が、ログの履歴として登録されていることが確認できました。

今日は以上まで

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

データベーストリガー(Oracle)

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

IMG_1978

桜の時期も終わってしまいました。ただ、今年は桜が咲いてから、少し寒かったので、桜がすぐには散らず、例年より長く、桜を楽しむことができたように思います。
東北や北海道などは、まだこれからがいよいよ本番ということなので、仕事で出張とかあればついでにお花見もできるのですが、そういう機会があるかな~。

<本日の題材>
データベーストリガー(Oracle)

データベーストリガーについて、まだブログで取り上げていなかったので、今回、取り上げてみたいと思います。
データベーストリガーとは、プロシージャやファンクションと同じように、データベースに格納されたPL/SQLのプログラムですが、プログラムなどから明示的にコールされて呼び出すのではなく、何らかのイベントをきっかけとして自動的に起動されるものです。

トリガーには、大きくは「DMLトリガー」「DDLトリガー」「その他」の3つの種類があります。
「DMLトリガー」は、特定の表に対してDML操作(INSERT, UPDATE, DELETEなど)が行われるときに起動するもの
「DDLトリガー」は、DDL操作(CREATE, ALTER, DROPなど)により起動するもの
「その他」については、
 ・データベースにログインやログオフするとき
 ・データベースの起動時や停止時
 ・エラーの発生時
などで起動するものがあります。
一般的によく使用される「DMLトリガー」についてですが、文トリガー、行トリガーという種類があり、さらに、処理の前後のどちらで起動させるかという、「BEFOREトリガー」「AFTERトリガー」という分類があります。

例)
今回は、ユーザー管理用の「ACCOUNTS」テーブルというものがあったとして、それに対しての、INSERT, UPDATE, DELETE が行われるタイミング(AFTER)で、処理のログを「ACCOUNTS_LOG」テーブルに登録するトリガーを作成してみます。

まず、処理のログを登録する「ACCOUNTS_LOG」テーブルを作成します。

CREATE TABLE accounts_log(
log_date DATE
,old_account_id INT
,new_account_id INT
,action VARCHAR2(50));

accounts_log

次に、トリガーを作成しますが、「ACCOUNTS」テーブルに対しての、INSERT, UPDATE, DELETE が行われてデータが更新されるたびに、「ACCOUNTS_LOG」テーブルに、処理日、変更前後の「ACCOUNT_ID」、そして処理内容を記録させます。
今回は、行トリガーのAFTERトリガーとして作成します。

CREATE OR REPLACE TRIGGER accounts_change_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON accounts FOR EACH ROW
DECLARE
  log_action  accounts_log.action%TYPE;
BEGIN
  IF INSERTING THEN
    log_action := 'Insert';
  ELSIF UPDATING THEN
    log_action := 'Update';
  ELSIF DELETING THEN
    log_action := 'Delete';
  ELSE
    DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
  END IF;

  INSERT INTO accounts_log (log_date, old_account_id, new_account_id, action)
    VALUES (SYSDATE, :OLD.account_id, :NEW.account_id, log_action);
END;
/

trigger

トリガーの中で、「FOR EACH ROW」を付けることで、行トリガーであることを示しています。そして、:OLD.列名、:NEW.列名とすることで、変更前と変更後の列名の値を取得することができます。
それでは、下記のように、「ACCOUNTS」テーブルへのデータの登録、修正、削除を順に行ってみます。

INSERT INTO accounts(account_id, name) VALUES(101, '山田 太郎');

INSERT INTO accounts(account_id, name) VALUES(102, '高橋 三郎');

UPDATE accounts SET NAME = '高橋 一郎' WHERE account_id = 102;

DELETE FROM accounts WHERE account_id = 101;

DML

それでは、「ACCOUNTS_LOG」テーブルを確認してみます。

「LOG_DATE」項目の表示を時刻まで出すようにフォーマットを変更します。
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';

データを確認します。
SELECT * FROM accounts_log
ORDER BY log_date;

select_accounts_log2

たしかに、「ACCOUNTS_LOG」テーブルへの更新履歴が登録されています。
ここで、INSERTの場合は、変更前はデータがないので、old_accounti_id は NULL であり、DELETEの場合は、変更後はデータがなくなるので、new_accounti_id は NULLになることがわかります。

また、COMMITをする前にROLLBACKをしてみると「ACCOUNTS_LOG」のデータはどうなるでしょうか?

ROLLBACK;
rollback

再度、「ACCOUNTS_LOG」テーブルを確認してみます。

select_accounts_log_sai

先ほど見えていたデータは、処理が取り消されたので、ログからも取り消されているのが確認できます。

今日は以上まで

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

データベースリンク(Oracle)

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

IMG_1878

だいぶ前になってしまいますが、お正月に、田舎に帰省したときに、飛行機から富士山が綺麗に見えたため、撮った写真です。飛行機の搭乗員の方も、これほど富士山が綺麗に見えるのは珍しいと言われていましたので、とても得をした気分になりましたね。
今年は縁起がいいかも!
いろいろと仕事が忙しかったため、前回の投稿からかなり時間が空いてしまいましたが、今年も頑張っていければと思います。よろしくお願いいたします。

<本日の題材>
データベースリンク(Oracle

システム開発の仕事をしていると、時折、複数のORACLEのサーバーがあり、それぞれに格納されているテーブルを同時にアクセスしてデータを抽出するなどの処理を行いたいという依頼を受けることがあります。
こういうときにORACLEデータベース側で、他のデータベースにアクセスできるデータベースリンクというものを作成することで、そういった操作が可能になります。
今回は、それを試してみたいと思います。

例)
今回は、Oracle12cのデータベースから、Oracle11gR2 のデータベースへのデータベースリンクを作成して、Oracle12c側からOracl11gR2側のデータを抽出できるようにしたいと思います。

設定の内容としては、大きくは以下になります。
①    ORACLEデータベースサーバー同士で接続できるように、
  ネットサービス名を設定します。
  今回は、Oracle12c側で設定を行います。
②    データベースリンクを作成するための権限を、該当のユーザ
  ーに与えます。
③ 該当のユーザーでデータベースリンクを作成します。

それでは始めて行きます。
①ネットサービス名の設定は、ツールでも可能ですが、今回はOracle12c側の「tnsnames.ora」ファイルを直接編集することで行います。

追加するネットサービス名を「ORCL11G」として、以下の内容を追加します。

ORCL11G =
(DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
   )
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = サービス名)
   )
 )

※「HOST=」の後には、Oracl11gR2サーバーのIPアドレス(ホスト名でもよい)を設定し、「PORT=」の後にはデフォルトのポート番号を記載しています。最後の「SERVICE_NAME =」の後には、Oracle11gR2データベースのサービス名を設定します。

②    データベースリンクを作成するための権限を、該当のユーザーに与えます。以前、「Oracle12cでのユーザー作成でエラー「ORA-65096」」という内容でブログで取り上げたときの内容を使って、Oracle12cの「BLOG_TEST」というユーザーに権限を与えます。

Sqlplusでsysdba でログインした後、「PDBORCL」というプラガブルデータベースに接続します。

ALTER SESSION SET CONTAINER = PDBORCL;

alter_session

 BLOG_TEST というユーザーでデータベースリンクを作成したいのですが、データベースリンク作成には権限が必要なので、このユーザーに権限を与えます。

GRANT CREATE DATABASE LINK TO BLOG_TEST;

cre_databaselink

それでは、「BLOG_TEST」ユーザーに接続して、データベースリンクを作成します。

CONNECT BLOG_TEST/passwd@PDBORCL
(passwdのところは、パスワード)
connect_2

Oracle11gR2のデータベースに対するデータベースリンク「ORCL11DB」を作成します。

CREATE DATABASE LINK ORCL11DB CONNECT TO BLOG_TEST IDENTIFIED BY passwd USING ‘ORCL11G’;

cre_databaselink_2
(IDENTIFIED BY の後には、Oracle11gR2側のBLOG_TESTユーザーのパスワードを設定します。)

 データベースリンクが作成されましたので、Oracle11gR2側の「CUSTOMER」テーブルのデータを抽出してみます。

SELECT * FROM CUSTOMER@ORCL11DB;

select_dblink

毎回、「CUSTOMER@ORCL11DB」のように書くのが面倒だという場合などには、シノニムを作成することもできます。

まず、シノニムを作成できる権限を、「BLOG_TEST」に与えます。一度、SYS ユーザーに戻ってから下記コマンドを実行します。

GRANT CREATE SYNONYM TO BLOG_TEST;
grant_cre_synonym

再度BLOG_TEST で接続して、シノニムを作成してみます。

CONNECT BLOG_TEST/passwd@PDBORCL
connect_2

CREATE SYNONYM OR11_CUSTOMER FOR CUSTOMER@ORCL11DB;

cre_synonym

それでは、シノニムを使って、データベースリンクを貼ったOracle11gR2側データベースの「CUSTOMER」データを抽出します。

SELECT * FROM OR11_CUSTOMER;
select_synonym

シノニムを使って、データベースリンクを貼ったテーブルのデータを抽出できることが確認されました。

ただし、注意点として、リモートのデータベースへ接続するため、ネットワークの状態や、データ件数やデータを取得するやり方により、ローカルのデータベース内のみでの操作に比べて、パフォーマンスが低下する場合があります。

今日は以上まで

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

計算列(SQL Server)

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

IMG_1826

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

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

<本日の題材>
計算列(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

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

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

insert_err

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

SELECT * FROM dbo.syain_health
ORDER BY id;

syain_select

計算列で指定した「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;

insert_300万

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

SELECT * FROM dbo.syain_health
ORDER BY id;

select_300

 件数を確認すると、

SELECT COUNT(*) FROM dbo.syain_health;

select_count

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

SET STATISTICS TIME ON

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

count_25_30

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

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

CREATE INDEX IX_syain_bmi ON dbo.syain(bmi);

cre_index

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

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

count_25_30

このときの時間を確認すると、
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万件作成して、先ほどと同様のことを行ってみます。

count_25_30_2

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

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

今日は以上まで

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

DELETEと領域の解放(ORACLE)

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

防災疑似体験

先日、新橋のほうに仕事で行った際に、近くのビルでVR(バーチャルリアリティ)映像を使った災害疑似体験ができる防災体験車に乗って体験する機会がありました。
昼休みくらいに、近くに来ているということでせっかくだからと行ってみたところ、東京消防庁が保有する防災体験車が止まっていて、8人くらいずつが乗って、ヘッドマウントディスプレイを被って、マンションにいるときに、震度7程度の地震が起きた場合の疑似体験ができました。
時間は3分ほどでしたが、上から物が落ちるは家具が倒れるは、ガラスは割れ、ドアは開かなくなるし、外の景色も大変なことに! いやあ~想像していたよりも迫力もあり、酔いそうな気分に! とても立っていられません。震度7だとこんなに凄いんですね。

地震大国である日本、いつそういう大きな地震に遭遇するか知れないので、とっさにどう動いたらいいか? なかなか想像がつかないですよね。一度は訓練として体験してみる価値があると思いました。

<本日の題材>
DELETEと領域の解放(ORACLE)

ORACLEを使用している場合に、使用している表領域のサイズがかなり大きくなってしまったので、データを削除して使用できる領域を増やそうと考える場合があると思います。しかし、DELETE文で削除してコミットしたのに、表領域のサイズが変わらないという状況に出くわして、困ってしまう場合があると思います。今回は、この内容について取り上げてみたいと思います。

例)
できるだけサイズが大きいテーブルで確認したいので、以前、ビットマップインデックスの題材のときに作成した、「T_CUSTOMER」という300万件のテーブルを使ってみます。

テーブルの定義は、
DESCRIBE T_CUSTOMER

describe

件数を確認すると
SELECT COUNT(*) FROM T_CUSTOMER;

件数確認

 件数は300万件。データの中身を一部確認してみると、
SELECT * FROM T_CUSTOMER
WHERE C_ID <= 30
ORDER BY C_ID;

 データ中身

C_ID列は、シーケンシャルに番号が増えていってます。
このテーブルが占めているサイズを確認すると、

SELECT SEGMENT_NAME, SEGMENT_TYPE, SUM(BYTES)/1024/1024 "MB"
FROM USER_SEGMENTS
WHERE SEGMENT_NAME LIKE '%T_CUSTOMER%'
GROUP BY SEGMENT_NAME, SEGMENT_TYPE
ORDER BY SEGMENT_TYPE DESC, SEGMENT_NAME;

テーブルサイズ確認

テーブル「T_CUSTOMER」が64MB、インデックス(主キー)「PK_T_CUSTOMER」が47MBであることが確認できます。

このテーブルから、200万件のデータを削除してみます。
DELETE FROM T_CUSTOMER
WHERE C_ID <= 2000000;

データDelete

この時点で、このテーブルの占めているサイズを再度確認してみます。

テーブルサイズ確認

確認したところ、サイズは全く変化がないことが確認できました。
この理由は、DELETE文では、ORACLEのハイウォーターマーク(HWM)に変化はなく、一度確保した領域は解放されないためです。ハイウォーターマーク(HWM)とは、テーブルなどに割り当てたブロックの中で、今までデータが挿入されたことがある最後尾のブロックのことで、その位置が変わらないと領域は解放されません。

このハイウォーターマーク(HWM)を低下させる方法としては、以下のような方法があります。
1.TRUNCATE でデータを削除する。
  ただし、データを全件削除することになります。
  (※最初に CREATE TABLE AS SELECT で元のテーブルを別テ
   ーブルにコピーしておけば、TRUNCATE後にデータを流し
   込むことはできます)
2.Export/Importでデータを復元する。
  Exportした後に、一度テーブルをDROPして削除してから
  Importします。
3.ALTER TABLE MOVE でテーブルを新しいセグメントに移動し
  て再作成する。
4.ALTER TABLE SHRINK SPACE による断片化の解消。

それでは、1.のTRUNCATE文でデータを削除してみます。
その前に、データを戻せるように別テーブルにコピーします。

CREATE TABLE COPY_T_CUSTOMER NOLOGGING
AS SELECT * FROM T_CUSTOMER;

Create_table_as_select

TRUNCATE文でデータを削除します。
TRUNCATE TABLE T_CUSTOMER;

truncate

処理時間もDELETE文に比べたら、全然速いです。

この時点で再度、テーブルのサイズを確認してみます。

truncate後のテーブルサイズ

TRUNCATEしたら、テーブル、インデックスとも、0.625MB ということで、領域が大きく解放されたことが確認できます。また、最初にデータをコピーして作成した COPY_T_CUSTOMER テーブルも、22MBということで、元の64MBよりは小さく作成されることもわかります。

この後、コピーしたデータを戻してCOMMITし、コピーした不要なテーブルを削除します。
INSERT INTO T_CUSTOMER SELECT * FROM COPY_T_CUSTOMER;
COMMIT;
DROP TABLE COPY_T_CUSTOMER;

データinsert

再度、テーブルのサイズを確認してみます。

insert後のテーブルサイズ

確かに、T_CUSTOMERのテーブルのサイズは、22MB、インデックスも30MBとなりました。

次に、2.のExport/Importを試してみます。

再度、データを300万件作成します。これは、以前、バルク処理の題材のときの方法で作成します。

300万件登録

再度、テーブルのサイズを確認してみます。

テーブルサイズ確認

このテーブルから、再度200万件のデータを削除します。

200万件削除

この後、Export/Importによってテーブルを作成しなおします。

expdp blog_test/パスワード directory=DP_DIR tables=t_customer dumpfile=t_customer.dmp
(※処理を行う前にディレクトリの作成とディレクトリへのread/write権限の付与が必要)

expdp

 表を削除します。
DROP TABLE T_CUSTOMER;

Drop_table

先ほどエクスポートしたデータをインポートします。

impdp blog_test/パスワード directory=DP_DIR dumpfile=t_customer.dmp

impdp

ここで、テーブルのサイズを確認してみます。

import後のテーブルサイズ

T_CUSTOMERのテーブルのサイズは、22MB、インデックスも18MBとなり、領域が解放されたことが確認できました。

次に、3.のテーブルを新しいセグメントに複製し、既存のものを削除するという方法(テーブルの移動)による再作成を行ってみます。

まずは、再度300万件のデータに戻し、その後、200万件のデータを削除します。(動作については省略)
このときのテーブル・インデックスのサイズは、テーブルが64MB、インデックスが47MB。

ALTER TABLE T_CUSTOMER MOVE;

alter_table_move

再度、テーブルのサイズを確認してみます。

alter_move後のテーブルサイズ

テーブルのサイズは、テーブルが64MBから22MBに小さくなっていることが確認できました。
LONG / LONG RAW のある表は、この方法での移動はできないとのこと。

最後に、4.のALTER TABLE SHRINK SPACE による断片化の解消を試してみたいと思います。
こちらもまずは、再度300万件のデータに戻し、その後、200万件のデータを削除します。(動作については省略)
このときのテーブル・インデックスのサイズは、テーブルが64MB、インデックスが47MB。

断片化の解消をしたいテーブル「T_CUSTOMER」に対して、以下のSQLを順に実行します。

ALTER TABLE T_CUSTOMER ENABLE ROW MOVEMENT;
ALTER TABLE T_CUSTOMER SHRINK SPACE CASCADE;
ALTER TABLE T_CUSTOMER DISABLE ROW MOVEMENT;

alter_table_shrink

2番目のSHRINK SPACE CASCADE の処理に多少時間がかかりました。
ここで、テーブルのサイズを確認してみます。

alter_shrink後のテーブルサイズ

T_CUSTOMERのテーブルのサイズは、21.625MB、インデックスも16MBとなり、領域が解放されたことが確認できました。
なお、レコード数が少ない場合においては、ハイウォーターマークが思ったより低下しない場合もあり、これは、行移行や行連鎖の状態によって効果は異なるようです。連鎖状態を解消してからであれば、効果は高いということです。

今日は以上まで

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

インデックス再構築・再構成(SQL Server)

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

IMG_1531

先日、妻の実家のある富山のほうに行く機会があり、そこできれいな夕焼けが見れたので、撮った写真です。車に乗っていた娘が、きれいだよ!というので、慌てて車の中から撮ったものです。
そのときも、本当に昼間は暑い日が続いていて、これは大変だと思っていましたが、その後もさらに暑さが増して、今年は本当に危険な暑さになってますよね。埼玉の熊谷では、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

断片化1

ここでの、主な項目の意味としては、
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

インデックス再構築_v8_1

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

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;

インデックス再構築後_v8_2

上記の結果から、断片化の割合は 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;

インデックス再構成前_3

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

ALTER INDEX PK_T_USER_LOG_V6 ON dbo.T_USER_LOG_V6 REORGANIZE;

インデックス再構成後_4

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

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

インデックス再構成後_5

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

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

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

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

今日は以上まで

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

動的管理ビュー(SQL Server)

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

IMG_1498

いよいよ梅雨の季節が近づいてきました。上の写真は、少し前に娘がスマホで撮って送ってくれたきれいな夕方の空です。少し神秘的ですね。梅雨になるとこういう空もなかなか見れなくなっちゃうな。
さて、最近のスポーツでは、サッカーのワールドカップがいよいよ始まります。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;

avg_cpu_cost

上記は、「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を元に実行プランを抽出することも可能です。)

querr_plan

参考: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;

io_top10

 こちらは、「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技術ブログへ
にほんブログ村

APPLY(SQL Server)

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

IMG_1412

自宅から会社に行く途中に道路のそばに咲いているポピーの花です。自転車で通るので、きれいに咲いているときには少し止まって眺めたりするこの頃ですが、若いときは、頭の中は仕事のことか、差し迫ったやるべきことなどでいっぱい(もしくはぼーっとしている?)で、見ていても記憶に残らないという感じだったと思います。妻にあそこにきれいな花が咲いているでしょう?と聞かれても、そうだっけ?という返事をするので、そういう感覚が欠落しているかわいそうな人だとあきれられていたものですが、少しずつ花などの自然も意識するようになってきました。

ちなみに、ポピーは色とりどりの花を咲かせるケシ科の植物の総称で、その実から採れる乳液には、入眠や麻痺の作用があることから、紀元前400年頃のギリシアでは麻酔薬や睡眠導入薬として用いられていたそうです。花言葉も「なぐさめ」「心の平静」「いたわり」「思いやり」などその効能にちなんだものが多くあり、相手をいたわる気持ちや、助けたい気持ちを表現するときによく贈られる花だそうです。

<本日の題材>
APPLY(SQL Server)

今回は、APPLYという演算子について取り上げてみます。
以前、テーブル値関数について記事として載せたことがありますが、あるテーブルのデータの値と、その値によって取得されるテーブル値関数の結果を組み合わせて抽出したいという場合に、APPLY という演算子を使用することができるようです。(SQL Server 2005から)
SQLのFROM句の後に、まずテーブルを記載し、「CROSS APLLY」、もしくは「OUTER APPLY」の後側にテーブル値関数を指定するかたちになり、SELECT のほうではテーブルの列とテーブル値関数の結果の列を含めるかたちになります。
私も今まであまり使ったことがなかったのですが、今回、試してみようと思います。

例)
使用するテーブルは、以前ブログで使ったことのある、日本の都道府県、山、川、湖などの大きさや高さや長さなどをデータにしたランキングのデータを使ってみたいと思います。
今回使用するテーブル(「ランキング」「ランキング区分」)の定義は、下記のような設定。

テーブル定義

データを抽出してみると、
「ランキング区分」テーブルは、

ランキング区分データ

「ランキング」テーブルは、

ランキングデータ

今回使用するテーブル値関数は、「区分NO」の値からランキングテーブルのデータを抽出する簡単なものです。定義は以下:

CREATE FUNCTION [dbo].[ufn_ランク上位取得]
(
         @p区分NO DECIMAL(4,0)
) RETURNS @ランク上位 TABLE (
         区分名 VARCHAR(40),
         ランキング DECIMAL(3,0),
         名称 VARCHAR(40),
         数値 DECIMAL(8,1)
)
AS
BEGIN
         INSERT @ランク上位
         SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC)
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         JOIN dbo.ランキング区分 K ON R.区分NO = K.区分NO
         WHERE K.区分NO = @p区分NO;
 
        RETURN;
END;

このテーブル値関数にパラメータを直接指定して抽出すると、

SELECT * FROM dbo.ufn_ランク上位取得(1)
ORDER BY ランキング;

テーブル値関数抽出

これを、「ランキング区分」テーブルの「区分NO」をテーブル値関数にセットして、「区分NO」毎にランキング情報を抽出するように、APPLY句を使用すると、以下のように抽出できます。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

CROSSAPPLY

ここで、「ランキング区分」テーブルにあっても、テーブル値関数で結果のないものは抽出されないのですが、外部結合のように、上記のようなデータも左側のランキング区分の情報は表示し、テーブル値関数側の結果は null で表示したい場合には、「OUTER APPLY」演算子を使用します。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  OUTER APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

OUTERAPPLY

 上記のように、「区分NO」=2, 3 のテーブル値関数では抽出されないデータも表示されることが確認できます。

ちなみに、APPLY演算子の右側は必ずテーブル値関数である必要はなく、上記の内容は、以下のようなSQLでも抽出できます。(CROSS APPLYの例)

SELECT K.区分NO, K.区分名, K.単位, RK.ランキング, RK.名称, RK.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY
   (SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC) AS ランキング
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         WHERE R.区分NO = K.区分NO) AS RK
ORDER BY K.区分NO, RK.ランキング;

CROSSAPPLY2

今まではあまり使ったことはなかったのですが、今後は、使えるときもあるのではないかと思います。

今日は以上まで

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

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