请教in和exists的区别

anlinew

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

应该是subquery本身的consistent gets就大，楼主可以试试

mihawk

楼主看看这两个语句的逻辑读.
select /*+full(a)*/
        count(*)
from tb_user a
/

Select ID
From tb_role f
where e.role_id = f.id
Connect By Prior Id = parent_id
Start With Id = 1
/

[ 本帖最后由 mihawk 于 2008-4-14 15:10 编辑 ]

paulyibinyi

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

应该是subquery本身的consistent gets就大，楼主可以试试

谢谢确实是

SQL> Select ID
  2                        From tb_role f
  3                      Connect By Prior Id = parent_id
  4                      Start With Id = 1;

8974 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=18 Bytes=162)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS
   3    2       INDEX (UNIQUE SCAN) OF 'PK_TB_ROLE' (UNIQUE) (Co
          st=1 Card=1 Bytes=5)

   4    2       TABLE ACCESS (BY USER ROWID) OF 'TB_ROLE'
   5    1     NESTED LOOPS
   6    5       BUFFER (SORT)
   7    6         CONNECT BY PUMP
   8    5       TABLE ACCESS (BY INDEX ROWID) OF 'TB_ROLE' (Cost
          =3 Card=18 Bytes=162)

   9    8         INDEX (RANGE SCAN) OF 'INX_TB_ADMIN_ROLE_PARENT_ID'
          (NON-UNIQUE) (Cost=1 Card=18)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10066  consistent gets
          0  physical reads
          0  redo size
      97091  bytes sent via SQL*Net to client
       7004  bytes received via SQL*Net from client
        600  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
       8974  rows processed
那应该没有再优化的余地了吧

paulyibinyi

原帖由 mihawk 于 2008-4-14 15:08 发表
楼主看看这两个语句的逻辑读.
select /*+full(a)*/
        count(*)
from tb_user a
/

Select ID
From tb_role f
where e.role_id = f.id
Connect By Prior Id = parent_id
Start With Id = 1
/

Select ID
From tb_role f
where e.role_id = f.id
Connect By Prior Id = parent_id
Start With Id = 1
/  
这个sql有问题
或者你想要的是
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)的
的逻辑读

paulyibinyi

SQL> select /*+full(a)*/
  2             count(*)
  3     from tb_user a
  4     ;

  COUNT(*)
----------
      8556


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'tb_user' (Cost=8 Card=8556)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        184  consistent gets
          0  physical reads
          0  redo size
        308  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

anlinew

原帖由 paulyibinyi 于 2008-4-14 15:10 发表

谢谢确实是

SQL> Select ID
  2                        From tb_role f
  3                      Connect By Prior Id = parent_id
  4                      Start With Id = 1;

8974 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=18 Bytes=162)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS
   3    2       INDEX (UNIQUE SCAN) OF 'PK_TB_ROLE' (UNIQUE) (Co
          st=1 Card=1 Bytes=5)

   4    2       TABLE ACCESS (BY USER ROWID) OF 'TB_ROLE'
   5    1     NESTED LOOPS
   6    5       BUFFER (SORT)
   7    6         CONNECT BY PUMP
   8    5       TABLE ACCESS (BY INDEX ROWID) OF 'TB_ROLE' (Cost
          =3 Card=18 Bytes=162)

   9    8         INDEX (RANGE SCAN) OF 'INX_TB_ADMIN_ROLE_PARENT_ID'
          (NON-UNIQUE) (Cost=1 Card=18)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10066  consistent gets
          0  physical reads
          0  redo size
      97091  bytes sent via SQL*Net to client
       7004  bytes received via SQL*Net from client
        600  SQL*Net roundtrips to/from client
         17  sorts (memory)
          0  sorts (disk)
       8974  rows processed
那应该没有再优化的余地了吧

那就要看业务逻辑了

[ 本帖最后由 anlinew 于 2008-4-14 15:26 编辑 ]

paulyibinyi

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

那就要看业务逻辑了
这个貌似可以MV

呵呵 谢谢 目前系统都没用到mv 暂时先这样

mihawk

这个用全表扫描试试

Select /*+full(f)*/ID
From tb_role f
where e.role_id = f.id
Connect By Prior Id = parent_id
Start With Id = 1
/

paulyibinyi

原帖由 mihawk 于 2008-4-14 15:26 发表
这个用全表扫描试试

Select /*+full(f)*/ID
From tb_role f
where e.role_id = f.id
Connect By Prior Id = parent_id
Start With Id = 1
/

呵呵 谢谢降下来了

SQL> Select /*+full(f)*/ID
  2  From tb_admin_role f
  3  Connect By Prior Id = parent_id
  4  Start With Id = 1;

8974 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8974 Bytes=80
          766)

   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS
   3    2       INDEX (UNIQUE SCAN) OF 'PK_TB_ADMIN_ROLE' (UNIQUE) (Co
          st=1 Card=1 Bytes=5)

   4    2       TABLE ACCESS (BY USER ROWID) OF 'TB_ADMIN_ROLE'
   5    1     HASH JOIN
   6    5       CONNECT BY PUMP
   7    5       TABLE ACCESS (FULL) OF 'TB_ADMIN_ROLE' (Cost=5 Card=89
          74 Bytes=80766)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        739  consistent gets          0  physical reads
          0  redo size
      97091  bytes sent via SQL*Net to client
       7004  bytes received via SQL*Net from client
        600  SQL*Net roundtrips to/from client
          9  sorts (memory)
          0  sorts (disk)
       8974  rows processed

SQL>

mihawk

整个语句试试这个(语法等效的, 但是强制使用了hash和full table)

select /*+ordered use_hash(e x)*/
        e.id, e.role_id, e.login
from tb_user e ,
(Select /*+full(f)*/distinct ID
From tb_role f
Connect By Prior Id = parent_id
Start With Id = 1) x
where e.role_id = x.id
/