ITPub博客

首页 > Linux操作系统 > Linux操作系统 > in 与 exists

in 与 exists

原创 Linux操作系统 作者:fulzu 时间:2009-06-24 19:38:48 0 删除 编辑

再次温习了一遍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的问题

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

请登录后发表评论 登录
全部评论

注册时间:2008-03-28

  • 博文量
    68
  • 访问量
    71575