ITPub博客

首页 > Linux操作系统 > Linux操作系统 > exists语句的两个性质

exists语句的两个性质

原创 Linux操作系统 作者:to_be_dba 时间:2013-07-05 15:05:46 0 删除 编辑
 
今天实验发现exists有以下两个性质:
(一)当有外键约束的两列上使用exists关联时,由于oracle知道子表中外键约束的字段肯定是在主表中存在的。因此只扫描子表,不需要扫描主表
(严格来说不是exists语句的特性,应该是外键的特性,11g中新加的)
(二)exists中select *和select 任何字符的效果是一样的

实验:
(一)
在scott用户下的emp、dept表上进行实验。emp和dept间存在外键约束。

Connected to Oracle Database 10g Release 10.2.0.1.0
Connected as scott
SQL>  select * from emp a where exists (select * from dept b
  2   where a.deptno=b.deptno);
………… 结果略

执行计划
----------------------------------------------------------
Plan hash value: 3274513678
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     7 |   266 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |         |     7 |   266 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |     7 |   245 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    12 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."DEPTNO"="B"."DEPTNO")

将这两个表导入到11g的数据库环境下。
SQL> select * from emp a where exists (select * from dept b where a.deptno=b.deptno); 
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   609 |    18   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     7 |   609 |    18   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"."DEPTNO" IS NOT NULL)
Note
-----
   - dynamic sampling used for this statement (level=2)

删除外键约束后:
SQL> alter table EMP
  2    drop constraint FK_DEPTNO;
Table altered.
SQL> select * from emp a where exists (select * from dept b where a.deptno=b.deptno);
Execution Plan
----------------------------------------------------------
Plan hash value: 3274513678
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     7 |   700 |    18   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |         |     7 |   700 |    18   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |     7 |   609 |    18   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    52 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."DEPTNO"="B"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)
 
         
(二)exists中select *和select比较
在11g环境下测试
SQL>  select * from emp a where exists (select * from dept b where a.deptno=b.deptno);
………………查询结果略
Execution Plan
----------------------------------------------------------
Plan hash value: 3274513678
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     7 |   700 |    18   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |         |     7 |   700 |    18   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |     7 |   609 |    18   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    52 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."DEPTNO"="B"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
       1293  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed

SQL> select * from emp a where exists (select 0 from dept b where a.deptno=b.deptno);
…………查询结果略

Execution Plan
----------------------------------------------------------
Plan hash value: 3274513678
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     7 |   700 |    18   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI |         |     7 |   700 |    18   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP     |     7 |   609 |    18   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     4 |    52 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."DEPTNO"="B"."DEPTNO")
Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         25  consistent gets
          0  physical reads
          0  redo size
       1293  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          7  rows processed

exists中的子句只是返回一个是否存在的标记,并不其中的查询字段是没有意义的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-765601/,如需转载,请注明出处,否则将追究法律责任。

上一篇: oracle o52 笔记
下一篇: 10053事件初探.TXT
请登录后发表评论 登录
全部评论

注册时间:2011-11-23

  • 博文量
    148
  • 访问量
    390780