1.原语句：

点击(此处)折叠或打开

SELECT *
FROM cm_jc_f_spf_bonus_gf t
WHERE (j_match_id, createtime) IN (
SELECT j_match_id, MAX (t1.createtime)
FROM cm_jc_f_spf_bonus_gf t1
WHERE lotterycode = '108'
AND (lotterperiod = '2014-04-17')
GROUP BY j_match_id, flag)

执行计划如下：
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 96 | 3039 (3)| 00:00:37 |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 96 | 3039 (3)| 00:00:37 |
| 2 | VIEW | VW_NSO_1 | 1631 | 29358 | 71 (2)| 00:00:01 |
| 3 | HASH GROUP BY | | 1631 | 50561 | 71 (2)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CM_JC_F_SPF_BONUS_GF | 1631 | 50561 | 70 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_CM_JC_F_SPF_BONUS_GF | 1636 | | 9 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | CM_JC_F_SPF_BONUS_GF | 1143K| 85M| 2957 (2)| 00:00:36 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("J_MATCH_ID"="$nso_col_1" AND "CREATETIME"="$nso_col_2")
5 - access("LOTTERPERIOD"='2014-04-17' AND "LOTTERYCODE"='108')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14380 consistent gets
12710 physical reads
0 redo size
3187 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26 rows processed

因为lotterperiod和lotterycode上有个组合索引，所以子select的数据检索还是很快的，cost值只有9，主要慢在第6步的全表扫描上。本来以为在join列createtime和j_match_id上也建一个索引可以避免的，很可惜一点用也没有。后来才想到既然主表和子表都是同一个表在做join，既然子表的条件可以走索引，那么为什么不把同样的条件也加到主表上呢（询问过开发人员，这样的改动和之前的在业务需求上是等价的）。

修改后的sql:

点击(此处)折叠或打开

SELECT *
FROM cm_jc_f_spf_bonus_gf t
WHERE (j_match_id, createtime) IN (
SELECT j_match_id, MAX (t1.createtime)
FROM cm_jc_f_spf_bonus_gf t1
WHERE lotterycode = '108'
AND (lotterperiod = '2014-04-17')
GROUP BY j_match_id, flag)
AND lotterycode = '108'
AND (lotterperiod = '2014-04-17')

-----------------------------------------------------------------------------------------------------------
| Id | Operation                      | Name                     | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                          |     1 |    96 |   142   (2)| 00:00:02 |
|* 1 | HASH JOIN RIGHT SEMI          |                          |     1 |    96 |   142   (2)| 00:00:02 |
|   2 |   VIEW                         | VW_NSO_1                 | 1631 | 29358 |    71   (2)| 00:00:01 |
|   3 |    HASH GROUP BY               |                          | 1631 | 50561 |    71   (2)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CM_JC_F_SPF_BONUS_GF     | 1631 | 50561 |    70   (0)| 00:00:01 |
|* 5 |      INDEX RANGE SCAN          | IDX_CM_JC_F_SPF_BONUS_GF | 1636 |       |     9   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | CM_JC_F_SPF_BONUS_GF     | 1631 |   124K|    70   (0)| 00:00:01 |
|* 7 |    INDEX RANGE SCAN            | IDX_CM_JC_F_SPF_BONUS_GF | 1636 |       |     9   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("J_MATCH_ID"="$nso_col_1" AND "CREATETIME"="$nso_col_2")
   5 - access("LOTTERPERIOD"='2014-04-17' AND "LOTTERYCODE"='108')
   7 - access("LOTTERPERIOD"='2014-04-17' AND "LOTTERYCODE"='108')
Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        129 consistent gets
          0 physical reads
          0 redo size
       3173 bytes sent via SQL*Net to client
        503 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         26 rows processed

可以看到这样修改之后cost值和逻辑读都大幅度降低了

2.另一个语句是这样的，在一张订单表上要统计各个代理商的销售额，这个本来其实很简单的，sum加group by就行了。但由于当初设计的问题，有几个代理商在系统里面有好几个不同的代理商编号，但在统计结算的时候他们都是算一家的。原先的sql是把这几个代理商单独拎出来统计然后再union all合并：

点击(此处)折叠或打开

SELECT a.cagentcode, b.cagentname, SUM (a.iamount)
FROM cm_orderinfo_report a, cm_agent_info b
WHERE a.cagentcode = b.cagentcode
AND a.coperdate >= TRUNC (SYSDATE) - 1
AND a.coperdate < TRUNC (SYSDATE)
AND a.iscprocflag = 0
AND a.cagentcode NOT LIKE '10771%'
AND a.cagentcode NOT LIKE '60%'
AND a.cagentcode != '1100'
GROUP BY a.cagentcode, b.cagentname /*order by a.cagentcode*/
UNION ALL
SELECT '1100' AS cagentcode, '广西网站' AS cagentname,
NVL (SUM (iamount), 0)
FROM cm_orderinfo_report
WHERE coperdate >= TRUNC (SYSDATE) - 1
AND coperdate < TRUNC (SYSDATE)
AND iscprocflag = 0
AND (cagentcode = '1100' OR cagentcode LIKE '60%')
UNION ALL
SELECT '1077106', '广西移动', NVL (SUM (iamount), 0)
FROM cm_orderinfo_report
WHERE coperdate >= TRUNC (SYSDATE) - 1
AND coperdate < TRUNC (SYSDATE)
AND iscprocflag = 0
AND cagentcode LIKE '10771%'
ORDER BY cagentcode;

-----------------------------------------------------------------------------------------------------------
| Id | Operation                        | Name                   | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                        |     4 |   122 |    30 (10)| 00:00:01 |
|   1 | SORT ORDER BY                   |                        |     4 |   122 |    29 (63)| 00:00:01 |
|   2 |   UNION-ALL                      |                        |       |       |            |          |
|   3 |    HASH GROUP BY                 |                        |     2 |    80 |    13 (16)| 00:00:01 |
|* 4 |     FILTER                       |                        |       |       |            |          |
|* 5 |      HASH JOIN                   |                        |     2 |    80 |    12   (9)| 00:00:01 |
|* 6 |       TABLE ACCESS BY INDEX ROWID| CM_ORDERINFO_REPORT    |     4 |    84 |     8   (0)| 00:00:01 |
|* 7 |        INDEX RANGE SCAN          | INDEX_REPORT_COPERDATE |     6 |       |     3   (0)| 00:00:01 |
|* 8 |       TABLE ACCESS FULL          | CM_AGENT_INFO          |    86 | 1634 |     3   (0)| 00:00:01 |
|   9 |    SORT AGGREGATE                |                        |     1 |    21 |            |          |
|* 10 |     FILTER                       |                        |       |       |            |          |
|* 11 |      TABLE ACCESS BY INDEX ROWID | CM_ORDERINFO_REPORT    |     1 |    21 |     8   (0)| 00:00:01 |
|* 12 |       INDEX RANGE SCAN           | INDEX_REPORT_COPERDATE |     6 |       |     3   (0)| 00:00:01 |
| 13 |    SORT AGGREGATE                |                        |     1 |    21 |            |          |
|* 14 |     FILTER                       |                        |       |       |            |          |
|* 15 |      TABLE ACCESS BY INDEX ROWID | CM_ORDERINFO_REPORT    |     2 |    42 |     8   (0)| 00:00:01 |
|* 16 |       INDEX RANGE SCAN           | INDEX_REPORT_COPERDATE |     6 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TRUNC(    6 - filter("A"."CAGENTCODE" NOT LIKE '10771%' AND "A"."ISCPROCFLAG"=0 AND
              "A"."CAGENTCODE"<>'1100' AND "A"."CAGENTCODE" NOT LIKE '60%')
   7 - access("A"."COPERDATE">=TRUNC(SYSDATE@!)-1 AND "A"."COPERDATE"    8 - filter("B"."CAGENTCODE"<>'1100')
10 - filter(TRUNC( 12 - access("COPERDATE">=TRUNC(SYSDATE@!)-1 AND "COPERDATE" 14 - filter(TRUNC( 16 - access("COPERDATE">=TRUNC(SYSDATE@!)-1 AND "COPERDATE" Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      68191 consistent gets
          0 physical reads
          0 redo size
       1485 bytes sent via SQL*Net to client
        503 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         26 rows processed

其实如此的union案例是有一个比较经典的解决方法的，那就是用case when来代替：

点击(此处)折叠或打开

SELECT a.cagentcode, b.cagentname, a.iamount
FROM (SELECT (CASE
WHEN cagentcode = '1100' OR cagentcode LIKE '60%'
THEN '1100'
WHEN cagentcode LIKE '10771%'
THEN '1077106'
ELSE cagentcode
END
) AS cagentcode,
SUM (iamount) AS iamount
FROM cm_orderinfo_report
WHERE coperdate >= TRUNC (SYSDATE - 1)
AND coperdate < TRUNC (SYSDATE)
AND iscprocflag = 0
GROUP BY (CASE
WHEN cagentcode = '1100' OR cagentcode LIKE '60%'
THEN '1100'
WHEN cagentcode LIKE '10771%'
THEN '1077106'
ELSE cagentcode
END
)) a,
cm_agent_info b
WHERE a.cagentcode = b.cagentcode
ORDER BY a.cagentcode

---------------------------------------------------------------------------------------------------------
| Id | Operation                      | Name                   | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                        |     6 |   312 |    13 (16)| 00:00:01 |
|   1 | SORT GROUP BY                 |                        |     6 |   312 |    13 (16)| 00:00:01 |
|* 2 |   FILTER                       |                        |       |       |            |          |
|* 3 |    HASH JOIN                   |                        |     6 |   312 |    12   (9)| 00:00:01 |
|* 4 |     TABLE ACCESS BY INDEX ROWID| CM_ORDERINFO_REPORT    |     6 |   126 |     8   (0)| 00:00:01 |
|* 5 |      INDEX RANGE SCAN          | INDEX_REPORT_COPERDATE |     6 |       |     3   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL          | CM_AGENT_INFO          |    86 | 2666 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(TRUNC(               THEN '1100' WHEN ("CAGENTCODE" LIKE '10771%') THEN '1077106' ELSE "CAGENTCODE" END )
   4 - filter("ISCPROCFLAG"=0)
   5 - access("COPERDATE">=TRUNC(SYSDATE@!-1) AND "COPERDATE" Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      22735 consistent gets
          0 physical reads
          0 redo size
       1482 bytes sent via SQL*Net to client
        503 bytes received via SQL*Net from client
          3 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
         26 rows processed

ITPub博客

两个sql优化

最新文章