再次温习了一遍in和exists
压力测试拿个东西，说执行计划很怪（主要是cost 怪）
SQL> explain plan for (
  2 
  2  SELECT *
  3    FROM lpgrpedoritem p
  4   WHERE 1 = 1
  5     AND '900000001198' IN (SELECT appntno
  6                          FROM lpgrpcont
  7                         WHERE grpcontno = p.grpcontno)
  8     AND edorstate = '0'
  9  );
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 642139162
--------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |   179 |  8741   (1)| 00:01:
|*  1 |  FILTER            |               |       |       |            |
|*  2 |   TABLE ACCESS FULL| LPGRPEDORITEM |  4329 |   756K|    57   (2)| 00:00:
|*  3 |   TABLE ACCESS FULL| LPGRPCONT     |     1 |    30 |     4   (0)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "LPGRPCONT" "LPGRPCONT" WHERE
              "GRPCONTNO"=:B1 AND "APPNTNO"='900000001198'))
   2 - filter("EDORSTATE"='0')
   3 - filter("GRPCONTNO"=:B1 AND "APPNTNO"='900000001198')
 
18 rows selected

SELECT count(1)
                        FROM lpgrpcont,lpgrpedoritem
                       WHERE lpgrpcont.grpcontno = lpgrpedoritem.grpcontno
                       and lpgrpcont.appntno='900000001198'
 
  COUNT(1)
----------
         8

SELECT count(*)
  FROM lpgrpedoritem p
  COUNT(*)
----------
      8784
结果不言自明

SQL> explain plan for
  2  (
  3 
  3  SELECT *
  4    FROM lpgrpedoritem p
  5   WHERE 1 = 1
  6     AND exists (SELECT 1
  7                          FROM lpgrpcont
  8                         WHERE grpcontno = p.grpcontno
  9                         and lpgrpcont.appntno='900000001198'
 10                          )
 11     AND edorstate = '0'
 12  )
 13  /
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1904886065
--------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |     2 |   418 |    61   (2)| 00:0
|*  1 |  HASH JOIN RIGHT SEMI|               |     2 |   418 |    61   (2)| 00:0
|*  2 |   TABLE ACCESS FULL  | LPGRPCONT     |     2 |    60 |     4   (0)| 00:0
|*  3 |   TABLE ACCESS FULL  | LPGRPEDORITEM |  4329 |   756K|    57   (2)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("GRPCONTNO"="P"."GRPCONTNO")
   2 - filter("LPGRPCONT"."APPNTNO"='900000001198')
   3 - filter("EDORSTATE"='0')
 
17 rows selected

HASH JOIN RIGHT SEMI，从另外的方面说了nest loop 和hash join的问题