- PR -

SQL Server で Oracle の MINUS 集合演算子と同等の SQL 文

«前のページへ 1|2|3|4 次のページへ»
投稿者投稿内容
未記入
大ベテラン
会議室デビュー日: 2003/11/24
投稿数: 121
投稿日時: 2004-09-02 13:45
Oracle の結果を出してくれてるんで、SQL Server の結果でも載せておこうかな。
SQL Server のサンプルデータベース Northwind を使用。社員マスタはあるんだけど、
部署マスタがなかったので、代わりに商品マスタと分類マスタを使用。emp, dept と
本質的な関係は同じです。

コード:
select Products.* 
from Products
where Products.CategoryID in (
  select Categories.CategoryID
  from Categories
  where Categories.CategoryID = Products.CategoryID
)
  |--Nested Loops(Left Semi Join, OUTER REFERENCES:([Products].[CategoryID]))
       |--Clustered Index Scan(OBJECT:([Northwind].[dbo].[Products].[PK_Products]))
       |--Clustered Index Seek(OBJECT:([Northwind].[dbo].[Categories].[PK_Categories]),
            SEEK:([Categories].[CategoryID]=[Products].[CategoryID]),
            WHERE:([Categories].[CategoryID]=[Products].[CategoryID]) ORDERED FORWARD)


select Products.*
from Products, Categories
where Products.CategoryID = Categories.CategoryID
  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([Northwind].[dbo].[Products]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Categories].[CategoryID]))
            |--Index Scan(OBJECT:([Northwind].[dbo].[Categories].[CategoryName]))
            |--Index Seek(OBJECT:([Northwind].[dbo].[Products].[CategoriesProducts]),
                 SEEK:([Products].[CategoryID]=[Categories].[CategoryID]) ORDERED FORWARD)

実行プラン違いますね。内部表/外部表の関係が逆になっています。
相関サブクエリの場合は、その記述によって内部表/外部表が変化しました。
通常の内結合を使用した場合は、テーブルの記述順によらず内部表/外部表は
同じになりました。(オプティマイザがどちらを内部表にしたほうが有利か判断している？)
相関サブクエリを使用せずに内結合で記述した後者のクエリのほうが実行コストが低いという結果になりました。

まあ、記述を意識したプランを立て、相関サブクエリが遅いデータベースの一例ということで。

ちなみに私は「相関サブクエリが必ず遅い」とは言ってないですよ。
「パフォーマンス低下の要因になることが多い」と言っているだけ。
これには、石橋を叩いて渡るという十分な意味があると思うけど？

カーニーさんの「実行プラン至上論」で「相関サブクエリはなるべく書かないほうがいい派」を
撃沈するためには、「相関サブクエリでも同じプランが立つことがある」と主張するのではなく「相関サブクエリのほうが速いプランが立つことがある」を示さないといけないのでは？

それと、select節に相関サブクエリを使用した場合は、さらにオプティマイザは苦労すると思います。やはり、人間がクエリを意識するのも重要なんじゃないかなあ？ その定石のひとつとして、「相関サブクエリのパフォーマンス問題」があるということだと思うんだけども。

引用:

可能性があるからこそ、実際のプランはどうなっているのかに基づいて性能的な優劣を…

相関サブクエリのほうが「速くなる可能性」はありますか？ もし、速くなる可能性がなくて、
「遅くなる可能性(良くても同速が限界)」しかないのであれば、
「相関サブクエリは遅くなりがち」というのは間違いではないと思いますけど。どうでしょう？
未記入
大ベテラン
会議室デビュー日: 2003/11/24
投稿数: 121
投稿日時: 2004-09-02 13:58
引用:

状況次第で良くも悪くなるもなるならば、別ですが、
相関サブクエリは結合処理に比べどの様な状況次第で良くなるのでしょうか？

おっと、はにまる さんと内容がかぶっていましたね。
「相関サブクエリのほうが速くなる例」を出してもらわない限り、
「相関サブクエリを使わずすむなら使わないほうがいい」という意見を
否定することはできないでしょう。いまのところ、カーニー さんは
「相関サブクエリを使用しても、同じ速度が得られる」と言ってるだけ。
同じ速度じゃ意味ないですね。
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2004-09-02 14:59
時間があれば一言一句に対していちいち回答するんですが、それをしなかったから段々論点がずれてきちゃったかな。申し訳ない。

僕はただ、
引用:

Oracleでは、副問合にて主問合せの項目を利用する文法は、
パフォーマンス劣化原因として嫌われています。

これに反論したかっただけです。

これだと十把一絡げで相関副問い合わせは良くない、って誤解する人がいそうな気がしません?

挙句、例えば各部門別に最高給をもらっている人をリストする次のような相関副問い合わせを、

select e1.ename, e1.deptno, e1.sal
from emp e1
where e1.sal = (select max(e2.sal) from emp e2 where e2.deptno = e1.deptno);

2つにばらしてホスト言語で二重ループさせ、却って性能を落としてしまうとかね。

「相関副問い合わせを使う必要があるときには、遠慮なく使ってよいんだよ」
言いたいのはこれだけです。

ただこれだけだと気分が悪いので、相関副問い合わせのほうが速くなる「可能性がある」ケースを挙げておきます。

少なくとも1人は所属部員が存在する部門をリストするクエリーです。

コード:
select dept.*
from dept
where exists (select 'X' from emp where emp.deptno = dept.deptno);

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'DEPT'
   3    1     INDEX (RANGE SCAN) OF 'EMP_N1' (NON-UNIQUE)

select distinct dept.*
from emp, dept
where emp.deptno = dept.deptno;

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (UNIQUE)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'DEPT'
   4    2       INDEX (RANGE SCAN) OF 'EMP_N1' (NON-UNIQUE)


ソートのコスト次第ですが。
未記入
大ベテラン
会議室デビュー日: 2003/11/24
投稿数: 121
投稿日時: 2004-09-02 16:12
引用:

2つにばらしてホスト言語で二重ループさせ、却って性能を落としてしまうとかね。

はにまる さんは、相関サブクエリと結合処理の場合について言っているので、
ホスト言語での制御を持ち出すと話が全然違ってきますね。

引用:

ただこれだけだと気分が悪いので、相関副問い合わせのほうが速くなる「可能性がある」ケースを挙げておきます。


少なくとも1人は所属部員が存在する部門をリストするクエリーですって？
相関サブクエリのほうが速いという結果を得るために、ずいぶん無理をなさいましたね。

SQL Server の Northwind データベースで先程と同じように Products, Categories を
使用してテストを試みました。

後者の distinct を使ったクエリでは、select節に * を使用することはできませんでした。
Categories には Picture という BLOB フィールドがあるためにdistinct が使えなかったのです。まあ、distinct での集約作業は列定義によっては高コストになりますから当然でしょうね。このことからも、内結合 + distinct 集約のほうが高コストになるでしょう。

だけど、相関サブクエリの使用もなんだか不自然です。
使用制限の大きい distinct で書き換えるというのも、
あからさまに遅くしようとしてるみたいに感じますね。

私が、相関サブクエリを使わずに書き換えるのであれば、
コード:
select dept.*
from dept
where dept.deptno in (select emp.deptno from emp)

このように書きますね。

実際、SQL Server では、このクエリと カーニーさんが提示した相関サブクエリは
同一プラン、同一コストとなるので、やはり「相関サブクエリのほうが速い」とは
言えないですね。

distinct を使った不自然に遅くなるクエリを持ち出して、
相関サブクエリのほうが速いと言われてもねぇ…。
もっとシンプルで(提示された相関サブクエリと)同速のクエリが書けるわけで。
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2004-09-02 17:43
引用:

未記入さんの書き込み (2004-09-02 16:12) より:
引用:

2つにばらしてホスト言語で二重ループさせ、却って性能を落としてしまうとかね。

はにまる さんは、相関サブクエリと結合処理の場合について言っているので、
ホスト言語での制御を持ち出すと話が全然違ってきますね。

ええ、その通りです。
なのでそういう勘違いをする方がいると困るので、決して相関副問い合わせが常に悪なわけじゃないですよ、と言ったのです。

引用:

少なくとも1人は所属部員が存在する部門をリストするクエリーですって？
相関サブクエリのほうが速いという結果を得るために、ずいぶん無理をなさいましたね。

ごめんなさい、言いたいことがよく分かりませんでした。
少なくとも1件の未発送の明細を含む受注をリストするクエリー、なら無理じゃないですか?

引用:

だけど、相関サブクエリの使用もなんだか不自然です。
使用制限の大きい distinct で書き換えるというのも、
あからさまに遅くしようとしてるみたいに感じますね。

自然ですよ。
得られる結果が同じになるように書き換えないと意味がないですから。

DISTINCTなしでできる方法がちょっと思いつかないので、良い方法があれば教えてください。
ただしGROUP BYを使うのはなしということでお願いします。

引用:

私が、相関サブクエリを使わずに書き換えるのであれば、
コード:
select dept.*
from dept
where dept.deptno in (select emp.deptno from emp)

このように書きますね。

emp表に例えば10万件あった場合、dept表1件について毎回10万件をフルスキャンしたりはしないんですか?

where dept.deptno in (select emp.deptno from emp where emp.deptno =
dept.deptno)

のほうが良さそうに見えますけど。

ちなみにOracleだと毎回フルスキャンしてしまうので、僕が例に出したEXISTSを使うほうが推奨されます。EXISTSは1件ヒットすれば以降の検索をストップしますから。

引用:

実際、SQL Server では、このクエリと カーニーさんが提示した相関サブクエリは
同一プラン、同一コストとなるので、やはり「相関サブクエリのほうが速い」とは
言えないですね。

distinct を使った不自然に遅くなるクエリを持ち出して、
相関サブクエリのほうが速いと言われてもねぇ…。
もっとシンプルで(提示された相関サブクエリと)同速のクエリが書けるわけで。

えーと、相関副問い合わせのほうが速い(かもしれない)事例をRDBMSに関わらず1つ示せば良いと思ってましたが、SQL Serverの事例も示さないと納得してもらえませんか?

DB2とSybaseとHiRDBも近くにありますが、それも示さないとまずいでしょうか?
でもInformixはないから結局証明は不完全にならざるを得ないのでそこまではやりませんが。悪しからずご了承ください。

あと、はにまるさん、僕の説明で納得いかない部分があれば指摘してもらえると嬉しいです。
はにまる
ぬし
会議室デビュー日: 2003/12/19
投稿数: 969
お住まい・勤務地: 誤字脱字の国
投稿日時: 2004-09-02 18:23
はにまるです。
引用:

カーニーさんの書き込み (2004-09-02 14:59) より:
僕はただ、
引用:

Oracleでは、副問合にて主問合せの項目を利用する文法は、
パフォーマンス劣化原因として嫌われています。

これに反論したかっただけです。
これだと十把一絡げで相関副問い合わせは良くない、って誤解する人がいそうな気がしません?

私は「パフォーマンス劣化原因」と言っており「要注意だぞ！」という意味合いで発言しています。
そう．．．とれませんかね？

それに対して、カーニーさんの発言である
引用:

カーニーさんの書き込み (2004-09-02 00:47) より:
相関サブクエリと結合処理が互いに置き換え可能な場合なら、オプティマイザが内部で表現を変換して、両者に対して同じ実行プランを立てることは大いに考えられます。

その場合に「どっちの書き方がいい?」と聞かれれば、「好きなほうにすればいいんじゃない?」と答えます。

の方が初級技術者にとって、
「な〜んだ、どんなサブクエリでもＤＢが最適化してくれんるだ！」
と十分に思える誤解を招いていると思いますし、
結果として障害を招く事も十分にある為より危険だと考えます。

で、「なんちゃって！」ナガラに業務系ＳＥとして最後一番言いたい事ですが、

ＳＱＬ文はコード量に対する処理能力が高く、
また扱う物自体がデータその物である為に、
コード量に対するシステム影響度合いは桁違いに高いと考えています。

その一方でデータ構成、テーブル規約（特に一意、必須）によっては
他の環境で上手くいったＳＱＬ文でも想定した結果と異なる恐れがある為、
理解をもってＳＱＬを利用する事を強く勧めています。

# その一方では習得時間に対する作業効果は絶大である事も伝えています。

上記の思想を持っているが為にカーニーさんの発言は
ＳＱＬを軽視している様に捉え理解する事が出来ませんでした。

ま〜、高度なお話をしている最中に低レベルな感情論で割り込むな！
と言われそうですが．．．

# 追記
お！割込んでよかったみたい。

>あと、はにまるさん、僕の説明で納得いかない部分があれば指摘してもらえると嬉しいです。



[ メッセージ編集済み 編集者: はにまる 編集日時 2004-09-02 18:26 ]
未記入
大ベテラン
会議室デビュー日: 2003/11/24
投稿数: 121
投稿日時: 2004-09-02 22:15
引用:

自然ですよ。

いや、不自然でしょう。カーニーさんが「相関サブクエリと結合処理が互いに
置き換え可能な場合なら、オプティマイザが内部で表現を変換して、両者に
対して同じ実行プランを立てることは大いに考えられます。」として自信を
持って挙げた製品 Oracle でさえ、異なるプランを立案したのですよね？
それはクエリが不自然なほどに書き換えられているからでは？

「自然である」とした場合、カーニーさんの「記述によらず同じ実行プランに
なることは大いに考えられる」という主張が揺らぎますね。

引用:

DISTINCTなしでできる方法がちょっと思いつかないので、良い方法があれば教えてください。
ただしGROUP BYを使うのはなしということでお願いします。

distinct を使用しない方法はすでにひとつ挙げています。
それと group by を使用しないというのはどのような理由によるのでしょうか？

引用:

emp表に例えば10万件あった場合、dept表1件について毎回10万件をフルスキャンしたりはしないんですか?

そんなバカな。少なくとも SQL Server はそのような非効率なプランには
なりませんでしたよ。

引用:

where dept.deptno in (select emp.deptno from emp where emp.deptno =
dept.deptno)

のほうが良さそうに見えますけど。

いや、私は直感的には相関サブクエリを使用したこちらのほうが高コストになると
予想しました。実際のところ、SQL Server では まったく同一のプランが立ちましたけども。

引用:

えーと、相関副問い合わせのほうが速い(かもしれない)事例をRDBMSに関わらず1つ示せば良いと思ってましたが、SQL Serverの事例も示さないと納得してもらえませんか?

いえいえ滅相もございません。反例はひとつあげていただければ十分です。
それにしても、おもしろい展開ですね。
「同等のクエリになることは大いに考えられる」として挙げた Oracle で、
「同等のクエリにならず、相関サブクエリのほうが速くなることもある」を
お示しになるとは…。ここでも「同等のクエリになることは大いに考えられる」という
主張が揺らぐことになりましたね。

引用:

ちなみにOracleだと毎回フルスキャンしてしまうので、

そうなんですか。Oracle が、そこまでお馬鹿さんなオプティマイザだったとは
思ってもみませんでした。この点に関しては完全に私の負けですね。完敗です。
こんなオプティマイザであれば、相関サブクエリが最適プランとなってしまう、
というのも頷けます。

Oracle のように相関サブクエリが唯一の最適プランを立案するお馬鹿な
オプティマイザが存在することは覚えておきます。よって、
「相関サブクエリに置き換えることで遅くなることはあっても速くなることはない」
という私の主張は全面的に撤回させていただきます。失礼いたしました。
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2004-09-13 14:46
ずいぶん間が空いてしまいました。
忙しかったのと、疲れてしまったのと両方あったんですが、今日全然別の調べモノしていて、偶然にも非常に興味深い記述を見つけたので紹介しておきます。

「マイクロソフト公式解説書 アーキテクチャ徹底解説 Microsoft SQL Server 2000」のP401より引用します。
「外部結合が相関サブクエリより高速に処理されるか否かは、検索されるデータとインデックスによって左右される。どちらのクエリを記述するか判断する前に、いくつかの代替表現を作成し、使用状況の中で最も高速なものを選択したいと考えるとよい。」

その前後の記述もなかなか興味深い。

引用:

未記入さんの書き込み (2004-09-02 22:15) より:
引用:

自然ですよ。

いや、不自然でしょう。カーニーさんが「相関サブクエリと結合処理が互いに
置き換え可能な場合なら、オプティマイザが内部で表現を変換して、両者に
対して同じ実行プランを立てることは大いに考えられます。」として自信を
持って挙げた製品 Oracle でさえ、異なるプランを立案したのですよね？
それはクエリが不自然なほどに書き換えられているからでは？

「自然である」とした場合、カーニーさんの「記述によらず同じ実行プランに
なることは大いに考えられる」という主張が揺らぎますね。


MS SQL Serverのプランの解釈の仕方に自信がなかったので書くのを躊躇していましたが、次の3種類のSQL文は同じ実行プランになっているみたいですがどうなんでしょう?

select dept.* from dept where exists (select 'X' from emp where emp.deptno = dept.deptno);
select dept.* from dept where dept.deptno in (select emp.deptno from emp);
select distinct dept.* from dept, emp where dept.deptno = emp.deptno;

どれも概念的には次のようなSQLに書き変えられたように思えるんですが、やっぱ自然な置き換えだからなんでしょうか?

select dept.* from (select distinct deptno from emp) ev, dept where dept.deptno = ev.deptno;

ちなみにこれは違うプランになりました。

select dept.* from dept where dept.deptno in (select emp.deptno from emp where emp.deptno = dept.deptno);

主クエリーのヒット件数が少なく、サブクエリーでインデックスが使えれば、さっきのやつよりも速いんじゃないかな。冒頭の引用の実例と理解できるかもしれません。

引用:

引用:

DISTINCTなしでできる方法がちょっと思いつかないので、良い方法があれば教えてください。
ただしGROUP BYを使うのはなしということでお願いします。

distinct を使用しない方法はすでにひとつ挙げています。
それと group by を使用しないというのはどのような理由によるのでしょうか？


集合関数なしでGROUP BYを使うとDISTINCTと同じになるので。

引用:

そうなんですか。Oracle が、そこまでお馬鹿さんなオプティマイザだったとは
思ってもみませんでした。この点に関しては完全に私の負けですね。完敗です。
こんなオプティマイザであれば、相関サブクエリが最適プランとなってしまう、
というのも頷けます。

Oracle のように相関サブクエリが唯一の最適プランを立案するお馬鹿な
オプティマイザが存在することは覚えておきます。よって、
「相関サブクエリに置き換えることで遅くなることはあっても速くなることはない」
という私の主張は全面的に撤回させていただきます。失礼いたしました。


Oracle以外においては主張を撤回していないようですが、僕は最初からOracleに限定した話はしていません。たまたま例を挙げたのがOracleだっただけの話。
どのRDBMSにしろ、実行プランや件数やインデックスを考慮しないと判断できないのでは? というのが僕の主張ですんで。
«前のページへ 1|2|3|4 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）