[每日一题] OCP1z0-047 :2013-08-08 相关子查询中EXISTS的使用.....................................28

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/9865711

正确答案是：AC

 

   EXISTS谓词非常简单，它是对一个非空集的测试。如果在其子查询中存在任何行，则返回TRUE，否则为FALSE。该谓词不会返回UNKNOWN结果。EXIST（）谓词语法如下： <EXISTS谓词>：：＝[NOTEXISTS]<表子查询>

 

对exists的测试，看执行计划：

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的执行步骤:

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半连接的伪代码

 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：252803295

学习交流QQ群：
DSI&Core Search  Ⅰ 群：127149411（技术:已满）
DSI&Core Search  Ⅱ 群：177089463（技术:未满）
DSI&Core Search  Ⅲ 群：284596437（技术:未满）
DSI&Core Search  Ⅳ 群：192136702（技术:未满）
DSI&Core Search  Ⅴ 群：285030382（闲聊:未满）

MAIL：[e-mail]

BLOG： http://blog.csdn.net/guoyjoe

WEIBO：http://weibo.com/guoyJoe0218

ITPUB： http://www.itpub.net/space-uid-28460966.html

OCM：   http://education.oracle.com/education/otn/YGuo.HTM