[每日一题] 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