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

最新推荐文章于 2024-09-04 08:54:10 发布

kuifeng.dong

最新推荐文章于 2024-09-04 08:54:10 发布

阅读量1.4k

点赞数

分类专栏： oracle

oracle 专栏收录该内容

72 篇文章 7 订阅

订阅专栏

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

---转载http://blog.sina.com.cn/s/blog_61cd89f60102efoc.html

今天在网上看到一个经典的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

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

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)