几个SQL写法影响执行效率的案例

ZLBH

对于目前的我只有学习的份

sykace

收藏之

aqszhuaihuai

好贴，学习一下，谢谢！

Coast_lichao

select * from some_table where or org_id in (
select 0 from dual
union all
select org_id from org_table start_with org_id = 0 connect by prior org_id = parent_org_id)
您为啥where后面还有个or呢，用org_id=0也可以噻。

caroline0803

楼主好。我这里遇到一个问题，请楼主帮忙看下把，感谢楼主。
如下，需要从多个表中进行统计，如下面的SQL语句所示，EMP1,EMP2,EMP3,EMP4代表，需要从四个业务表中，统计不同维度的值，
应该用哪种方式，能够提高效率呢；
第一种：
SELECT t1.deptno,
         (SELECT COUNT(1) FROM emp1 t WHERE t1.deptno = t.deptno) EMP1_COUNT,
         (SELECT SUM(SAL) FROM emp1 t WHERE t1.deptno = t.deptno) EMP1_SAL,
         (SELECT COUNT(1) FROM emp2 t WHERE t1.deptno = t.deptno) EMP2_COUNT,
         (SELECT SUM(SAL) FROM emp2 t WHERE t1.deptno = t.deptno) EMP2_SAL,
         (SELECT COUNT(1) FROM emp3 t WHERE t1.deptno = t.deptno) EMP3_COUNT,
         (SELECT SUM(SAL) FROM emp3 t WHERE t1.deptno = t.deptno) EMP3_SAL,
         (SELECT COUNT(1) FROM emp4 t WHERE t1.deptno = t.deptno) EMP4_COUNT,
         (SELECT SUM(SAL) FROM emp4 t WHERE t1.deptno = t.deptno) EMP4_SAL
    FROM dept t1
   GROUP BY t1.deptno;
第二种：
explain plan FOR
WITH A AS
(SELECT COUNT(DEPTNO) AS COUNTS_1, DEPTNO, SUM(SAL) AS SAL1 FROM emp1 GROUP BY DEPTNO),
T2 AS
(SELECT COUNT(DEPTNO) AS COUNTS_2, DEPTNO, SUM(SAL) AS SAL2 FROM emp2 GROUP BY DEPTNO),
t3 AS
(SELECT COUNT(DEPTNO) AS COUNTS_3, DEPTNO, SUM(SAL) AS SAL4 FROM emp3 GROUP BY DEPTNO),
t4 AS
(SELECT COUNT(DEPTNO) AS COUNTS_4, DEPTNO, SUM(SAL) AS SAL4 FROM emp4 GROUP BY DEPTNO)
SELECT *
  FROM DEPT T1
  LEFT JOIN A
    ON T1.DEPTNO = A.DEPTNO
  LEFT JOIN T2
    ON T1.DEPTNO = T2.DEPTNO
  LEFT JOIN T3
    ON T1.DEPTNO = T3.DEPTNO
  LEFT JOIN T4
    ON T1.DEPTNO = T4.DEPTNO;
第三种:
SELECT *
  FROM DEPT T1
  LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_1, DEPTNO, SUM(SAL) AS SAL1 FROM emp1 GROUP BY DEPTNO) A
    ON T1.DEPTNO = A.DEPTNO
  LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_2, DEPTNO, SUM(SAL) AS SAL2 FROM emp2 GROUP BY DEPTNO) T2
    ON T1.DEPTNO = T2.DEPTNO
  LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_3, DEPTNO, SUM(SAL) AS SAL4 FROM emp3 GROUP BY DEPTNO) T3
    ON T1.DEPTNO = T3.DEPTNO
  LEFT JOIN (SELECT COUNT(DEPTNO) AS COUNTS_4, DEPTNO, SUM(SAL) AS SAL4 FROM emp4 GROUP BY DEPTNO) T4
    ON T1.DEPTNO = T4.DEPTNO
那种方式可以更好呢，我觉得第一种方式较好，
原帖见：http://www.itpub.net/thread-1854190-1-1.html

anlinew

第一种不好，2、3差不多

xccheese

很不错！

anlinew

ignu 发表于 2010-11-19 20:03
10046的trace  虽然看上去全部等待的是  SQL*Net message from client ，其实这时间  SQL*Net messag ...

很好，我只说了问题现象，你补充了原因“ EVENT的信息是上一步的等待”
通过这个我们也能大概可以猜测一下EVENT体系的一些运行机制，在我们自己的其他系统进行借鉴

dotaddjj

顶了，最近遇见的几个案例是在10.2.0.5下 子查询无法展开，而元凶就是几个or引起的，改写了sql，同样开发人员写sql
SELECT *
  FROM table02 b
WHERE object_id IN (SELECT object_id
                       FROM table02 a
                      WHERE b.object_id > 5000);

也走了一个糟糕的filter，确实是好贴，dba和开发人员都得注意！