- PR -

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

«前のページへ 1|2|3|4 次のページへ»
投稿者投稿内容
うきょきょ
常連さん
会議室デビュー日: 2004/01/09
投稿数: 44
投稿日時: 2004-08-31 14:10
皆さん、レスありがとうございます。

目的は達した”ぽい”ので、このまま進めます。
ただカーニーさんの

引用:

引用:

回避策としては、
Exist の場合は、単純結合

これはDISTINCTを使わないと等価にならない場合があるので要注意。


は、どういう時に必要なのでしょうか？
例の場合、
select DISTINCT Field from TestA where not exists
(select DISTINCT Field from TestB where TestA.Field = TestB.Field)

こうすべきなのでしょうか？
＃Fieldが１つしか無いから、alterして試してみます。

Ｐ．Ｓ．

エンドユーザーによって、OracleやSQL Server等色々と異なるため、
かじる程度にしか触ってないと、正直パフォーマンスまでは手が回らなかったりします。

用語も普通(?)のアプリケーション開発には使わないのが多々あり混乱します。
＃まー開発言語でも同じ事がいえますが...

でえっと、何が言いたいかというとOracleでもSQL Serverでも同じだった気がしますが、
「単純結合」と「外部結合」の違いを勉強し直してきます^^;
＃"inner join"と"outer join"でしたよね。確か。

---------------------------------------------------------------------
追記：試しました。後学のため残しておきます。
/**
** --Drop Table
** Drop Table TestA
** Drop Table TestB
**
** go
**
** --Create Table
** Create Table TestA(
** FieldA char(1) Not Null,
** FieldB char(2) Not Null,
** Primary key(FieldA, FieldB)
** )
** Create Table TestB(
** FieldA char(1) Not Null,
** FieldB char(2) Not Null,
** Primary key(FieldA, FieldB)
** )
**
** go
**
** --TestA(Insert)
** Insert Into TestA(FieldA, FieldB) Values ('1', 'A')
** Insert Into TestA(FieldA, FieldB) Values ('1', 'B')
** Insert Into TestA(FieldA, FieldB) Values ('2', 'A')
** Insert Into TestA(FieldA, FieldB) Values ('2', 'B')
**
** --TestB(Insert)
** Insert Into TestB(FieldA, FieldB) Values ('1', 'C')
** Insert Into TestB(FieldA, FieldB) Values ('1', 'C')
** Insert Into TestB(FieldA, FieldB) Values ('3', 'D')
** Insert Into TestB(FieldA, FieldB) Values ('3', 'D')
**/

--Select
Select 'TestA' TableName, * From TestA
Select 'TestB' TableName, * From TestB
Select 'not DISTINCT1' Note, * from TestA where not exists
(select * from TestB where TestA.FieldA = TestB.FieldA)
Select 'not DISTINCT2' Note, FieldA from TestA where not exists
(select FieldA from TestB where TestA.FieldA = TestB.FieldA)
Select DISTINCT 'DISTINCT1' Note, * from TestA where not exists
(select * from TestB where TestA.FieldA = TestB.FieldA)
Select DISTINCT 'DISTINCT2' Note, FieldA from TestA where not exists
(select FieldA from TestB where TestA.FieldA = TestB.FieldA)




[ メッセージ編集済み 編集者: うきょきょ 編集日時 2004-08-31 14:34 ]
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2004-08-31 16:28
引用:

うきょきょさんの書き込み (2004-08-31 14:10) より:
ただカーニーさんの

引用:

引用:

回避策としては、
Exist の場合は、単純結合

これはDISTINCTを使わないと等価にならない場合があるので要注意。


は、どういう時に必要なのでしょうか？


時間ないので、どこにDISTINCTつけると等価になるかまでは書けませんが。

コード:
create table TestA(FieldA char(1));
create table TestB(FieldA char(1));
insert into TestA values('1');
insert into TestA values('2');
insert into TestA values('3');
insert into TestB values('1');
insert into TestB values('1');
insert into TestB values('3');
commit;

select TestA.* from TestA where exists(select 'X' from TestB where TestB.FieldA = TestA.FieldA);

FIELDA
------
1
3

select TestA.* from TestA, TestB where TestB.FieldA = TestA.FieldA;

FIELDA
------
1
1
3


情報処理試験のテクニカルエンジニア(データベース)なんかでも出題されたりするので、DBの種類には依存しない問題です。
はにまる
ぬし
会議室デビュー日: 2003/12/19
投稿数: 969
お住まい・勤務地: 誤字脱字の国
投稿日時: 2004-09-01 09:35
はにまるです。
再投稿のチャンスがあったので再度御礼です。
引用:

未記入さんの書き込み (2004-08-30 23:13) より:
相関サブクエリ、ね。

フォローありがとうございます。

引用:

カーニーさんの書き込み (2004-08-31 11:28) より:
サブクエリがパフォーマンス劣化の原因になるかどうかは状況次第で、それほど嫌われているわけではありません。

状況次第と云われますが、サブクエリは
サーバ側での一括処理により「ネットワーク負荷の軽減」、「各プロセスのオーバヘッドの減少」
又、ＤＢが既に検索結果をメモリ確保している事による即時でデータ活用が出来る状態になる為、
「単純クエリ→個別プログラム→単純クエリ」の処理形態に比べパフォーマンス改善になる事が多いです。

また、問題視しているのは「サブクエリ」では無く「相関サブクエリ」の事です。
状況次第で良くも悪くなるもなるならば、別ですが、
相関サブクエリは結合処理に比べどの様な状況次第で良くなるのでしょうか？

後、インデックスの話を引っ張り出していますが、実業務上インデックスの
話で「状況次第」といえると云う事は、インデックスが利用される条件、
各種インデックスが利用される優先順位が理解出来ての話です。
もし、そのあたりを理解している技術者が、「状況次第」という言葉で
結合処理より相関サブクエリを選択するとは考え難いのですが、
どの様に考えられていますか？
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2004-09-02 00:47
誤解だったらごめんなさい。
はにまるさんの意見は、「相関サブクエリ＝ネステッドループ結合で実行される」という前提に立っているように思えてしまうのです。

SQLは宣言型言語なので、性能の優劣を議論するのであれば表面的な記述方法ではなく、あくまでも実行プランで考えるべきです。

相関サブクエリと結合処理が互いに置き換え可能な場合なら、オプティマイザが内部で表現を変換して、両者に対して同じ実行プランを立てることは大いに考えられます。

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

当然ながら、置き換え不可能な場合は性能の優劣は意味を持ちません。
＃ ホスト変数やワークテーブルを使うなどして、複数のSQLに分割するケースは考慮してません

あと、「状況次第」というのはオプティマイザがどの程度までの置き換えを考慮してくれるのか、その限度次第ということです。そして目の前の問題はその限度に引っかかるのかどうなのかということです。
インデックスの有無が影響することもあるでしょうし、使用するRDBMSやオプティマイザモードによることもあるでしょう。

こんな感じで回答になっていますか?
はにまる
ぬし
会議室デビュー日: 2003/12/19
投稿数: 969
お住まい・勤務地: 誤字脱字の国
投稿日時: 2004-09-02 10:16
はにまるです。

引用:

カーニーさんの書き込み (2004-09-02 00:47) より:
SQLは宣言型言語なので、性能の優劣を議論するのであれば表面的な記述方法ではなく、あくまでも実行プランで考えるべきです。

そうですね。ただ私は「べき」とまで言い切れませんが．．．

引用:

相関サブクエリと結合処理が互いに置き換え可能な場合なら、オプティマイザが内部で表現を変換して、両者に対して同じ実行プランを立てることは大いに考えられます。

「考えられます」では、「実行プランで考えるべき」の意味が弱くなります
「実行プランで考えるべき」ですから「実行プランを理解するべき」になりません？

尚、私がしる限りでは、ＯＲＡＣＬＥではそんな置き換えはしません。
どのＲＤＢＭＳならば置き換えされますか？

引用:

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

そうですね。「オプティマイザが内部で表現を変換」するならば。
未記入
大ベテラン
会議室デビュー日: 2003/11/24
投稿数: 121
投稿日時: 2004-09-02 11:06
引用:

相関サブクエリと結合処理が互いに置き換え可能な場合なら、オプティマイザが内部で表現を変換して、両者に対して同じ実行プランを立てることは大いに考えられます。

・現実として、そこまで賢いオプティマイザを見たことが無い。
・記述されたクエリの書式に基づいてプランを立てるオプティマイザが多い。

オプティマイザの計画を指示するためのヒント句が使用できるデータベースってありますよね？
「ネステッドループしなさいよ」とか。クエリの記述によって、プランが変わるという点では、
相関サブクエリなどのクエリ記述も、このヒント句と同じよう意味合いを持っていると
考えて良いのではないかと思います。

もちろん、完全に等価でもっとも最適化されたプランを立ててくれるオプティマイザがあれば、
それはそれですばらしいと思います。(よろしければ、その製品を教えてください。)
私の知る限り現在の主要なデータベースはクエリの記述自体をプラン立案のヒントとして
利用しています。

よって、「相関サブクエリがパフォーマンス低下の要因になりやすい」というのは事実だと思います。
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2004-09-02 12:21
手元にOracle9i R2しかないので、それで試しています。

デモ用のSCOTTスキーマのemp表とdept表で、dept表のdeptno列には主キー制約を作成しています。

コード:

(変換される例)

select emp.*

from emp

where emp.deptno in (select dept.deptno

                     from dept

                     where dept.deptno = emp.deptno);



実行計画

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'EMP'

   3    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)



(変換後と同じクエリ)

select emp.*

from dept, emp

where emp.deptno = dept.deptno;



実行計画

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE

   1    0   NESTED LOOPS

   2    1     TABLE ACCESS (FULL) OF 'EMP'

   3    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE)



(変換を無効にする)

select /*+ no_merge */ emp.*

from emp

where emp.deptno = (select dept.deptno

                     from dept

                     where dept.deptno = emp.deptno);



実行計画

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=7 Bytes=609)

   1    0   FILTER

   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=7 Bytes=609)

   3    1     INDEX (UNIQUE SCAN) OF 'DEPT_PK' (UNIQUE) (Cost=1 Card=1 Bytes=13)


これでいいですか?
SQLの変換のことは、9iであればチューニングマニュアルの第2章です。

他のRDBMSはどうなんでしょう?

***
ごめんなさい、最後の no_merge のやつは間違えた。
in じゃなくて = になってる。
時間がないのですぐには再検証できないけど、言いたかったのは、SQL表現がそのまま実行プランに反映されるんだったら Nested Loop じゃなくて FILTER になるだろう、ということです。

[ メッセージ編集済み 編集者: カーニー 編集日時 2004-09-02 12:56 ]
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2004-09-02 13:08
失礼、マニュアル見たらすぐ分かりました。

コード:
select emp.*
from emp
where emp.deptno in (select /*+ no_unnest */ dept.deptno
                     from dept
                     where dept.deptno = emp.deptno);

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


ついでなのでこっちにも回答します。

引用:

はにまるさんの書き込み (2004-09-02 10:16) より:
引用:

相関サブクエリと結合処理が互いに置き換え可能な場合なら、オプティマイザが内部で表現を変換して、両者に対して同じ実行プランを立てることは大いに考えられます。

「考えられます」では、「実行プランで考えるべき」の意味が弱くなります
「実行プランで考えるべき」ですから「実行プランを理解するべき」になりません？


質問の意味を取り違えているかもしれませんが・・・
最初のやつと次のやつとでは、違う意味で「考える」を使っています。
後者は「オプティマイザが変換する『可能性がある』」という意味です。

可能性があるからこそ、実際のプランはどうなっているのかに基づいて性能的な優劣を「考えるべき」というのが僕の意見です。
«前のページへ 1|2|3|4 次のページへ»

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