请教in和exists的区别

paulyibinyi

那现在的问题就是
select id, role_id, login
  from tb_user e
where Exists (select id
          from (Select ID
                  From tb_role f
                Connect By Prior Id = parent_id
                 Start With Id = 1)
         where id = e.role_id);
         
         
select id, role_id, login
                          from tb_user e
                         where Exists (Select ID
                                  From tb_role f
                                 where e.role_id = f.id
                                Connect By Prior Id = parent_id
                                 Start With Id = 1);

这两个sql语句的区别了 为什么执行计划相差那么大

[ 本帖最后由 paulyibinyi 于 2008-4-14 14:32 编辑 ]

anlinew

原帖由 paulyibinyi 于 2008-4-14 14:25 发表
set autot traceonly
SQL> select id, role_id, login
  2  from tb_user e
  3  where role_id in (
  4  Select
  5  ID
  6  From tb_role f
  7  Connect By Prior Id = parent_id Start With Id = 1)
  8  ;

8369 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=18 Bytes=630
          )

   1    0   HASH JOIN (SEMI) (Cost=14 Card=18 Bytes=630)
   2    1     TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=8556 Byt
          es=188232)

   3    1     VIEW OF 'VW_NSO_1' (Cost=3 Card=18 Bytes=234)
   4    3       CONNECT BY (WITH FILTERING)
   5    4         NESTED LOOPS
   6    5           INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQUE)
           (Cost=1 Card=1 Bytes=5)

   7    5           TABLE ACCESS (BY USER ROWID) OF 'tb_role'
   8    4         NESTED LOOPS
   9    8           BUFFER (SORT)
  10    9             CONNECT BY PUMP
  11    8           TABLE ACCESS (BY INDEX ROWID) OF 'tb_role' (
          Cost=3 Card=18 Bytes=162)

  12   11             INDEX (RANGE SCAN) OF 'INX_tb_role_PARENT_
          ID' (NON-UNIQUE) (Cost=1 Card=18)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10250  consistent gets
          0  physical reads
          0  redo size
     224163  bytes sent via SQL*Net to client
       6553  bytes received via SQL*Net from client
        559  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
       8369  rows processed

把exists 的写法改成如下 和in的效果是一样的

SQL> select id, role_id, login
  2    from tb_user e
  3   where Exists (select id
  4            from (Select ID
  5                    From tb_role f
  6                  Connect By Prior Id = parent_id
  7                   Start With Id = 1)
  8           where id = e.role_id);

8369 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=18 Bytes=630
          )

   1    0   HASH JOIN (SEMI) (Cost=14 Card=18 Bytes=630)
   2    1     TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=8556 Byt
          es=188232)

   3    1     VIEW OF 'VW_SQ_1' (Cost=3 Card=18 Bytes=234)
   4    3       VIEW (Cost=3 Card=18 Bytes=234)
   5    4         CONNECT BY (WITH FILTERING)
   6    5           NESTED LOOPS
   7    6             INDEX (UNIQUE SCAN) OF 'PK_tb_role' (UNIQU
          E) (Cost=1 Card=1 Bytes=5)

   8    6             TABLE ACCESS (BY USER ROWID) OF 'tb_role'
   9    5           NESTED LOOPS
  10    9             BUFFER (SORT)
  11   10               CONNECT BY PUMP
  12    9             TABLE ACCESS (BY INDEX ROWID) OF 'tb_role'
           (Cost=3 Card=18 Bytes=162)

  13   12               INDEX (RANGE SCAN) OF 'INX_tb_role_PAREN
          T_ID' (NON-UNIQUE) (Cost=1 Card=18)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10250  consistent gets
          0  physical reads
          0  redo size
     224163  bytes sent via SQL*Net to client
       6553  bytes received via SQL*Net from client
        559  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
       8369  rows processed

SQL>

这样就做了unnesting了，什么情况下会unnesting还是不得而知
看不到metalink的那几篇文章，很是郁闷

select id, role_id, login
      from tb_user e,(Select ID
                      From tb_role f
                    Connect By Prior Id = parent_id
                    Start With Id = 1)d
             where d.id = e.role_id;

改成这样岂不更直接

anlinew

"Please note that the decision to unnest a subquery is not costed in  Oracle9i. The decision to unnest a subquery is taken based on a set of  heuristics"

"The UNNEST hint tells Oracle to check the subquery block for validity only. If the
subquery block is valid, then subquery unnesting is enabled without checking the
heuristics or costs."

再次恳请达人能提供下面的文档
@ INTERNAL Note 250646.1 SubQuery Unnesting - IN SubQuery
@ INTERNAL Note 258228.1 SubQuery Unnesting - NOT IN SubQuery
@ INTERNAL Note 258676.1 SubQuery Unnesting - EXISTS SubQuery .

microsoft_fly

哪个快，哪个慢，在9i里，LZ的这个例子可以模拟一下，
create table tb_role(id number,parent_id number,rolename varchar2(30))
insert into tb_role(id,parent_id,rolename)values(1,0,'all')
insert into tb_role(id,parent_id,rolename)
select (rownum+2) as id,2 as parent_id,o_type as rolename from
(
  select distinct(object_type) as o_type
  from dba_objects where object_type
)
create table tb_user as
select a.object_id as id,b.id as role_id,a.object_name as login
from  dba_objects a inner join
tb_role b on a.object_type=b.rolename
看看，然后再用LZ的语句,数据外大内小，用in 比exists大，呵呵，好象in更适合（由此得出结论: exits适合内小外大的查询，in适合内大外小的查询----这个结论刚好相反），在10G里，很多时候执行计划是一样的

paulyibinyi

原帖由 anlinew 于 2008-4-14 14:33 发表

这样就做了unnesting了，什么情况下会unnesting还是不得而知
看不到metalink的那几篇文章，很是郁闷

select id, role_id, login
      from tb_user e,(Select ID
                      From tb_role f
                    Connect By Prior Id = parent_id
                    Start With Id = 1)d
             where d.id = e.role_id;

改成这样岂不更直接

恩 tks ,这样是可以,但一致性读还是大点
SQL> select e.id, e.role_id, e.login
  2        from tb_client e,(Select ID
  3                        From tb_admin_role f
  4                      Connect By Prior Id = parent_id
  5                      Start With Id = 1)d
  6               where d.id = e.role_id;

8369 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=18 Bytes=630
          )

   1    0   HASH JOIN (Cost=12 Card=18 Bytes=630)
   2    1     VIEW (Cost=3 Card=18 Bytes=234)
   3    2       CONNECT BY (WITH FILTERING)
   4    3         NESTED LOOPS
   5    4           INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE' (UNIQUE)
           (Cost=1 Card=1 Bytes=5)

   6    4           TABLE ACCESS (BY USER ROWID) OF 'TB_ADMIN_ROLE'
   7    3         NESTED LOOPS
   8    7           BUFFER (SORT)
   9    8             CONNECT BY PUMP
  10    7           TABLE ACCESS (BY INDEX ROWID) OF 'TB_ADMIN_ROLE' (
          Cost=3 Card=18 Bytes=162)

  11   10             INDEX (RANGE SCAN) OF 'INX_TB_ADMIN_ROLE_PARENT_
          ID' (NON-UNIQUE) (Cost=1 Card=18)

  12    1     TABLE ACCESS (FULL) OF 'TB_CLIENT' (Cost=8 Card=8556 Byt
          es=188232)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10803  consistent gets         
         0  physical reads
          0  redo size
     224163  bytes sent via SQL*Net to client
       6553  bytes received via SQL*Net from client
        559  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
       8369  rows processed

目前改为 in的方式

microsoft_fly

原帖由 paulyibinyi 于 2008-4-14 14:30 发表
那现在的问题就是
select id, role_id, login
  from tb_user e
where Exists (select id
          from (Select ID
                  From tb_role f
                Connect By Prior Id = parent_id
                 Start With Id = 1)
         where id = e.role_id);
         
         
select id, role_id, login
                          from tb_user e
                         where Exists (Select ID
                                  From tb_role f
                                 where e.role_id = f.id
                                Connect By Prior Id = parent_id
                                 Start With Id = 1);

这两个sql语句的区别了 为什么执行计划相差那么大

我觉得很正常，如果一样，就没必要同时有in和exists，更何况，语句一样执行计划都会不一样，关键上如何调优最重要！我倒觉得你这里即使用in为什么consistent gets还有点大！

paulyibinyi

查出来的数据都有8000多条 1万多点consistent gets 应该正常吧

shiri512003

偶没帐号了，但是从92的官方文档有以下说法：
[php]Unnesting of Nested Subqueries
Subqueries are "nested" when they appear in the WHERE clause of the parent statement. When Oracle evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins.

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the subquery's immediate outer query block.

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:

Uncorrelated IN subqueries
IN and EXISTS correlated subqueries, as long as they do not contain aggregate functions or a GROUP BY clause
You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:

You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery.
You can unnest other subqueries by specifying the UNNEST hint in the subquery.

See Also:
Chapter 2, "Basic Elements of Oracle SQL" for information on hints


[/php]

microsoft_fly

呵呵那么能否再优化一下呢

paulyibinyi

原帖由 microsoft_fly 于 2008-4-14 14:54 发表
呵呵那么能否再优化一下呢

谢谢,我现在也在想,能否针对以上语句提供更好的优化思路,有好的方法希望大家提出来