通过图表简化sql语句的表关联(r4笔记第70天)

在之前的博文中分享过一个执行了两天的一条sql语句,走了两个大表的扫描,导致执行时间很长,通过简化sql做了不小的改进,今天我们来看看还可以做些什么。 上次简化后的语句如下:

with tmp_logical_date as (SELECT logical_date
                  FROM logical_date
                 WHERE logical_date_type = 'R'
                   AND expiration_date IS NULL)
SELECT trim(TO_CHAR(COUNT(distinct coll.entity_id), '000000000'))
  FROM cl1_coll_entity coll,
       table_bpm_step_inst bpm,
       table_bpm_step,
       ar1_account,
       csm_account,
       csm_pay_channel,
       customer,
       subscriber,
       ar1_billing_arrangement,
       ar1_address_name,
       charge_distribute,
       tmp_logical_date
 WHERE coll.entity_id(+) = csm_account.ban
   AND coll.proc_inst_id = bpm.parent2proc_inst
   AND bpm.step2step = table_bpm_step.objid
   AND bpm.status = 30
   AND coll.entity_id = ar1_account.account_id
   AND csm_account.ban = csm_pay_channel.ban
--   AND ar1_account.account_id = ar1_aged_trial_balance.account_id
   AND csm_account.customer_id = customer.customer_id
   AND csm_account.customer_id = subscriber.customer_id
   AND ar1_account.account_id = ar1_billing_arrangement.account_id
   AND ar1_account.account_id = ar1_address_name.account_id
   AND ar1_address_name.address_type = 'ACC'
  and exists(
  (SELECT 1
          FROM ar1_aged_trial_balance
         WHERE aged_type = 'D'
           AND group_type = 'B'
           AND status = 'EFF'
           AND TRUNC(tmp_logical_date.logical_date - due_date) >= 0
           AND account_id = coll.entity_id
         )
  )
   AND subscriber.trx_id = charge_distribute.trx_id
   AND subscriber.subscriber_no = charge_distribute.agreement_no
   AND charge_distribute.target_pcn = csm_pay_channel.pym_channel_no
   AND csm_account.ban = csm_pay_channel.ban
   AND EXISTS
 (SELECT null--cl1_treatment_activity.entity_id
          FROM cl1_treatment_activity, table_bpm_step_inst, table_bpm_step
         WHERE cl1_treatment_activity.step_id = table_bpm_step_inst.objid
           AND table_bpm_step_inst.step2step = table_bpm_step.objid
           AND table_bpm_step.NAME LIKE '%IVR%'
           AND table_bpm_step_inst.status = 65
           AND TO_DATE(TO_CHAR(cl1_treatment_activity.activity_date,
                               'YYYYMMDD'),
                       'YYYYMMDD') =tmp_logical_date.logical_date
           AND cl1_treatment_activity.entity_id = csm_account.ban)

单纯来看这么多表的关联,着实是一个很棘手的事情,十多张大表关联,从技术角度来看,oracle的分析确实还是很细致的,根据数据量,走索引的地方都走了索引,预估的数据量也差不离。 但是想对这条语句做进一步的改进,单纯调整执行计划还是很有限制的。 我们来看看一个新的方法,首先我已经被这些表关联弄晕了,我简单整理了下面的图表。这个图表能够很清楚的看到表连接的情况。

表的数据都是基于cl1_coll_entity,但是通过这个图发现,重心似乎转移了。感觉重心似乎是csm_account 我们来看看csm_account和cl1_coll_entity的关联,使用了一个外连接,即对于csm_account中的关联数据在cl1_coll_entity都存在。csm_account的数据是最全的。 coll.entity_id(+) = csm_account.ban 明白了这一点,我们来看看红色框内的表连接,既然csm_account中的数据是完整的,类似一个全表扫描,那么后面的一个环形表连接就是多余的。因为方框中的表连接都是业务层面,是这些entity之间的完全映射。这些表中没有额外的过滤条件。 可以通过一个简单的例子来说明。我们创建两个表csm_account,cl1_coll_entity

create table csm_account(id number);
insert into csm_account values(1);
insert into csm_account values(2);
insert into csm_account values(3);

create table cl1_coll_entity(id number);
insert into cl1_coll_entity values(1);

select coll.id from cl1_coll_entity coll,csm_account
where coll.id(+)=csm_account.id
        ID
----------
         1

3 rows selected.
select count(coll.id) from cl1_coll_entity coll,csm_account
where coll.id(+)=csm_account.id
COUNT(COLL.ID)
--------------
             1
1 row selected.

因为cl1_coll_entity中的数据是csm_account中的子集,所以后面csm_account的完全映射丝毫不会对cl1_coll_entity的数据有任何的影响。既然没有任何的影响,就不需要保留它了。 同理标红的ar1_billing_arrangement和ar1_account中的数据是多对一的映射。这个也是完全从业务层面保证。 简化后的表连接情况如下:

可以看到原本14个表连接最后简化为了8个表连接,简化的幅度还是比较大的。 这种简化思路可以在平时的调优中参考,从业务层面能够完全保证的数据情况反复关联就显得有些冗余了。毕竟从技术层面我们无法得到更多的细节。 不管怎么样,都是为了简化逻辑,减少资源的消耗。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-03-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏互联网开发者交流社区

SQL触发器实例(下)

1484
来自专栏猿人谷

【性能提升神器】Covering Indexes

可能有小伙伴会问,Covering Indexes到底是什么神器呢?它又是如何来提升性能的呢?接下来我会用最通俗易懂的语言来进行介绍,毕竟不是每个程序猿都要像D...

611
来自专栏文渊之博

介绍一种非常好用汇总数据的方式GROUPING SETS

介绍   对于任何人而言,用T-SQL语句来写聚会查询都是工作中重要的一环。我们大家也都很熟悉GROUP BY子句来实现聚合表达式,但是如果打算在一个结果集中包...

38711
来自专栏happyJared

爬虫进阶:Scrapy抓取慕课网

  完整的爬虫流程大致是这样的:分析页面结构 -> 确定提取信息 -> 设计相应表结构 -> 编写爬虫脚本 -> 数据保存入库;入库可以选择mongo这样的文档...

3344
来自专栏企鹅号快讯

数据分析师必备的数据提取技能

数据分析师必备技能SQL 在数据分析的整个流程中,数据获取是不可或缺的一环,那么作为数据分析师,我们不仅仅需要了解如何获取二手数据,还必须掌握如何从数据库中获取...

28110
来自专栏java达人

SQL索引优化

序言 数据库的优化方法有很多种,在应用层来说,主要是基于索引的优化。本次秘笈根据实际的工作经验,在研发原来已有的方法的基础上,进行了一些扩充,总结了基于索引的S...

2028
来自专栏程序猿DD

一个不可思议的MySQL慢查分析与解决

前言 开发需要定期的删除表里一定时间以前的数据,SQL如下 mysql > delete from testtable WHERE biz_date <= '2...

3465
来自专栏杨建荣的学习笔记

一条SQL语句的执行计划变化探究(r10笔记第3天)

最近有个同事碰到一个问题,想让我给点思路。我大体了解了一下,是一个系统目前在做压力测试,但是经业务反馈发现某个环节的处理时间有些长,排查了一圈,最后这件事情就落...

3296
来自专栏Jed的技术阶梯

Hive案例02-数值累加

其中字段意义: userid(string) month(string) count(int) 分别代表: 用户id 月份 该月访问次数 需求: ...

3803
来自专栏数据和云

SQL为王:oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化、SQL优化和troubleshooting 编辑手记:如何提高数...

4596

扫码关注云+社区

领取腾讯云代金券