请教in和exists的区别

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语句的区别了为什么执行计划相差那么大

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

anlinew · 发表于 2008-4-14 14:33

原帖由 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 · 发表于 2008-4-14 14:37

"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 · 发表于 2008-4-14 14:38

哪个快，哪个慢，在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 · 发表于 2008-4-14 14:38

原帖由 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 · 发表于 2008-4-14 14:42

原帖由 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 · 发表于 2008-4-14 14:50

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

shiri512003 · 发表于 2008-4-14 14:53

偶没帐号了，但是从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 · 发表于 2008-4-14 14:54

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

paulyibinyi · 发表于 2008-4-14 15:00

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

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