手动迁移原创博客，原文发表在http://blog.itpub.net/20777547/viewspace-1144174/
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
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
