今天实验发现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博客

exists语句的两个性质

最新文章