式を使用した表の結合とパフォーマンス

とあるデータ、たとえばセールス オーダー番号などは、組織内にある様々なデータベース表で完全に同じように定義されていると思われがちですが、実は、そうではないのかもしれません。2つ以上の表を、一致していないデータ タイプで結合するという難問に直面したことは何度もあります。

しかし、もっと厄介な状況もあります。結合に、算術演算や関数（組み込み、ユーザー記述を問わず）の呼び出しから成る、式が含まれるケースです。 SELECT内のDISTINCTキーワードのケースと同様に、結合で式が使用されているのに気が付くと、私には警報音が聞こえてきます。その照会は見直しが必要だということを知らせる警報音です。他の人には聞こえないと思いますが。そのような場合は、パフォーマンスの問題に取り組む必要が生じるかもしれません。

すべての結合は、同一のデータ タイプ間で行われるのが理想的です。2つの表について考えてみましょう。セールス オーダー ヘッダー（sales order header）表と、セールス オーダー詳細（sales order detail）表です。

create table sordh
  ( OrderNbr     dec   (5),
    CustomerID   dec   (5),
    DueDate      date,
    primary key (OrderNbr));

insert into sordh values
( 320, 20260, '2021-07-15'),
( 321, 33750, '2021-07-12'),
( 322, 19455, '2021-07-25');
    
create table sordd
  ( OrderNbr     dec   (5),
    LineNbr      dec   (3),
    Item         char  (6),
    Quantity     dec   (3),
    primary key ( OrderNbr, LineNbr ));

insert into sordd values
( 320, 1, 'AB-101', 12),
( 320, 2, 'BC-202',  1),
( 321, 1, 'XZ-411',  2),
( 322, 1, 'JR-399',  6);

これら2つの表は、オーダー番号（order number）で結合するのは言うまでもありません。オーダー番号は、どちらの表でも5桁のパック10進数として定義されています。

ここで、製造オーダー（manufacturing order）表を導入しましょう。

create table mord
  ( OrderNbr     dec   (5),
    Item         char  (6),
    Quantity     dec   (3),
    DueDate      date,
    Comment      char (24),
    primary key ( OrderNbr )); 

insert into mord values
( 725, 'C-9091', 2, '2021-07-01', 'SABGR7AA00005 DE00320001'),
( 726, 'K-3432', 1, '2021-07-01', 'TLD  7AB00003X  00320001'),
( 727, 'D-8850', 1, '2021-07-05', 'MENO 7TB00121 4 00321001'),
( 728, 'F-2855', 1, '2021-06-30', 'PLOTZ6  00009   00320002');

少し経緯を説明しておきましょう。この会社では、見込み生産方式向けに設計されたERPパッケージを使用しています。そのため、製造オーダーとセールス オーダー ラインとを結び付ける方法がありません。この会社が受注生産を行うことになり、その際、進取果敢なIT部門のスタッフは、オーダー番号（order number）とオーダー ライン番号（order line number）を、COMMENTフィールドの最後の8桁に入れることとしました。

以下は、製造オーダー（manufacturing order）表とセールス オーダー ヘッダー（sales order header）表との結合です。

select m.OrderNbr, m.Item, m.DueDate, soh.CustomerID
  from mord as m
  left join sordh as soh
    on substr(m.Comment, 17, 5) = soh.OrderNbr;

パフォーマンスがあまり芳しくないことは、ほぼ間違いありません。システムは、COMMENT列の17～21バイト目に何があるか調べるために、MORD表のすべての行を読み取る必要があるからです。

この照会を高速化「するはずである」1つの方法は、COMMENTの当該部分に対して索引を作成することです。

create index mord1 on mord (substr(Comment, 17, 5));

「 するはずである 」と言ったのは、照会エンジンが索引を使用することを確信できないからです。索引アドバイザーの推奨に基づいて構築していた索引が、照会エンジンによって無視されたこともありました。

もうひとつ、方法があります。索引を式に対して構築できなかった頃によく使用していた方法です。すなわち、相互参照表を作成することができます。

この架空のシステムでは、セールス オーダー番号は3種類のフォーマットで保存されているとします。そのうち2つについては、先程、見たところです。3つ目のフォーマットは、出荷（shipments）表にあります。ERPの出荷の処理方法に思わしくないところがあるため、他の出荷管理ソフトウェアを使用することになりました。そこには、8文字のセールス オーダー番号用のフィールドがあります。

create table shipments
  ( ID          dec(7),
    Date        date,
    Carrier     char(3),
    SalesOrd    char(8),
    primary key (ID));

create index shipment1 on shipments (cast (SalesOrd as dec(8)));

insert into shipments values
( 184, '2021-07-15', 'ABC', '00000320'),
( 185, '2021-07-15', 'ABC', '00000322');

以下は、出荷（shipments）表とセールス オーダー ヘッダー（sales order header）表との結合です。

select sh.ID, sh.Date, sh.Carrier,
       so.OrderNbr, so.CustomerID, so.DueDate
  from shipments  as sh
  left join sordh as so on sh.SalesOrd = so.OrderNbr
 order by sh.ID;

まずまずでしょうか。この結合では、式は使用されていません。あるいは、式があるのでしょうか。セールス オーダー番号（sales order number）は、一方の表では5桁で、他方の表では8文字です。 暗黙的な型変換が行われています。

では、相互参照表に戻りましょう。セールス オーダー1件に対して行が1行の表を作成します。各行には、ここで使用している3種類のフォーマットでのセールス オーダー番号が入ります。

create table soxref
  ( OrderNbr   dec(5),
    OrderC8    char(8),
    OrderC5    char(5),
    primary key (OrderNbr));

create index soxref1 on soxref (OrderC8);
create index soxref2 on soxref (OrderC5);

insert into soxref values
( 320, '00000320', '00320'),
( 321, '00000321', '00321'),
( 322, '00000322', '00322');

もう一度、先程の出荷（shipments）表の結合を行ってみましょう。

select sh.ID, sh.Date, sh.Carrier,
       so.OrderNbr, so.CustomerID, so.DueDate
  from shipments as sh
  left join soxref as x  on sh.SalesOrd = x.OrderC8
  left join sordh as so  on x.OrderNbr = so.OrderNbr
 order by sh.ID;

照会に相互参照表を追加したことにより、結合からすべてのデータ変換がなくなりました。

しかし、相互参照表は本当に必要なのでしょうか。それぞれの表でセールス オーダー列に索引を作成しました。それで十分ではないのでしょうか。

もちろん、十分です。しかし、相互参照表を作成することで、データ変換がなくなることの他に、別の可能性が広がります。

1つには、他の表から頻繁に要求されるデータを相互参照表へ複製することができます。たとえば、照会に顧客番号を含めることが非常に多いとします。他に必要とされるヘッダー列がない場合には、相互参照表に顧客番号を追加しておくことで、照会にセールス オーダー ヘッダー表を含める必要性をなくすことができます。私は、この手法を推奨するわけではありません。私は、きちんと正規形に準拠して、データベースに例外が入らないようにしたいと思っています。ただし、適切に使用された場合には、複製が効果的なこともあります。

2つには、オーダー番号に関数従属している列を追加できるものの、データベースにその場所がないというケースがあります。この例では、セールス オーダー ヘッダー表は、ソフトウェア ベンダー製です。独自のデータをデータベースに追加する方法を、このベンダーが用意してくれているのでなければ、追加のフィールドをSORDH表に追加すると、おそらくエラーになるでしょう。その代わりに、独自のデータを相互参照表に追加することができます。

これについては、典型的な例があります。以前に作業したことのあるいくつかのショップには、カレンダー ファイル（日付ディメンション ファイルとも呼ばれる）がありました。そのようなファイル（または表）では、それぞれの日付ごとに1つのレコード（行）があります。そのファイルのフィールド（列）には、以下のように、様々な種類の値が入ります。

• 各種日付フォーマットおよびデータ タイプでの日付
• 複数のフォーマットでの曜日
• 日付が平日、週末、祝日などであるかどうかを示すフラグ
• DAYS関数によって返される値
• 月の初日および末日
• 会計期間情報
• その他

以下は、シンプルなカレンダー表の定義です。

create or replace table Calendar
 ( BaseDate        date,
   YYMD            numeric(8),
   MDY_Edited      char(8),
   MDYY_Edited     char(10),
   MDY             numeric(6),
   MDYY            numeric(8),
   DayOfWeek       numeric(1),
   DOWAbbr         char(3),
   DayOfWeekName   varchar(9),
   primary key (BaseDate));
   
create index Calendar1 on Calendar (yymd);   

例として、いくつか行を示します。

このような表を使用することで、JOIN節内だけでなく、SELECT節やWHERE節、およびその他の節内についても、照会から日付変換関数をなくすことができます。

数か月前にこの記事を書き始めた時点では、データベースに日付の表があれば有用なため、この例にかなりスペースを割く予定でした。ところが、そうこうしているうちに、Kent Milligan氏が彼のブログで 同じようなテーマの記事 を公開していました。彼が記したことをここで繰り返しても仕方ないので、カレンダー表との結合に関する詳細については、彼の記事を参照するようにしてください。私はそうしていますが、まだの方は、Kent氏のブログを読者登録するようお勧めします。 また、 こちらの記事についても紹介しておきたいと思います。こちらは、Aaron Bertrand氏が、Microsoft SQL Serverデータベースでのカレンダー表の使用法について記した記事です。Aaron氏は、カレンダー表の有用性について熱心に説明しています。

話を戻しましょう。式を使用して表を結合した場合、機能することは間違いありません。照会エンジンは強力なので、必要とする結果が得られるでしょう。私は、式を使用して結合を行うべきではないと言っているのではありません。パフォーマンス的に許容できるのであれば、照会はそのまま放っておいて、より有用なタスクに時間を割くようにすればよいでしょう。

また、パフォーマンス的に許容できないが、索引の作成も不可能という場合は、そのままに任せるしかないでしょう。

あわせて読みたい記事

SQLで連続データを生成する

IBMｉ海外記事2020.05.13

SQLを使用してシンプルかつ効果的にデータを比較する方法

IBMｉ海外記事2020.06.24

今はIBM i の一部に過ぎないクラウドは、将来ハイブリッド化が進む

IBMｉ海外記事2024.04.24

IBM i のショップは、まだ生成AI導入に向けての準備段階にある

IBMｉ海外記事2024.04.24