oracleのexistsとnot existsの使い方の詳細
4947 ワード
「exists」と「in」の効率の問題を説明する2つの簡単な例があります.
1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;
T 1データ量が小さくT 2データ量が非常に大きい場合,T 1<2) select * from T1 where T1.a in (select T2.a from T2) ;
T 1データ量が非常に大きく,T 2データ量が小さい場合,T 1>>T 2の場合,2)のクエリ効率が高い.
existsの使い方:
1)文の中の色のフォントがある部分に注意して、その意味を理解してください.
ここで、「select 1 from T 2 where T 1.a=T 2.a」は、関連テーブルクエリに相当し、
“select 1 from T1,T2 where T1.a=T2.a”
ただし、1)のカッコ内の文を実行すると、文法が間違っていることが報告されます.これもexistsを使用するときに注意しなければならない点です.
「exists(xxx)」は、カッコ内の文が記録を検出できるかどうか、調べる記録が存在するかどうかを示します.
したがって「select 1」ここの「1」は実はどうでもいいので、「*」に変えても大丈夫です.かっこの中のデータが検索できるかどうか、このような記録があるかどうかだけを気にします.もしあるならば、この1)文のwhere条件は成立します.
inの使い方:
上の例を引き続き引用する
“2) select * from T1 where T1.a in (select T2.a from T2) ”
ここの「in」の後ろの括弧の中の文が検索したフィールドの内容は必ず対応しなければならない.一般的に、T 1とT 2の2つのテーブルのaフィールドの表現の意味は同じであるべきだ.そうしないと、このように調べても意味がない.
例えば、T 1、T 2表には工単番号を表すフィールドがありますが、T 1が工単番号を表すフィールドは「ticketid」、T 2が「id」ですが、その表現の意味は同じで、データフォーマットも同じです.この場合は、2)の表記で次のようになります.
“select * from T1 where T1.ticketid in (select T2.id from T2) ”
Select name from employee where name not in (select name from student);
Select name from employee where not exists (select name from student);
最初のSQL文の実行効率は2番目の文に及ばない.
EXISTSを使用すると、Oracleはまずプライマリ・クエリーをチェックし、サブクエリーを実行して最初の一致が見つかるまで時間を節約します.Oracleは、INサブクエリを実行するときに、最初にサブクエリを実行し、結果リストをインデックス付きテンポラリ・テーブルに保存します.サブクエリを実行する前に、システムはまずプライマリクエリを一時停止し、サブクエリの実行が完了するまで、一時テーブルに保存してからプライマリクエリを実行します.これは,EXISTSを用いることがINを用いることよりも通常のクエリ速度が速い理由である.
FROM: http://blog.sina.com.cn/s/blog_601d1ce30100cyrb.html
EXISTSとINの使用効率の問題、通常の情況の下でexistsを採用してinより効率が高くて、INがインデックスを歩かないため、しかし実際の情況を見て具体的に使用します:INは外見が大きくて内表が小さい情況に適しています;EXISTSは、見た目が小さくて中身が大きい場合に適しています.
==========
1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;
T 1データ量が小さくT 2データ量が非常に大きい場合,T 1<
T 1データ量が非常に大きく,T 2データ量が小さい場合,T 1>>T 2の場合,2)のクエリ効率が高い.
existsの使い方:
1)文の中の色のフォントがある部分に注意して、その意味を理解してください.
ここで、「select 1 from T 2 where T 1.a=T 2.a」は、関連テーブルクエリに相当し、
“select 1 from T1,T2 where T1.a=T2.a”
ただし、1)のカッコ内の文を実行すると、文法が間違っていることが報告されます.これもexistsを使用するときに注意しなければならない点です.
「exists(xxx)」は、カッコ内の文が記録を検出できるかどうか、調べる記録が存在するかどうかを示します.
したがって「select 1」ここの「1」は実はどうでもいいので、「*」に変えても大丈夫です.かっこの中のデータが検索できるかどうか、このような記録があるかどうかだけを気にします.もしあるならば、この1)文のwhere条件は成立します.
inの使い方:
上の例を引き続き引用する
“2) select * from T1 where T1.a in (select T2.a from T2) ”
ここの「in」の後ろの括弧の中の文が検索したフィールドの内容は必ず対応しなければならない.一般的に、T 1とT 2の2つのテーブルのaフィールドの表現の意味は同じであるべきだ.そうしないと、このように調べても意味がない.
例えば、T 1、T 2表には工単番号を表すフィールドがありますが、T 1が工単番号を表すフィールドは「ticketid」、T 2が「id」ですが、その表現の意味は同じで、データフォーマットも同じです.この場合は、2)の表記で次のようになります.
“select * from T1 where T1.ticketid in (select T2.id from T2) ”
Select name from employee where name not in (select name from student);
Select name from employee where not exists (select name from student);
最初のSQL文の実行効率は2番目の文に及ばない.
EXISTSを使用すると、Oracleはまずプライマリ・クエリーをチェックし、サブクエリーを実行して最初の一致が見つかるまで時間を節約します.Oracleは、INサブクエリを実行するときに、最初にサブクエリを実行し、結果リストをインデックス付きテンポラリ・テーブルに保存します.サブクエリを実行する前に、システムはまずプライマリクエリを一時停止し、サブクエリの実行が完了するまで、一時テーブルに保存してからプライマリクエリを実行します.これは,EXISTSを用いることがINを用いることよりも通常のクエリ速度が速い理由である.
FROM: http://blog.sina.com.cn/s/blog_601d1ce30100cyrb.html
EXISTSとINの使用効率の問題、通常の情況の下でexistsを採用してinより効率が高くて、INがインデックスを歩かないため、しかし実際の情況を見て具体的に使用します:INは外見が大きくて内表が小さい情況に適しています;EXISTSは、見た目が小さくて中身が大きい場合に適しています.
==========
SQL> select * from dept where deptno in (select deptno from emp);
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
SQL> select * from dept where exists (select deptno from emp);
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select * from dept where exists (select 1 from dual);
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON