Oracle数据库优化-标量子查询改写优化

1 标量子查询不改写，在子查询内部创建合适的索引，提高查询循环效率。

2.简单版:子查询 优化

SELECT
empno,
ename,
sal,
deptno,
SELECT d.dname
FROM dept d
WHERE d.deptno = e.deptno)
AS dname
FROM emp e; 

改写后:

SELECT e.empno,
 
e.ename,

e.sal,

e.deptno,

D.DNAME AS dname

FROM emp e left join dept d on (d.deptno = e.deptno) 

 1.带聚合函数的标量子查询改写:

SELECT d.department_id,
 
      d.department_name,

        d.location_id,

        NVL ( (SELECT SUM (e.salary)

                 FROM hr.employees e

                WHERE e.department_id = d.department_id),

            0)

           AS sum_sal

   FROM hr.departments d; 

改写后:

 SELECT d.department_id,
                d.department_name,

                d.location_id,

        NVL (e.salary, 0) AS sum_sal

   FROM hr.departments d

        LEFT JOIN ( SELECT e.department_id, SUM (e.salary) salary

                       FROM hr.employees e

                   GROUP BY e.department_id) e

           ON (e.department_id = d.department_id) 

 3.行转列优化标量子查询

SELECT 
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='1' and F2.UMUM=F4101.IMUOM1) AS M3,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='2' and F2.UMUM=F4101.IMUOM1) AS KG,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='3' and F2.UMUM=F4101.IMUOM1) AS KN,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='4' and F2.UMUM=F4101.IMUOM1) AS MH,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='5' and F2.UMUM=F4101.IMUOM1) AS ML,
(SELECT ROUND(v,2) FROM F2 where F2.UMITM=F4101.IMITM AND m='6' and F2.UMUM=F4101.IMUOM1) AS MW,
(SELECT MAX(BPUPRC)/10000 FROM F4106 WHERE TRIM(BPMCU) = 'ZX10' and BPCRCD='CNY' AND F4106.BPLITM = F4101.IMLITM) BPUPRC
FROM F4101, F4102
WHERE F4101.IMITM = F4102.IBITM
AND F4101.IMLITM = F4102.IBLITM
AND IBMCU <> 'ZX10'
AND IMDSC1 NOT LIKE '%CANCEL%'
AND ( IMSRP5 IN ('1', '6') OR IMSRP3 IN ('37', '38') OR IBLITM IN ('506000040') AND TRIM (IBMCU) = 'SF10') ;

改写后

WITH f0
 
      AS (SELECT IMITM, IMUOM1, IMLITM

            FROM F4101, F4102

           WHERE F4101.IMITM = F4102.IBITM

                 AND F4101.IMLITM = F4102.IBLITM

                 AND IBMCU <> 'ZX10'

                 AND IMDSC1 NOT LIKE '%CANCEL%'

                 AND ( IMSRP5 IN ('1', '6')

                      OR IMSRP3 IN ('37', '38')

                      OR IBLITM IN ('506000040') AND TRIM (IBMCU) = 'SF10'))

 SELECT F2.M3 AS M3,

        F2.KG AS KG,

        F2.KN AS KN,

        F2.MH AS MH,

        F2.ML AS ML,

        F2.MW AS MW,

        F4106.BPUPRC AS BPUPRC

   FROM f0

        LEFT JOIN ( SELECT UMITM,

                            UMUM,

                            MAX (CASE WHEN m = '1' THEN ROUND (v, 2) END) AS M3,

                            MAX (CASE WHEN m = '2' THEN ROUND (v, 2) END) AS KG,

                            MAX (CASE WHEN m = '3' THEN ROUND (v, 2) END) AS KN,

                            MAX (CASE WHEN m = '4' THEN ROUND (v, 2) END) AS MH,

                            MAX (CASE WHEN m = '5' THEN ROUND (v, 2) END) AS ML,

                            MAX (CASE WHEN m = '6' THEN ROUND (v, 2) END) AS MW

                       FROM F2

                   GROUP BY UMITM, UMUM) F2

           ON (F2.UMITM = f0.IMITM AND F2.UMUM = f0.IMUOM1)

        LEFT JOIN ( SELECT BPLITM, MAX (BPUPRC) / 10000 BPUPRC

                       FROM F4106

                      WHERE TRIM (BPMCU) = 'ZX10' AND BPCRCD = 'CNY'

                   GROUP BY BPLITM) F4106

           ON (F4106.BPLITM = f0.IMLITM) 

4.不等连接的标量子查询改写(一)

 SELECT a.licenceid,

        a.data_source,

        a.street,

        (SELECT MIN (contdate)

           FROM ct

          WHERE ct.licenceid = a.licenceid

                AND ct.data_source = a.data_source

                AND TRUNC (contdate) >= a.opensaledate)

           AS mincontdate,

        (SELECT MIN (buydate)

           FROM ct

          WHERE ct.licenceid = a.licenceid

                AND ct.data_source = a.data_source

                AND TRUNC (buydate) >= a.opensaledate)

           AS minbuydate

   FROM a 
改写后:

 with ct2 as

 (

     select rowid as rid, 

 min(case when TRUNC (contdate) >= a.opensaledate then contdate end),

 min(case when TRUNC (buydate) >= a.opensaledate then buydate end)

 from ct , a

 where ct.licenceid = a.licenceid

 AND ct.data_source = a.data_source

 group by rowid

 )

 SELECT a.licenceid,

        a.data_source,

        a.street,

        ct2.mincontdate AS mincontdate,

        ct2.minbuydate AS minbuydate

   FROM a left join ct2 on (ct2.rid = a.rowid) 

5.不等连接的标量子查询改写(二)

 SELECT s.stkcode,

        t.mktcode,

        t.stype,

        t.sname,

        (SELECT SUM (c.hs1)

           FROM c

          WHERE c.stkcode = s.stkcode

                AND c.mktcode = t.mktcode

                AND c.calcdate BETWEEN TO_CHAR (

                                          TO_DATE (s.tdate, 'yyyymmdd') - 365,

                                          'yyyymmdd')

                                   AND s.tdate)

           AS f1,

        (SELECT DECODE (COUNT (c.calcdate),

                        0, NULL,

                        SUM (c.hs1) / COUNT (c.calcdate))

           FROM c

          WHERE c.stkcode = s.stkcode

                AND c.mktcode = t.mktcode

                AND c.calcdate BETWEEN TO_CHAR (

                                          TO_DATE (s.tdate, 'yyyymmdd') - 365,

                                          'yyyymmdd')

                                   AND s.tdate)

           AS f2,

        s.tdate

   FROM s, t

  WHERE s.stkcode = t.scode

        AND t.status = 1

        AND t.stype = 2

        AND s.tdate >= TO_NUMBER (TO_CHAR (SYSDATE - 3, 'yyyymmdd')); 
改写后:
 (1).

 with t0 as

 (select rownum as sn, s.*,t.*

     FROM s, t

  WHERE s.stkcode = t.scode

        AND t.status = 1

        AND t.stype = 2

        AND s.tdate >= TO_NUMBER (TO_CHAR (SYSDATE - 3, 'yyyymmdd'))

 )

 SELECT t.stkcode,

        t.mktcode,

       t.stype,

       t.sname,

       ha.f1 AS f1,

       ha.f2 AS f2,

       t.tdate

  FROM t0 t left join ( SELECT t0.sn,

         SUM (c.hs1) f1,

         DECODE (COUNT (c.calcdate), 0, NULL, SUM (c.hs1) / COUNT (c.calcdate))

            f2

    FROM c, t0
  
    WHERE c.stkcode = t0.stkcode
 
          AND c.mktcode = t0.mktcode
 
          AND c.calcdate BETWEEN TO_CHAR (TO_DATE (t0.tdate, 'yyyymmdd') - 365,
 
                                          'yyyymmdd')
 
                             AND t0.tdate
 
 GROUP BY t0.sn) ha on (ha.sn = t0.sn) 
 2).
 
  WITH t0
 
      AS (SELECT ROWNUM AS sn, s.*, t.*
 
            FROM s, t
 
           WHERE s.stkcode = t.scode
 
                 AND t.status = 1
 
                 AND t.stype = 2
 
                 AND s.tdate >= TO_NUMBER (TO_CHAR (SYSDATE - 3, 'yyyymmdd')))
 
   SELECT s.stkcode,
 
          t.mktcode,
 
(         t.stype,
 
          t.sname,
 
          SUM (c.hs1) AS f1,
 
          DECODE (COUNT (c.calcdate), 0, NULL, SUM (c.hs1) / COUNT (c.calcdate))
 
             AS f2,
 
          s.tdate
 
     FROM t0 t LEFT JOIN c ON (c.stkcode = t.stkcode AND c.mktcode = t.mktcode)
 
    WHERE c.calcdate BETWEEN TO_CHAR (TO_DATE (s.tdate, 'yyyymmdd') - 365,
  
                                      'yyyymmdd')
 
                         AND s.tdate
 
 GROUP BY t.sn,
 
          t.stkcode,
 
          t.mktcode,
 
          t.stype,
 
          t.sname,
 
          s.tdate 
 

 标量子查询DISTINCT
 

 SELECT *
 
   FROM (SELECT *
  
           FROM t1
 
          WHERE col4_1 = '00') a
 
        LEFT JOIN
 
        (SELECT col3_1,
 
                col3_2 col1,
 
                col2,
 
                (SELECT DISTINCT col2_1
 
.                  FROM t2
 
                  WHERE col2_2 = a.col2
 
                        AND col2_3 <= TO_DATE ( ('2012-09-30'), 'yyyy-mm-dd')
 
                        AND col2_4 > TO_DATE ('2012-09-30', 'yyyy-mm-dd'))
 
                   col3
 
           FROM t3 a
 
          WHERE col2_3 <= TO_DATE ('2012-09-30', 'yyyy-mm-dd')
 
                AND col2_4 > TO_DATE ('2012-09-30', 'yyyy-mm-dd')) b
 
           ON (a.col4 = b.col3_1) 
 改写后:
 
  SELECT *
 
    FROM (SELECT *
 
             FROM t1
 
           WHERE col4_1 = '00') a
 
         LEFT JOIN
 
         (SELECT col3_1,
 
                 col3_2 col1,
 
                 col2,
 
                 col3.col2_1 col3
 
          FROM t3 a

                LEFT JOIN

                ( SELECT col2_1, col2_2

                     FROM t2

                    WHERE col2_3 <=

                                 TO_DATE ( ('2012-09-30'),

                                          'yyyy-mm-dd')

                          AND col2_4 >

                                 TO_DATE ('2012-09-30', 'yyyy-mm-dd')

                 GROUP BY col2_1, col2_2) col3

                   ON (col3.col2_2 = a.col2)

          WHERE col2_3 <= TO_DATE ('2012-09-30', 'yyyy-mm-dd')

                AND col2_4 > TO_DATE ('2012-09-30', 'yyyy-mm-dd')) b
  
           ON (a.col4 = b.col3_1)