ITC シュウちゃん のすべての投稿

実行計画の取得(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技術ブログへ
にほんブログ村

デッドロックその2(SQL Server)

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

IMG_0645

これは、ちょっと前に咲いた早咲きの桜の写真です。家の近くの川べりに咲いた桜がきれいでした。

さて、年度が変わって子供たちも春休みの後半を迎え、もうすぐ新しい学年として出発します。中学生の息子が数学の問題集のわからないところについて、聞いてきたのですが、なかなかスパッと解き方がひらめかず、かっこよく教えてあげることができませんでした。ん~! 俺って結構年を取ってきたかな~。頭が固くなってきたみたい。とほほほ。。。

話しは変わりますが、dbSheetClientにまたまた新しい事例がアップされました。
全社、約800名が利用する、念願の「 予算申請(損益予算・設備投資予算)システム 」をdbSheetClientで構築!予算責任者及び担当者、財務部の作業負担が大幅に削減!という、JR九州様の事例第2弾です。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice_sequel/jr_kyushu2.html

また、dbSheetClient(ディービーシート・クライアント)の導入メリットや導入の様子を、マンガを使って解りやすく解説したページができていますので、こちらも是非ご参照ください。
http://www.newcom07.jp/dbsheetclient/intromanga/index.html

<本日の題材>
デッドロックその2(SQL Server)

前回、SQL Serverでのデッドロックの現象について、実際に簡単なSQLで発生することを確認しましたが、実際にデッドロックが発生した場合には、その情報を調査して対応しなければなりません。今回は、デッドロック発生時の情報の取得のやり方について見てみたいと思います。

SQL Server 2012以降では、拡張イベントという機能でデッドロックの情報も確認することができます。(それ以前は、トレースフラグ:1204と1222を設定して確認していました)

SQL Server Management Studioのオブジェクトエクスプローラーの「管理」→「拡張イベント」→「セッション」→「system_health」の下に、「package0.event_file」というものがあり、それをクリックすると下記のような表示が出ます。

system_health2

ここで、メニューの「拡張イベント」→「フィルター」から

system_health_menu

フィールドに「name」、演算子に「=」、値に「xml_deadlock_report」を設定して「OK」を押下すると、拡張イベントがデッドロックのイベントに絞られて表示されます。

system_health_deadlock

このイベントにカーソルを合わせて、下側のウィンドウの「デッドロック」のタブをクリックすると、下記のように図でデッドロックの情報が確認できます。

デッドロック情報

 図の×がついているプロセスの上にカーソルを置くと、デッドロックが発生したSQLが確認できます。

デッドロック情報sql

また、「詳細」タブのほうで、その行をダブルクリックすると、デッドロックのより詳細な情報を確認できます。

デッドロック詳細

ここでもデッドロックになったSQLが確認できますが、<deadlock> タグの中の、<process-list>タグのほうに
waitresource="KEY: 46:72057594039762944 (a0c936a3c965)"
というものがあり、実際のどのデータを処理しようとしたときにデッドロックになったかがわかるかたちになっています。

実際のデータを確認するのは、上記のKEY情報を使って以下のように抽出します。

SELECT *
FROM tbl_B(NOLOCK)
where %%lockres%% = '(a0c936a3c965)';

デッドロックデータ確認

上記から、tbl_B テーブルの、CD_B = 4 のデータの UPDATE のときにデッドロックが起きたことがわかります。

今日は以上まで

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

デッドロック(SQL Server)

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

IMG_0641

お世話になった知人の方が、仕事の都合もあって実家のある愛媛県のほうに引っ越しされるということで、瀬戸大橋を電車で移動されているときに撮られた写真を頂きました。まだ瀬戸大橋を渡ったことはないのですが、是非一度行ってみたいですね。
そういえば、瀬戸大橋を渡る瀬戸大橋線に乗っているときに、瀬戸の花嫁の曲が流れるみたいですね。「瀬戸は日暮れて~!」瀬戸に来たという実感がわくみたいです。

さて、dbSheetClientに新しい事例がアップされました。
成約引合管理、予算収集、ワークフロー、文書管理、業績見える化まで、様々な業務効率化システムを4年間で80以上構築稼働させたナラサキ産業様の事例です。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/narasakisangyo.html

<本日の題材>
デッドロック(SQL Server

なかなか普段はそんなにはお目にかからないデッドロックの現象ですが、複数のクライアントが同時に同じテーブルにアクセスする環境では、アプリの作り方によっては簡単に起きてしまうこともあります。
一方のタスクがロックを試みているリソースに対して他方のタスクがロックを獲得し、これが相互に行われるとデッドロックが発生するというのが一般的な現象になると思います。本日は、複数のテーブルに対して更新する処理を、処理の順番が逆になるかたちで2つのプロセスが行うかたちでのデッドロック現象を確認してみたいと思います。ちなみに、同じテーブルの異なる行に対する処理でも同様に発生します。

 例)
2つのテーブル(tbl_A、tbl_B)があり、片方の処理は、トランザクション処理の中で、tbl_Aのレコード(CD_A=4)を更新した後に、tbl_Bのレコード(CD_B=4)を更新しますが、同時に、もう一つの処理が tbl_Bのレコード(CD_B=4)を更新した後に、tbl_A(CD_A=4)の値を更新します。
なお、タイミングが重なるように、処理の途中で以前ブログでも取り上げましたスリープ処理(WAITFOR DELAY)を入れてみます。

処理A):
BEGIN TRANSACTION
 UPDATE dbo.tbl_A SET
   amount = 2100
  WHERE CD_A = 4;
 
 WAITFOR DELAY '00:00:10'
 
 UPDATE dbo.tbl_B SET
   amount = 3000
  WHERE CD_B = 4;
COMMIT TRANSACTION

処理B):
BEGIN TRANSACTION
 UPDATE dbo.tbl_B SET
   amount = 1200
  WHERE CD_B = 4;

 WAITFOR DELAY '00:00:3'

 UPDATE dbo.tbl_A SET
   amount = 1500
  WHERE CD_A = 4;
COMMIT TRANSACTION

処理Aを実行した直後に、処理Bを実行します。すると、下記のようなかたちで処理が進み、デッドロックが発生します。

デッドロック図示

実際に、SQL Serverで実行してみます。
SQL Server Maangement Studioの1つのウィンドウで処理Aを実行し、すぐに別のウィンドウで処理Bを実行します。

処理A
デッドロックエラー

処理B
デッドロック処理B

 すると、処理Aでは、上記の結果のように、下記のようなデッドロックのエラーが表示されます。

「メッセージ 1205、レベル 13、状態 51、行 9
トランザクション (プロセス ID 52) が、ロック個のリソースで他のプロセスとデッドロックして、このトランザクションがそのデッドロックの対象となりました。トランザクションを再実行してください。」

それでは、次に、デッドロックが発生したときの対応として、エラーコード:1205を取得したら処理をリトライするという仕組みを、BEGIN CATCH~END CATCHの中に組み込んでみます。

処理A)
RETRY:
BEGIN TRANSACTION
BEGIN TRY
 UPDATE dbo.tbl_A SET
   amount = 2100
  WHERE CD_A = 4;
 WAITFOR DELAY '00:00:10'
 UPDATE dbo.tbl_B SET
   amount = 3000
  WHERE CD_B = 4;
 COMMIT TRANSACTION
END TRY

BEGIN CATCH
 PRINT 'ロールバックします'
 ROLLBACK TRANSACTION
 IF ERROR_NUMBER() = 1205 -- デッドロックエラーコード
 BEGIN
   WAITFOR DELAY '00:00:05'
   GOTO RETRY
 END
END CATCH

処理B)
RETRY:
BEGIN TRANSACTION
BEGIN TRY
 UPDATE dbo.tbl_B SET
   amount = 1200
  WHERE CD_B = 4;
 WAITFOR DELAY '00:00:3'
 UPDATE dbo.tbl_A SET
   amount = 1500
  WHERE CD_A = 4;
 COMMIT TRANSACTION
END TRY

BEGIN CATCH
 PRINT 'ロールバックします'
 ROLLBACK TRANSACTION
 IF ERROR_NUMBER() = 1205 -- デッドロックエラーコード
 BEGIN
   WAITFOR DELAY '00:00:05'
   GOTO RETRY
 END
END CATCH

 SQL Serverで実際に実行してみます。

処理A)

デッドロック処理A_retry

 処理B)

デッドロック処理B_retry

処理Aのほうが、一旦はデッドロックのエラーが発生しましたが、5秒後に再度リトライを実施して、最終的には更新が終了している様子が確認できます。

処理後の、tbl_A、tbl_B のデータを確認してみると

tbl_Aは

tbl_A結果

 tbl_Bは

tbl_B結果

 上記から、最後は処理Aの更新処理がされていることが確認できました。

さて、実際には、デッドロックは上記のようなケースだけではなく、ロックのエスカレーションによるブロッキングが原因で発生する場合などもあります。これについては、また機会があるときに取りあげてみたいと思います。

今日は以上まで

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

PIVOT、UNPIVOT(SQL Server)

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

夏みかん

知人の方から、家に夏みかんの木があって、たくさん実がなるということで頂きました。その写真です。

さて、米国では、トランプ大統領が就任しましたが、就任直後から大統領令を連発し、大きな話題になるとともに、中にはきわめて大きな反発も起こっていますね。オバマケア見直し、TPPの離脱をはじめ、選挙のときから掲げてきた内容を具体的に実施しているわけですが、大きな波紋を呼んだものとしては、メキシコ国境の壁の建設、そして、中東・アフリカ7カ国からの渡航者の入国禁止(シリアからの難民受け入れの拒否も含む)です。
特に入国禁止については、米国政府内でも批判的な声が上がり、反対するデモも各地で起きていて、かなり混乱した状況ですね。この大統領令は憲法に違反しているということで、差し止めを命じるワシントン州の連邦地裁も現れました。強力な力を持つ大統領令ですが、司法のほうで歯止めをすることが可能だということが今回の内容で確認できました。さすが、民主主義の国!

しかし、おっかない世の中になってきました。これからどうなるのやら? できれば平和裏に進んでほしい。僕もやっぱり平和が好きですから。でもよく考えてみると、うちのかみさんもおっかなかったっけなあ?

 <本日の題材>
PIVOT、UNPIVOT (SQL Server)

以前、複数行のデータを集計して横展開という記事をアップしたことがありましたが、今回はこれと同様のことを、SQL ServerのPIVOTという関係演算子を使って試してみたいと思います。

前回の記事では、部品別の日別の仕入数量のデータが、部品コード、年月日、数量というようなレイアウトでDBに登録されている場合に、部品別に、月を横に並べて仕入数量の月別合計値を表示させるようなケースについて、case文を使いながら各月を横に並べて表示させるようにしていました。
このテーブルのデータを単純に抽出すると以下のようになります。

SELECT * FROM dbo.部品発注表;

blog75_部品発注表

部品別に、月を横に並べて仕入数量の月別合計値を表示させることを、pivot演算子を使って試してみます。

SELECT *
FROM (select 部品コード, substring(年月日,5,2)+'月' as '月', 数量
       from [dbo].[部品発注表]) as B_tab
PIVOT (
   SUM(数量) FOR 月 IN
  ([01月], [02月], [03月], [04月], [05月], [06月], [07月], [08月], [09月], [10月], [11月], [12月])
) as PVTab
ORDER BY PVTab.部品コード;

 blog75_pivot

 上記のSQLでは、FROM の後の集計対象テーブルの後に、PIVOT句を設定し、FOR .. IN で設定した値を列として出力して、それ毎にSUM関数で設定した項目[数量]を集計しています。なお、SUM関数とFOR .. IN で指定されていない「部品コード」でGROUP BYがされていて、それぞれの行が出力されています。最後のORDER BY句はオプションですが、並び順を設定できます。
Excelのピボットと同様のことが、SQLでできるのが確認できました。

また、以前別の記事「横に並んだ項目を縦の行データに変換」で、最初から列として登録されているデータを、複数行の縦のデータに変換して抽出するという件を取り上げましたが、UNPIVOT という関数がこれに該当します。

前回のデータをそのまま利用する場合に、まず、「商品売上」テーブルのデータをそのまま抽出すると、

SELECT * FROM dbo. 商品売上;

blog75_商品売上

 上記データについて、各月ごとのデータを行として表示する場合に、UNPIVOT演算子を使ったSQLは以下のようになります。

SELECT 商品CD, 月, 月売上
FROM
  (SELECT * FROM dbo.商品売上) p
UNPIVOT
  (月売上 FOR 月 IN
     (売上4月, 売上5月, 売上6月, 売上7月, 売上8月, 売上9月, 売上10月, 売上11月, 売上12月, 売上1月, 売上2月, 売上3月)
)AS unpvt
ORDER BY unpvt.商品CD desc;

blog75_unpivot

 上記のSQLでは、FROM の後の対象テーブル(最初から横に並んだ項目を持つ)の後に、UNPIVOT句を設定し、FOR .. IN で設定した値を行として出力して、それ毎に月の売上を抽出しています。
また、FOR .. IN で指定されていない「商品CD」でGROUP BYがされていて、商品CD毎の、各月毎の売上が出力されています。

今日は以上まで

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

Oracle12cでのユーザー作成でエラー「ORA-65096」

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

IMG_0585

新年あけましておめでとうございます。
ごあいさつがとても遅くなってしまいました。本年もよろしくお願いいたします。

昨年2016年は、IOTとともにAI(人工知能)がとてもクローズアップされるようになってきた1年ではなかったかと思います。Googleの「AlphaGo」は囲碁で韓国のトッププロ棋士を破り、話題になりましたね。囲碁は打ち手のパターンが10の360乗通り、あるいは400乗通り存在するといわれており、AIがプロ棋士に匹敵する強さになるにはあと10年かかるといわれていたのを、10年前倒しで達成したということです。また、IBMのワトソンは患者の正確な白血病の病名を10分で見抜き、割り出した病名に対する適切な治療によって患者の命を救ったと報道され、AIが医療の世界で大きく貢献する事例となりました。また、自動運転、ディープラーニングなど、様々な技術へのチャレンジ、大きな投資がなされています。
そして、20年後には現在日常で行われている仕事のほどんどをロボットが行う時代が来ると断言する有名なCEOもいれば、科学者や有識者の間では、2045年には人工知能が人間の知能を超えるという予測「シンギュラリティ」についての議論もあり、人工知能は人類史上最悪の脅威と主張する英ノーベル賞授賞物理学者・ホーキング博士のような学者もいます。
いよいよ、過去SFでしか想像できなかったような世界が具体的にやってくると予感させる時代になってきたと感じますが、この科学を正しく使いこなせるかという点が、昔から言われてきたことではありますが、今後の人類にとってのさらに重要な課題、焦点になってくるのではないでしょうか?
将来は、仕事や技術関連は、ロボットが受け持ち、人は、心情、文化、芸術などで楽しむ世界になるかも知れませんね!

<本日の題材>
Oracle12cでのユーザー作成でエラー「ORA-65096」

Oracle12cが国内で提供開始されたのは、2013年7月、既に3年半近くが経過しました。クラウドの『c』を製品名に掲げ、コンテナ、プラガブル、マルチテナントなど、これまでのOracle Databaseにはなかった多くのコンセプトを引っさげ、実に500を超える新機能が実装された12cですが、なかなか開発で12cに触れる機会が少なく、残念に思っていたところ、少し触れる機会がありましたので、今回はそれについて取り上げてみたいと思います。

まず、最初に行うことの一つがユーザの作成だと思いますが、ここで、11gまでと同じようにユーザを作成しようとすると、「ORA-65096: 共通ユーザーまたはロール名が無効です」のエラーが発生します。

まず、SQL*Plusを立ち上げ、sysユーザでログインします。

sqlplus / as sysdba

oracle12cログイン

このあと、「BLOG_TEST」というユーザを作成してみます。

create user blog_test
identified by passwd
default tablespace users
temporary tablespace temp
quota unlimited on users;

create_userエラー

すると、上記のように、「ORA-65096: 共通ユーザーまたはロール名が無効です」のエラーが発生します。
11gまでしか知らないユーザでは、ここでまずびっくりしてしまいますが、このエラーに対応するためには、Oracle12c で大幅に変更された、マルチテナント・アーキテクチャという新しいアーキテクチャの仕組みを理解する必要があります。

Oracle11gまでは、1つのインスタンスには1つのデータベースしかありませんでしたが、12cからは1つのインスタンスには1つのコンテナ・データベース(CDB)と、その下にプラガブル・データベース(PDB)と呼ばれる子DB(複数作成可能)が存在するかたちになります。
このマルチテナントのメリット、魅力については、既にいろいろな資料がWEB上などで出ていますが、例えば、OTNの「Oracle Database 12c マルチテナントの魅力」というHPでは大きく5つの内容が上がっています。

1.サーバー最適化の向上
2.多数のデータベースを一元管理
3.多数のデータベースをまとめてバックアップ
4.データベースの迅速なプロビジョニング
5.データベースの迅速な移動
詳細はそちらのHPのほうを確認してみてください。

さて、ユーザー作成のエラーの対応の仕方に戻ります。11gまでと同様にOracleに接続した場合は、コンテナ・データベース(CDB)に接続されるのですが、CDBではユーザスキーマ(ローカルユーザー)を作成することができません。上記のような ORA-65096 のエラーになります。ローカルユーザーは、実際にデータを管理するプラガブル・データベース(PDB)に接続して作成する必要があります。

※ローカル・ユーザーとは、特定のPDBのみに存在するユーザーのことで、共通ユーザーとは、各コンテナ(ルートと各PDB)に同名のユーザーが存在する場合のことで、ユーザー名に接頭辞(C##)が必要になります。

それでは、現在接続しているデータベースを確認します。
SQL*Plus上で、show con_name を実行します。

12c_show_con_name

上記のように、「CDB$ROOT」と表示されたら、コンテナ・データベース(CDB)に接続していることがわかります。プラガブル・データベース(PDB)に接続し直すために、まず、PDBの名前と現在のOPEN_MODEを確認します。

select name, open_mode from v$pdbs;

12c_pdb

今回接続したいPDBは「PDBORCL」で、OPEN_MODEは「READ WRITE」となっていて、起動できていることが確認されましたので、PDBORCLに接続し直します。

alter session set container = pdborcl;

12c_pdb_session

再度、現在接続しているデータベースを確認します。

12c_show_con_name2

プラガブル・データベースのほうに接続していることが確認できました。
もしも、PDBのOPEN_MODEが「MOUNTED」の状態であれば、まだPDBが起動していない状態のため、以下のようにしてPDBを起動する必要があります。
alter pluggable database pdborcl open;

また、停止する場合は、以下のようにします。
alter pluggable database pdborcl close immediate;

それでは、PDBに接続した状態でユーザーを作成してみます。

create user blog_test
identified by passwd
default tablespace users
temporary tablespace temp
quota unlimited on users;

12c_cre_user

次に、connect, resource のロールを付与します。

grant connect, resource to blog_test;

12c_roll

それでは、作成したユーザ「blog_test」でログインしてみます。

sqlplus blog_test/passwd@pdborcl

12c_pdb_login

作成したユーザーでログインできました。
※ただし、PDBには、たとえサーバー内からの接続であっても、上記のように @ネット・サービス名 をつけて、必ずリスナー経由でログインする必要があるようです。

今日は以上まで

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

varchar(max)、nvarchar(max) (SQL Server)

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

%e5%ae%9f%e4%bd%b3img_0565

2番目の娘が、土浦市の霞ケ浦総合公園で行われているイルミネーションを見てきたということで送ってきてくれた写真です。水郷桜イルミネーションと言うんですね。高さ25m・羽根直径20mのオランダ型風車のイルミネーションを中心に,市の花である『桜』や『土浦の花火』『霞ケ浦』『帆引き船』『ハス田』など土浦市の資源をモチーフにしたイルミネーションだそうです。私も行ってみたいですね。

さて、年末も間近に迫ってきました。ブログをアップしたいと思いつつも、日々の仕事に追われて、ヘトヘトです。そう言えば、先日まで日本でFIFAクラブワールドカップが行われ、開催国代表の鹿島アントラーズが、何と準優勝!それも、あのクリスティアーノ・ロナウド擁するレアル・マドリードを一時は逆転して追い込む場面もあり、最後は延長でのロナウドの2得点というさすがと思わせるゴールも見られた、とても面白い試合だったと思います。日本のJリーグのチームがあそこまでやるとはと、とても興奮しましたね。今後の日本のサッカー界にとても希望を持たせる活躍だったと思います。

話しは変わりますが、dbSheetClientのユーザー事例に、株式会社みうら様の事例が載りました。
Excelのインターフェースを活かした見積作成、及び納期・工程進捗管理システムを短期間で構築、内製化を実現し、ERPとの連携を効率化した事例ということで、お客様もとても喜ばれているようです。ご興味ある方は、是非こちらをご参照ください!
http://www.newcom07.jp/dbsheetclient/usrvoice/miura_system.html

なお、今回が今年最後のブログになりそうです。
1年間どうもありがとうございました。_m(. .)m_  来年も、よろしくお願いいたします。

<本日の題材>
varchar(max)、nvarchar(max) (SQL Server)

何回か前に、テーブルの項目数、レコード長の制限という内容で、SQL Serverの1レコードの制限が8060バイトという内容についてブログで取り上げましたが、SQL Server 2005から、大きな値のデータ型とよばれる、varchar(max)、nvarchar(max)型というものが機能として増えていますので、今回はこれを取り上げてみたいと思います。(他に、varbinary(max)型というバイナリデータを扱うものもあります)

システムの開発を行っていると、ときには、文字の桁数をどれくらいにしていいかを決めかねる場合があります。普通はそれほど多く入力しないけれども、ときにはいろいろな情報や説明を書きこむ必要があって、とても大きな桁数の入力をする可能性があるケース、そして、それも1レコード上に複数そのような項目が発生してしまう可能性があるケースなどもときに遭遇することもあります。その場合に、テーブルをうまく分割して対応するなどもありますが、いろいろと処理が複雑になってしまったりで悩んでしまうこともあると思います。

そんなときに、varchar(max)、nvarchar(max) を使うと、1項目の最大バイト数が varchar(max)では2^31-1、nvarchar(max)では文字数が最大2^30-1 というとてつもない大きさまで持つことができ、当然1レコードもその分は確保されるということになるため、テーブルの分割なども気にせずに対応することができることになります。

ちなみに、以前から text型、ntext型というサイズの大きなデータ型は用意されていましたが、whereの条件で = を使用することができないなどの制約がありました。将来のバージョンでは削除される予定ですので、これらを使うのではなく、varchar(max)、nvarchar(max)を使うように推奨されていますね。

今回は、それを試してみます。
例)

create table dbo.test_big(
a varchar(10),
b varchar(max),
c nvarchar(max),
d text,
e ntext
constraint pk_test_big primary key (a));

insert into dbo.test_big values('001','abcde',N'abcde','abcde',N'abcde');
insert into dbo.test_big values('002','fgabc',N'fgabc','fgabc',N'fgabc');
insert into dbo.test_big values('003','jklab',N'jklab','jklab',N'jklab');

blog73_var_max_cre

このときの、各項目のバイト数を確認してみると、

select
a, datalength(b) b_byte, datalength(c) c_byte, datalength(d) d_byte, datalength(e) e_byte
from dbo.test_big
order by a;

blog73_datalength

 nvarchar(max)、ntext型は、5文字で10バイトを取っていて、varchar(max)、text型の2倍のサイズになっていることが確認できます。頭に n がつく nvarcharやncharなどは、unicode文字列をサポートするデータ型であり、2バイトを使用して1つの文字をエンコードする仕組みのため、文字数の2倍のバイト数が取られることになります。

それでは、varchar(max)、nvarchar(max)型は、whereの条件に「=」を使えますが、text型、ntext型では使用できないことを確認してみます。

select * from dbo.test_big
where b = 'abcde'
 and c = N'abcde';

blog73_max_equal

select * from dbo.test_big
where d = 'abcde';

blog73_text_equal

select * from dbo.test_big
where e = N'abcde';

blog73_ntext_equal

上記から、varchar(max)型、nvarchar(max)型は、通常の varchar, nvarchar 型と同様に、whereに「=」の条件を設定して使用できるけれども、text、ntext型では、whereに「=」演算子は使用できないことが確認できました。
ちなみに、like 演算子は使用できます。

select * from dbo.test_big
where d like 'abc%'
  or e like N'abc%';

blog73_text_like

次は、nvarchar(max)型を1つのテーブルに複数持ち、それぞれに8000文字(100文字ずつを改行して80行分)のデータを登録してみます。

create table dbo.test_big2(
a varchar(10),
b nvarchar(max),
c nvarchar(max),
d nvarchar(max),
e nvarchar(max),
constraint PK_test_big2 Primary key (a));

insert into dbo.test_big2(a,b,c,d,e) values(
'123',
N'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
……. (後ろは省略)

blog73_insert

※画面は insert文の最初の部分のみ

すると、各項目のバイト数を抽出してみると、

select
a, datalength(b) b_byte, datalength(c) c_byte, datalength(d) d_byte, datalength(e) e_byte
from dbo.test_big2;

blog73_max_%e3%81%9f%e3%81%8f%e3%81%95%e3%82%93%e3%83%87%e3%83%bc%e3%82%bf

 これは、8000文字分に加えて、改行コードはCHAR(13) - CR: キャリッジリターンと、CHAR(10) - LF: ラインフィードの組合せで合わせて2バイトになるので、8000+80*2=8160バイトのところ、nvarcharはvarcharの2倍のサイズを取るため、8160*2=16,320バイトとなっています。

上記から、1レコードの合計のレコード長も、65,283バイトとなり、以前紹介した8,060バイトの壁も問題なく超えて、長いサイズのレコードが登録できることが確認できました。

今日は以上まで

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

FileTable(SQL Server)

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

img_0511

先日、会社の親睦会でさいたま市のさぎ山記念公園というところに行ったときに撮った写真です。

仕事が結構忙しくなってせわしない状況ですが、世界の情勢もいろいろと変化が激しいことを感じるこの頃ですね。アメリカでは、大統領選で、当初の予想を覆してトランプ大統領が次期大統領に決定し、TPPを離脱するというメッセージを出しましたし、お隣の韓国では、朴大統領のスキャンダルによる弾劾手続きが始まろうとしている状況で、朴大統領自身も任期満了前に辞任する意向を示しました。日本にも影響があると思われる出来事がさまざま起きている現状です。
目まぐるしく変わる国際情勢の中、先がなかなか見えない状況がありますが、今できることをしっかりとやって行くしかないと思いながら、、、
今日も頑張るぞ!

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

システムでは、画像データを画面上に表示させたいというケースがときにあるかと思います。そのときに、その画像データを物理ファイルとして持ちつつ、データベースではファイルパスを管理する方法と、データベースにバイナリデータ自体を保存する方法のどちらを採用するか、など判断に迷うことがあるかと思います。
今回は、ファイルパスを管理する方法で、SQL Server 2012で追加された機能であるFileTableを使う方法を取り上げてみたいと思います。FileTableでは、Windowsファイル名前空間をサポートすることで、エクスプローラからファイルのツリー階層を確認したり、データを登録することができますし、アプリケーションからファイルパスを指定してファイルを表示したりすることができます。

例)
SQL ServerのDB上に商品マスタを作成し、その商品イメージの画像データをFileTableを使用して保存します。
まず、そのための準備として、SQL Serverの設定が必要になります。
これについては、参考になるホームページもいくつかあり、そちらの内容も参考にしながら設定してみました。

1)インスタンスレベルでFileStreamを有効化
スタートメニューから、[Microsoft SQL Server 2014] の [構成ツール] の SQL Server 2014 構成マネージャを起動し、[SQL Serverのサービス]から FileStreamを有効化したいインスタンスを選択します。そして、マウスの右クリックでプロパティを選択し、[FILESTREAM]タブを選択します。そこで、下図のように、[Transact-SQLアクセスに対してFILESTREAMを有効にする] [ファイルI/Oアクセスに対してFILESTREAMを有効にする]にチェックを入れます。

blog72_%e6%a7%8b%e6%88%90%e3%83%9e%e3%83%8d%e3%83%bc%e3%82%b8%e3%83%a3

 次に、SQL Server 2014 Management Studioを起動し、サーバーのプロパティ画面の、[詳細設定]メニューから、[FILESTREAMアクセスレベル]を[有効なフルアクセス]に変更します。

blog72_%e3%82%b5%e3%83%bc%e3%83%90%e3%83%97%e3%83%ad%e3%83%91%e3%83%86%e3%82%a3

2)FileStreamを有効にしたデータベースの作成

SQL Server 2014 Management Studioで新しいデータベース(BLOG_DATA)を作成します。このとき、左側メニューの[ファイルグループ]を選択し、下側のFILESTREAM用のファイルグループを追加し、任意の名前を設定します。

blog72_%e3%83%87%e3%83%bc%e3%82%bf%e3%83%99%e3%83%bc%e3%82%b9_%e3%83%95%e3%82%a1%e3%82%a4%e3%83%ab%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97

次に、左側メニューの[全般]を選択し、[追加]ボタンにより[ファイルの種類]が[FILESTREAMデータ]のデータベースファイルを1行追加します。このとき、 [ファイルグループ]は先ほど追加したFILESTREAM用のファイルグループが自動的に選択され、[論理名]は任意の名前にします。

blog72_%e3%83%87%e3%83%bc%e3%82%bf%e3%83%99%e3%83%bc%e3%82%b9%e5%85%a8%e8%88%ac

さらに、左側メニューの[オプション]を選択し、[FILESTREAMディレクトリ名]に任意の文字列を入力、[FILESTREAM非トランザクションアクセス]をFullにしてデータベースを作成します。

blog72_%e3%83%87%e3%83%bc%e3%82%bf%e3%83%99%e3%83%bc%e3%82%b9_%e3%82%aa%e3%83%97%e3%82%b7%e3%83%a7%e3%83%b32

 3)FileTableの作成
FILESTREAMデータやファイル、ディレクトリなどの情報を格納する固定スキーマを含んだ特殊なユーザーテーブルとして、FileTable というものを作成します。FileTableを作成するスクリプトは以下のようになります。

CREATE TABLE FileTable_gazo1 AS FILETABLE
 WITH
 (
   FILETABLE_DIRECTORY = 'FileTable_gazo1',
   FILETABLE_COLLATE_FILENAME = database_default
 )
GO

blog72_cre_filetable

実際のデータベースをManagement Studioで見てみると、「FileTable_gazo1」というテーブルが、「FileTables」というカテゴリの中に作成されているのがわかります。

blog72_manstudio1

4)画像データの登録
上記で作成したFileTableにエクスプローラで接続して、画像データを登録します。SQLServer Management Studioから先ほど作成したFileTableを右クリックし、コンテキストメニューから[FileTableディレクトリの探索]を選択します。

blog72_manstudio2

すると、エクスプローラが開いてきます。

filetable1_%e3%82%a8%e3%82%af%e3%82%b9%e3%83%97%e3%83%ad%e3%83%bc%e3%83%a9

ここに、エクスプローラで、別のフォルダから画像データ(今回はペンの画像)をドラッグ&ドロップで持ってきます。ドラッグ&ドロップした後の状態が以下。

filetable_%e3%82%a8%e3%82%af%e3%82%b9%e3%83%97%e3%83%ad%e3%83%bc%e3%83%a9_%e7%94%bb%e5%83%8f

次に、この画像情報を持つ商品マスタテーブルを作成し、データを登録します。

CREATE TABLE dbo.syomst(
             商品コード varchar(10) NOT NULL,
             商品名 nvarchar(20) NULL,
             ファイル名 varchar(50) NULL,
  CONSTRAINT PK_syomst PRIMARY KEY (商品コード)
GO

 INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0001', N'ペン_01', 'images01.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0002', N'ペン_02', 'images02.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0003', N'ペン_03', 'images03.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0004', N'ペン_04', 'images04.jpg')
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0005', N'ペン_05', 'images05.jpg')
…(途中省略)
INSERT dbo.syomst (商品コード, 商品名, ファイル名) VALUES ('A0040', N'ペン_40', 'images40.jpg')
GO

blog72_insert

この商品マスタ「syomst」と、画像データの「FileTable_gazo1」をジョインして、画像データの名前(name)を取得してみます。ここでは、GetFileNamespacePath というファンクションを使用して、FileTable 内のファイルの相対パスを取得しています。

select s.商品コード, file_stream.GetFileNamespacePath() f_name
from dbo.syomst s
join dbo.FileTable_gazo1 f
 on s.ファイル名 = f.name
order by s.商品コード;

blog72_select_filetable

上記で作成したデータを、試しにdbSheetClientというツールで、EXCELに表示させる画面を作成してみると、以下のように一覧で表示させることができました。

blog72_dbsheet

 今日は以上まで

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

一時テーブルのキー(SQL Server)

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

img_0509

毎朝、自転車で通っている道にコスモスが植えてあるので、ちょこっと写真を撮ってみました。

さて、dbSheetClientのユーザー事例にJR九州様が載りました。
Excelを利用して行ってきた勘定明細内訳システムを、dbSheetClientを採用してシステム化したということ。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/jr_kyushu.html

JR九州様と言えば、東京証券取引所に株式を上場したということで、とてもホットなニュースになっていますね。今年の大型IPO(新規株式公開)案件としては、LINEに次ぐ案件として注目を浴びていましたが、初値は公募価格を大きく上回る金額になったとか。今後の動きにも注目です!

<本日の題材>
一時テーブルのキー(SQL Server)

以前、一時テーブルについての記事をアップしたことがありました。一時テーブル一時テーブル(2)。そのときには、一時テーブルの主キーやインデックスなどのキーについては特に記事としては上げていませんでしたが、今回、実際のシステムの開発時に気付いたことがありましたので、それについて取り上げてみたいと思います。

 ストアドプロシージャなどの中で、一時テーブルを作成してそこに一旦データを登録し、それを使っていろいろな条件によって処理を実行するようなことがよくあるかと思いますが、データ量が多くなると、やはり処理速度が遅くなってきます。
(一時テーブルは、SQL Serverの場合は、「tempdb」というシステムデータベースを利用して処理が行われていますし、Oracleの場合も、テンポラリー用の表領域を使うかたちになります。)

ですので、インデックスを作成することで、処理を早めるということは可能になります。このとき、主キーを設定したいと思う場合もあるかと思いますが、そのときには注意が必要です。
実は、SQL ServerをDBとして一時テーブルを使った処理のレスポンスをアップしようと思ったときに、主キーを設定したのですが、Create table文のときに、実テーブル作成時と同じように、Constraint句で主キー制約を設定し、制約名を設定しました。すると、そのプロシージャをほぼ同時のタイミングで複数のユーザで実行したところ、制約名が同じという理由で主キーを作成できないというエラーが起きてしまいました。
一時テーブルだから、別々のユーザで行う別々のセッションの処理では問題なく動作するはずという認識でいたのに、思わぬ落とし穴に引っかかってしまったような感覚でした。
ですので、一時テーブルでパフォーマンスを上げるためにインデックスが必要であれば、主キーではなく普通のインデックスを作成するほうが無難かと思われます。処理として主キー制約が必要な場合は、Create table 文で一時テーブルを作成した後に、Alter table文で主キーを追加するようにし、そのときに制約名を指定しないかたちを取る方法がよいかと思われます。

例)
以前ブログに一時テーブルの内容を取り上げたときの例を使い、主キーを設定して試してみたいと思います。そのときに、処理がすぐに終わらないように、一時テーブル作成後に、30秒間待機させるコマンド「WAITFOR DELAY」を設定します。(「スリープ処理」という標題で以前ブログで取り上げました)

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
   -- 一時テーブルの作成
       CREATE TABLE #顧客初回購入(
               顧客ID                 VARCHAR(8)  NOT NULL,
               初回購入日           DATETIME,
              CONSTRAINT PK_t顧客初回購入 PRIMARY KEY (顧客ID)
       );
       CREATE TABLE #顧客購入履歴(
               顧客ID              VARCHAR(8)  NOT NULL,
               累計購入回数    decimal(7),
               累計購入金額    decimal(9),
              CONSTRAINT PK_t顧客購入履歴 PRIMARY KEY (顧客ID)
       );

       WAITFOR DELAY '00:00:30'                -- 30秒間待機

   -- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #
顧客初回購入
        SELECT
顧客ID, MIN(出荷日)
          FROM dbo.
売上
         WHERE
顧客ID = @顧客ID
         GROUP BY
顧客ID;

   -- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #
顧客購入履歴
        SELECT
顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.
売上
         WHERE
顧客ID = @顧客ID
         GROUP BY
顧客ID;

    -- 顧客購買履歴情報の表示
        SELECT
          A.
顧客ID
        , FORMAT(A.
初回購入日,'yyyyMMdd') AS 初回購入日
        , B.
累計購入回数
        , B.
累計購入金額
         FROM #
顧客初回購入 A
         JOIN #
顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

このプロシージャの実行を、2つのセッションで同時に行ってみます。
1つめのセッションで以下を実行:

DECLARE @顧客ID varchar(8)='K0000054'
EXEC dbo.顧客購買履歴情報抽出
 @顧客ID

すると、30秒後に以下のような結果が返ってきます。

blog71_exec_1

上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。

DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
 @顧客ID

すると、下記のようにエラーが返ってきます。

blog71_exec_2

1つめのセッションのプロシージャで一時テーブルを作成し、それを使った処理が終わってないうちに、2つめのセッションの処理を行おうとすると、一時テーブル作成時に、主キーに設定した制約名が既に存在しているものと同じということでエラーになったということです。

ということなので、一時テーブルの作成時に主キーを設定する場合には、制約名を固定で指定しないかたちにしたほうがいいと思われます。
具体的には、以下のようにします。

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
   -- 一時テーブルの作成
       CREATE TABLE #顧客初回購入(
               顧客ID                 VARCHAR(8)  NOT NULL,
               初回購入日           DATETIME
       );
       CREATE TABLE #顧客購入履歴(
               顧客ID              VARCHAR(8)  NOT NULL,
               累計購入回数    decimal(7),
               累計購入金額    decimal(9)
       );

    ALTER TABLE #顧客初回購入 ADD PRIMARY KEY (顧客ID);
    ALTER TABLE #顧客購入履歴 ADD PRIMARY KEY (顧客ID);

       WAITFOR DELAY '00:00:30'                -- 30秒間待機

   -- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #
顧客初回購入
        SELECT 
顧客ID, MIN(出荷日)
          FROM dbo.
売上
         WHERE 
顧客ID = @顧客ID
         GROUP BY 
顧客ID;

   -- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #
顧客購入履歴
        SELECT 
顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.
売上
         WHERE 
顧客ID = @顧客ID
         GROUP BY 
顧客ID;

    -- 顧客購買履歴情報の表示
        SELECT
          A.
顧客ID
        , FORMAT(A.
初回購入日,'yyyyMMdd') AS 初回購入日
        , B.
累計購入回数
        , B.
累計購入金額
         FROM #
顧客初回購入 A
         JOIN #
顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

上記のように修正したプロシージャをコンパイルし、その実行を、再度2つのセッションで同時に行ってみます。

1つめのセッションで実行:
すると、30秒後に以下のような結果が返ってきます。

blog71_exec_1

上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。

blog71_exec_2ok

今度は、こちらもエラーは出ず、30秒後には上記のような結果が返ってきました。

プロシージャの実行時に、SQL Server Management Studio のほうで一時テーブルを確認してみると、下記のように、2つずつ一時テーブルが作成されているのがわかります。

blog71_%e4%b8%80%e6%99%82%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab

また、主キーである必要がなく、インデックスの作成でよければ、インデックスを追加するかたちで行うこともできます。
この場合は、ALTER TABLE … ADD PRIMARY KEY の部分を、CREATE INDEX xxx …に変更します。具体的には、

CREATE INDEX IX_t顧客初回購入 ON #顧客初回購入(顧客ID);
CREATE INDEX IX_t顧客購入履歴 ON #顧客購入履歴(顧客ID);

上記に変更したプロシージャをコンパイルして実行しても、先ほどと同様にエラーなく処理は実行されました。

今日は以上まで

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

参照整合性制約(SQL Server)

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

img_0754

知人が松本城に行ったときに撮ってきた写真を頂きました。
ちょっと曇りの天気なのが残念ですが、天守閣が堀と一緒にきれいに写っています。松本城の天守は国宝で、その周りにある石垣や堀も国の史跡に指定されているそうです。天守の建造年は、戦国時代~江戸時代初期の頃だそうで、NHK大河ドラマで放映中の「真田丸」の主人公 真田信繁(幸村)が活躍した時代に重なるようです。
真田丸のほうも関ヶ原の戦いも終わり、徐々にクライマックスに向かっていきますね。ちなみに、「真田丸」とは、真田信繁が、大阪城に入城した後、大坂冬の陣の際に、大阪城の最弱部であったとされるところの近辺に築いた土づくりの出城のことのようです。今後、大河ドラマで放映されると思われる真田丸での戦いを、是非見てみたいです。

<本日の題材>
参照整合性制約(SQL Server)

前回、参照整合性制約(外部キー制約)について、ORACLEでの例を上げて記事をアップしました。今回は、SQL Serverで試してみたいと思います。

例)
前回ORACLEで試したものと同様のテーブルをSQL Serverで作成します。社員マスタと部門マスタを作成し、部門マスタの部門CDを社員マスタから参照するかたちの設定をしてみます。
まず、部門マスタを作成します。

CREATE TABLE dbo.BUMON_M(
BUMON_CD VARCHAR(3)
, BUMON_NAME VARCHAR(20)
, CONSTRAINT PK_BUMON PRIMARY KEY (BUMON_CD)
);

次に、社員マスタを作成します。

CREATE TABLE dbo.SYAIN_M(
 SYAIN_CD VARCHAR(10)
, SYAIN_NAME VARCHAR(20)
, BUMON_CD VARCHAR(3) DEFAULT ‘ ‘
, POSITION VARCHAR(40)
, CONSTRAINT PK_SYAIN PRIMARY KEY (SYAIN_CD)
, CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES dbo.BUMON_M (BUMON_CD)
);

上記の、7~8行目の CONSTRAINT .. FOREIGN KEY .. REFERENCES ..という表記部分が参照整合性制約の設定箇所で、ORACLEでの記述と基本同じです。

部門マスタ(BUMON_M)に何件かデータを登録し、現在以下の部門が存在しているとします。

SELECT * FROM dbo.BUMON_M
ORDER BY BUMON_CD;

blog70_%e9%83%a8%e9%96%80m

この状況で、部門マスタに存在しない部門CD「500」で社員を登録しようとしてみると、

INSERT INTO dbo.SYAIN_M VALUES('10311', 'メンバーK', '500', '一般社員');

blog70_insert_err

 上記のように、SQL Serverの場合も、ORACLE同様エラーが出てレコードを登録できません。SQL Serverでのエラーメッセージは、「メッセージ 547、レベル 16、状態 0、行 38 INSERT ステートメントは FOREIGN KEY 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.BUMON_M", column 'BUMON_CD' です。」となります。

また、各部門CDを持つ社員データを社員マスタに登録した状態で、部門マスタから既存の部門CDのレコードを削除しようとしてみます。

DELETE FROM dbo.BUMON_M
WHERE BUMON_CD = '100';

blog70_del_err

 この場合も、上記のように、「メッセージ 547、レベル 16、状態 0、行 41 DELETE ステートメントは REFERENCE 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.SYAIN_M", column 'BUMON_CD' です。」というエラーが出て、削除することはできません。

それでは、子表にデータが存在している親表のキーの値を更新してみます。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '500'
WHERE BUMON_CD = '300';

blog70_upd_err

この場合も、やはり「メッセージ 547、レベル 16、状態 0、行 45 UPDATE ステートメントは REFERENCE 制約 "FK_BUMON" と競合しています。競合が発生したのは、データベース "BLOG"、テーブル "dbo.SYAIN_M", column 'BUMON_CD' です。」というエラーが出て、更新することはできません。

以上のように、参照整合性制約(外部キー制約)のデフォルトの設定は、NO ACTION と言われるもので、参照先のデータの更新や削除を禁止するかたちになります。

SQL Serverでも、オプションの設定として、下記のようなものがあります。
・ON DELETE { CASCADE | SET NULL | SET DEFAULT }
・ON UPDATE { CASCADE | SET NULL | SET DEFAULT }

「ON DELETE CASCADE」「ON DELETE SET NULL」については、前回、ORACLEで動作を確認しましたので、今回は省略し、それ以外のオプションについて確認してみます。

・ON UPDATE { CASCADE | SET NULL | SET DEFAULT }
ORACLEにはなかったオプションです。動作を確認してみます。

参照整合性制約のオプションを変更するため、現在の制約を削除し、オプションを設定した制約を追加します。このとき、ON DELETE CASCADE に加えて ON UPDATE CASCADE も設定します。

ALTER TABLE dbo.SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE dbo.SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M(BUMON_CD)
        ON DELETE CASCADE
        ON UPDATE CASCADE;

blog70_alter%e5%88%b6%e7%b4%841

 まず、社員表から、部門CD=’100’のデータを確認します。

SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;

blog70_%e7%a4%be%e5%93%a1

部門マスタのBUMON_CD = ‘100’のデータを'500'に更新します。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '500'
WHERE BUMON_CD = '100';

blog70_upd_cascade

このとき、先ほどの社員マスタのデータを確認します。

SELECT * FROM dbo.SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

blog70_%e7%a4%be%e5%93%a1_upd

社員マスタの部門CDが「100」であったデータが、「500」に変わっていることが確認できます。

次に、参照整合性制約のオプションを、ON UPDATE SET NULL に変更します。

ALTER TABLE dbo.SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE dbo.SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M(BUMON_CD)
        ON DELETE CASCADE
        ON UPDATE SET NULL;

blog70_alter%e5%88%b6%e7%b4%842

 先ほど変更した部門マスタのBUMON_CD = ‘500’のデータを、元の’100’に変更します。

UPDATE dbo.BUMON_M SET
 BUMON_CD = '100'
WHERE BUMON_CD = '500';

blog70_upd_setnull

このとき、先ほど部門CD「500」になっていた社員マスタのデータを確認します。

SELECT * FROM dbo.SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

blog70_%e7%a4%be%e5%93%a1_updnull

先ほど、部門マスタ側で変更した部門CDをもつ社員マスタの部門CDは、NULL に変更されていることが確認できました。

※ON UPDATE SET DEFAULT、ON DELETE SET DEFAULT については、親表の対応する行が削除、更新されると、子表の外部キーを構成する値にデフォルト値がセットされるという認識でしたが、テストしてみたところ、思ったような動作にならなかったため、今回は省略します。

以上から、SQL Serverの場合には、ON UPDATE … という更新時の対応のオプションがあるのが確認できました。

今日は以上まで

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

参照整合性制約(外部キー制約)

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

img_0334

近くの川で、かもが泳いでいるのが見えたので、写真にとってみました。
9月のシルバーウィークも終わり、もうすぐ10月を迎えようとしています。今年は台風も多く日本に上陸し、いろいろと大変な天候が続いていました。地球温暖化の影響もますます大きくなってきている気がしますね。
この前、NHKで放映していたNHKスペシャル MEGA CRISIS 巨大危機~脅威と闘う者たち~ 第1集「加速する異常気象との闘い」をたまたま見たのですが、21世紀末には平均気温が今より4℃上昇し、最高気温が東京で43・0度、名古屋では44.5度にもなるという予測が出ていました。また、巨大積乱雲(スーパーセル)も頻繁に発生し、東京都の最大雨量も1日あたり現在100mm程度なのが、310mmまで増加し、大雨のときには地下街は水没してしまうかもしれないというような、とてつもない状況をこのままでは迎えてしまうという話しです。
21世紀末と言えば、80数年後なので、私たちの孫や曾孫が生きている世代です。いよいよ、環境問題は待ったなしの状況に来ている気がしますね。このような脅威と如何に向き合い、温暖化を抑制していくのか、科学の英知の結集と世界的な連携が必要だということを改めて感じるこの頃です。

 <本日の題材>
参照整合性制約(外部キー制約)

リレーショナルデータベースには、整合性制約と言って、データの整合性を保つための制約を設定することができ、主キー制約、一意キー制約、NOT NULL制約、CHECK制約、参照整合性制約などがあります。

今回は、参照整合性制約(外部キー制約)について取り上げてみたいと思います。参照整合性制約とは、関連付けられた表の共通する列の関係が保たれていることをDB側で保証するもので、簡単にいえば、参照先のテーブルにデータが存在しないようなデータの入力を許可しないということになります。
よく例として挙げられるのが、ORACLEにあらかじめ用意されているEMP表(従業員表)とDEPT表(部門表)で、EMP表の部門番号(DEPTNO)は、DEPT表の部門番号(DEPTNO)に存在するデータしか登録できないというものです。
 このとき、DEPT表を親表、EMP表を子表、DEPTNOを外部キーと言います。そして、DEPT表のDEPTNOは主キー、もしくは一意キーである必要があります。
この参照整合性制約の設定は、基本的にテーブル作成時に設定しますが、後から制約を追加することも可能です。

例)
それでは、例として、社員マスタと部門マスタを作成し、部門マスタの部門CDを社員マスタから参照するかたちの設定をしてみます。(ORACLE)
まず、部門マスタを作成します。

CREATE TABLE BUMON_M(
 BUMON_CD VARCHAR2(3)
, BUMON_NAME VARCHAR2(20)
, CONSTRAINT PK_BUMON PRIMARY KEY (BUMON_CD)
);

社員マスタを作成します。

CREATE TABLE SYAIN_M(
 SYAIN_CD VARCHAR2(10)
, SYAIN_NAME VARCHAR2(20)
, BUMON_CD VARCHAR2(3)
, POSITION VARCHAR2(40)
, CONSTRAINT PK_SYAIN PRIMARY KEY (SYAIN_CD)
, CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M (BUMON_CD)
);

上記の、7~8行目の CONSTRAINT .. FOREIGN KEY .. REFERENCES ..という表記部分が参照整合性制約の設定箇所になります。

部門マスタ(BUMON_M)に何件かデータを登録し、現在以下の部門が存在しているとします。

SELECT * FROM BUMON_M
ORDER BY BUMON_CD;

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf

この状況で、部門マスタに存在しない部門CD「500」で社員を登録しようとしてみると、

INSERT INTO SYAIN_M VALUES('10311', 'メンバーK', '500', '一般社員');

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_insert

上記のように、「ORA-02291: 整合性制約(BLOG.FK_BUMON)に違反しました - 親キーがありません」というエラーが出て、レコードを登録できません。

また、各部門CDを持つ社員データを社員マスタに登録した状態で、部門マスタから既存の部門CDのレコードを削除しようとしてみます。

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_del

 この場合も、上記のように、「ORA-02292: 整合性制約(BLOG.FK_BUMON)に違反しました - 子レコードがあります」というエラーが出て、削除することはできません。

それでは、子表にデータが存在している親表のキーの値を更新してみます。

UPDATE BUMON_M SET
 BUMON_CD = '500'
WHERE BUMON_CD = '300';

blog69_%e6%95%b4%e5%90%88%e6%80%a7%e3%82%a8%e3%83%a9%e3%83%bc_upd

 この場合も、やはり「ORA-02292: 整合性制約(BLOG.FK_BUMON)に違反しました - 子レコードがあります」というエラーが出て、更新することはできません。

以上のように、参照整合性制約(外部キー制約)のデフォルトの設定は、NO ACTION と言われるもので、参照先のデータの更新や削除を禁止するかたちになります。
(※親表のテーブルをDROPしようとしてもエラーになります。)
そのため、どうしても親表のデータを更新したいときには、更新する値と一致する子表の部門CDの値を一旦親表に存在する違う値に更新してから、親表のキー値を更新するというような手順が必要になります。

ただ、オプションの設定として、「ON DELETE SET NULL」「ON DELETE CASCADE」というものがあります。それぞれ、どういう動作になるかというと、

・ON DELETE SET NULL
 これは、親表のキー(部門CD)の値を削除すると、その値と同じ値を持っていた子表のほうの部門CDは NULL に更新されるというものです。

・ON DELETE CASCADE
 これは、親表のキー(部門CD)の値を削除すると、その値と同じ値を持っていた子表のほうのレコード自体が削除されるというものです。

試してみます。
参照整合性制約のオプションを変更するため、現在の制約を削除し、オプションを設定した制約を追加します。

ALTER TABLE SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M (BUMON_CD) ON DELETE SET NULL;

blog69_%e5%88%b6%e7%b4%84_%e5%86%8d%e4%bd%9c%e6%88%90

まず、社員表から、部門CD=’100’のデータを確認します。

SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf_100

部門マスタのBUMON_CD = ‘100’のデータを削除します。

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e9%83%a8%e9%96%80del

このとき、先ほど抽出した、部門CD=100 だった社員マスタのデータを確認します。

SELECT * FROM SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

blog69_%e7%a4%be%e5%93%a1%e3%83%9e%e3%82%b9%e3%82%bf%e7%a2%ba%e8%aa%8d1

 社員マスタの部門CDが「100」であったデータが、NULL に変わっていることが確認できます。

一旦元に戻します。
ROLLBACK;

次に、参照整合性制約のオプションを、ON DELETE CASCADE に変更します。

ALTER TABLE SYAIN_M DROP CONSTRAINT FK_BUMON;

ALTER TABLE SYAIN_M ADD CONSTRAINT FK_BUMON FOREIGN KEY (BUMON_CD)
   REFERENCES BUMON_M (BUMON_CD) ON DELETE CASCADE;

blog69_%e5%88%b6%e7%b4%84_%e5%86%8d%e4%bd%9c%e6%88%902

再度、社員表から、部門CD=’100’のデータを確認します。

SELECT * FROM SYAIN_M
WHERE BUMON_CD = '100'
ORDER BY SYAIN_CD;

blog69_%e9%83%a8%e9%96%80%e3%83%9e%e3%82%b9%e3%82%bf_100

部門マスタのBUMON_CD = ‘100’のデータを削除します。

DELETE FROM BUMON_M
WHERE BUMON_CD = '100';

blog69_%e9%83%a8%e9%96%80del

このとき、先ほどの社員マスタのデータを確認します。

SELECT * FROM SYAIN_M
WHERE SYAIN_CD IN ('10001','10005','10006')
ORDER BY SYAIN_CD;

blog69_%e7%a4%be%e5%93%a1%e3%83%9e%e3%82%b9%e3%82%bf%e7%a2%ba%e8%aa%8d2

親表から削除した部門CD=’100’を持つ、子表のレコードが削除されたことが確認できます。
※実際に使用する場合には、子表のレコードが自動的に削除されてしまうので、運用上問題がないか注意が必要です。

このように、DBのほうでデータの整合性を保つように制御することができますが、実際のシステムではあまりこれを細かく設定したという経験は少ないのが実情です。これには賛否両論があり、実際にシステムに実装した場合に、テストデータなどの作成がいろいろと大変になるとか、レスポンスへの影響、また、整合性のエラーが起きた際のエラーメッセージをユーザ向けに変更する処理が必要になり、アプリ側での負担になる場合が多いということなどが影響しているようです。
次回は、SQL Serverで試してみたいと思います。

今日は以上まで

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