SQL优化经典----子查询优化_王显伟

http://blog.sina.com.cn/u/1640860150

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

SQL优化经典----子查询优化

(2014-02-24 15:56:22)

今天在网上看到一个经典的SQL优化案例，优化思路值得学习和参考，在此做一下自己学习分析的过程，具体SQL如下，该SQL执行需要1分钟的时间，对该SQL进行优化。

select tpc.policy_id,
       tcm.policy_code,
       tpf.organ_id,
       to_char(tpf.insert_time, 'YYYY-MM-DD') As insert_time,
       tpc.change_id,
       d.policy_code,
       e.company_name,
       f.real_name,
       tpf.fee_type,
       sum(tpf.pay_balance) as pay_balance,
       c.actual_type,
       tpc.notice_code,
       d.policy_type,
       g.mode_name as pay_mode
from t_policy_change    tpc,
       t_contract_master tcm,
       t_policy_fee       tpf,
       t_fee_type         c,
       t_contract_master d,
       t_company_customer e,
       t_customer         f,
       t_pay_mode         g
where tpc.change_id = tpf.change_id
   and tpf.policy_id = d.policy_id
   and tcm.policy_id = tpc.policy_id
   and tpf.receiv_status = 1
   and tpf.fee_status = 1
   and tpf.payment_id is null
   and tpf.fee_type = c.type_id
   and tpf.pay_mode = g.mode_id
   and d.company_id = e.company_id(+)
   and d.applicant_id = f.customer_id(+)
   and tpf.organ_id in
       (select
         organ_id
          from t_company_organ
         start with organ_id = '101'
        connect by prior organ_id = parent_id)
group by tpc.policy_id,
          tpc.change_id,
          tpf.fee_type,
          to_char(tpf.insert_time, 'YYYY-MM-DD'),
          c.actual_type,
          d.policy_code,
          g.mode_name,
          e.company_name,
          f.real_name,
          tpc.notice_code,
          d.policy_type,
          tpf.organ_id,
          tcm.policy_code
order by change_id, fee_type

执行计划信息

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT

http://s3/mw690/001N2SGizy6GQ4PIfzs22&690
http://s13/mw690/001N2SGizy6GQ4PLUscec&690
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("TPF"."FEE_TYPE"="C"."TYPE_ID")
   5 - access("TCM"."POLICY_ID"="TPC"."POLICY_ID")
   7 - access("TPF"."ORGAN_ID"="VW_NSO_1"."$nso_col_1")
   8 - access("D"."APPLICANT_ID"="F"."CUSTOMER_ID"(+))
   9 - access("TPF"."POLICY_ID"="D"."POLICY_ID")
10 - access("TPF"."PAY_MODE"="G"."MODE_ID")
12 - filter("TPF"."CHANGE_ID" IS NOT NULL AND TO_NUMBER("TPF"."RECEIV_STATUS")=1

               AND "TPF"."FEE_STATUS"=1 AND
              "TPF"."PAYMENT_ID" IS NULL)
15 - access("indexjoin_alias_012".ROWID="indexjoin_alias_011".ROWID)
19 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')
21 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')
26 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)
28 - access("TPC"."CHANGE_ID"="TPF"."CHANGE_ID")
31 - access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))

55 rows selected

Statistics
----------------------------------------------------------
         21 recursive calls
          0 db block gets
     125082 consistent gets
      21149 physical reads
          0 redo size
       2448 bytes sent via SQL*Net to client
        656 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          4 sorts (memory)
          0 sorts (disk)
         11 rows processed
优化思路：

第一步：排除大表中的全表扫描情况

从执行计划中，我们可以看出，12行 T_POLICY_FEE该表有40万行记录，走了全表扫描，查看ID为12的过滤信息，发现TO_NUMBER("TPF"."RECEIV_STATUS")=1，典型的开发人员书写SQL不请注意细节导致无法走索引

第二步：过滤条件中存在in子查询

       (select
         organ_id
          from t_company_organ
         start with organ_id = '101'
        connect by prior organ_id = parent_id)
从执行计划上看，CBO对这儿子查询进行了unnest，因为通常情况下CBO认为子查询被unnest之后性能好

filter，这时我们需要确定子查询返回多少行，经过确认，该子查询返回只返回1行，对于子查询，如果它返回数据很少(这里返回1行)，那么可以让它走filter, 而且filter基本上是在SQL最后去阶段执行，这样t_policy_fee就可以走索引了，所以我给这个子查询加了个HINT，禁止子查询扩展

select tpc.policy_id,
       tcm.policy_code,
       tpf.organ_id,
       to_char(tpf.insert_time, 'YYYY-MM-DD') As insert_time,
       tpc.change_id,
       d.policy_code,
       e.company_name,
       f.real_name,
       tpf.fee_type,
       sum(tpf.pay_balance) as pay_balance,
       c.actual_type,
       tpc.notice_code,
       d.policy_type,
       g.mode_name as pay_mode
from t_policy_change    tpc,
       t_contract_master tcm,
       t_policy_fee       tpf,
       t_fee_type         c,
       t_contract_master d,
       t_company_customer e,
       t_customer         f,
       t_pay_mode         g
where tpc.change_id = tpf.change_id
   and tpf.policy_id = d.policy_id
   and tcm.policy_id = tpc.policy_id
   and tpf.receiv_status = '1' ---这里原来没引号，不添加上就没法用索引
   and tpf.fee_status = 1
   and tpf.payment_id is null
   and tpf.fee_type = c.type_id
   and tpf.pay_mode = g.mode_id
   and d.company_id = e.company_id(+)
   and d.applicant_id = f.customer_id(+)
   and tpf.organ_id in
       (select /+ no_unnest /     --新浪博客自动屏蔽hint,此处的HINT后加的,注意补全
         organ_id
          from t_company_organ
         start with organ_id = '101'
        connect by prior organ_id = parent_id)
group by tpc.policy_id,
          tpc.change_id,
          tpf.fee_type,
          to_char(tpf.insert_time, 'YYYY-MM-DD'),
          c.actual_type,
          d.policy_code,
          g.mode_name,
          e.company_name,
          f.real_name,
          tpc.notice_code,
          d.policy_type,
          tpf.organ_id,
          tcm.policy_code
order by change_id, fee_type

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT

http://s2/mw690/001N2SGizy6GQ6Jz23De1&690
http://s2/mw690/001N2SGizy6GQ6JATId81&690
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter( EXISTS (SELECT 0 FROM "T_COMPANY_ORGAN" "T_COMPANY_ORGAN" WHERE
              "T_COMPANY_ORGAN"."PARENT_ID"=NULL AND ("T_COMPANY_ORGAN"."ORGAN_ID"=:B1)))
   8 - access("SYS_ALIAS_1"."FEE_TYPE"="C"."TYPE_ID")
10 - access("SYS_ALIAS_1"."PAY_MODE"="G"."MODE_ID")
12 - filter("SYS_ALIAS_1"."CHANGE_ID" IS NOT NULL AND "SYS_ALIAS_1"."FEE_STATUS"=1

               AND "SYS_ALIAS_1"."PAYMENT_ID"  IS NULL)
13 - access("SYS_ALIAS_1"."RECEIV_STATUS"='1')
15 - access("SYS_ALIAS_1"."POLICY_ID"="D"."POLICY_ID")
17 - access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))
19 - access("D"."APPLICANT_ID"="F"."CUSTOMER_ID"(+))
21 - access("TPC"."CHANGE_ID"="SYS_ALIAS_1"."CHANGE_ID")
23 - access("TCM"."POLICY_ID"="TPC"."POLICY_ID")
24 - filter("T_COMPANY_ORGAN"."ORGAN_ID"=:B1)
25 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')
27 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')
32 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)

58 rows selected.

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       2817 consistent gets
          0 physical reads
          0 redo size
       2268 bytes sent via SQL*Net to client
        656 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         40 sorts (memory)
          0 sorts (disk)
          9 rows processed
最终这个SQL能在1秒以内跑完，逻辑读下降到2817

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：SQL优化经典案例----RLS(ROW LEVEL SECURITY)

后一篇：ORA-01102: cannot mount database in EXCLUSIVE mode

新浪BLOG意见反馈留言板　欢迎批评指正