[每日一题] OCP1z0-047 :2013-08-08相关子查询中EXISTS的使用
[每日一题] OCP1z0-047 :2013-08-08相关子查询中EXISTS的使用
正确答案是：AC
EXISTS谓词非常简单，它是对一个非空集的测试。如果在其子查询中存在任何行，则返回TRUE，否则为FALSE。该谓词不会返回UNKNOWN结果。EXIST（）谓词语法如下： <EXISTS谓词>：：＝[NOTEXISTS]<表子查询>
对exists的测试，看执行计划：
[html]
gyj@MYDB> create table t3(id number,name varchar2(100));
Table created.
gyj@MYDB> insert into t3 select level,'a'||level from dual connect by level<1000000;
999999 rows created.
gyj@MYDB> create index idx_t3 on t3(id);
Index created.
gyj@MYDB> create table t4(id number,name varchar2(100));
Table created.
gyj@MYDB> insert into t4 select level,'a'||level from dual connect by level<10;
9 rows created.
gyj@MYDB> commit;
Commit complete.
gyj@MYDB> exec dbms_stats.gather_table_stats(user,'t3',cascade => true);
PL/SQL procedure successfully completed.
gyj@MYDB> exec dbms_stats.gather_table_stats(user,'t4');
PL/SQL procedure successfully completed.
看完计行计划就明白了EXISTS的执行步骤:
[html]
gyj@MYDB> set autot traceonly;
gyj@MYDB> select * from t4 where id in (select id from t3);
9 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1092212754
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 99 | 21 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 9 | 99 | 21 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T4 | 9 | 54 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_T3 | 999K| 4882K| 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID"="ID")
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
20 consistent gets
0 physical reads
0 redo size
723 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
EXISTS半连接的伪代码
[html]
open tab1
while tab1 still has records
fetch record from tab1
result = false
open tab2
while tab2 still has records
fetch record from tab2
if(tab1.record matches tab2.record) then
result = true
exit loop
end if
end loop
close tab2
if (result = true) return tab1 record
end loop
close tab1
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。