关联表查询和索引使用的探讨一则-阿里云开发者社区

关联表查询和索引使用的探讨一则

2017-11-28 963

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介：

   今天碰到一个小的查询统计需求，总结一番确实有些意思。
      在一张超多200万行的表中，需要查询在某个时段，符合全部10个类型的信息。即：
      例如某个手机号码，在某个时段完成了全部10种类型的服务。注意，决对不能使用IN操作，是全部的10个都要符合条件。同事采用中间表分步提交再查询的方法。我想是否可以用一个SQL来完成呢？且性能要好。于是“旅程”开始了。
      第一，其实这是一个比较典型的自连接操作。所以首次写了这样的SQL：

select * from
(select distinct src_terminal_id
                  from mms_seed_info_cdr_history t
                 where to_number(t.send_time) >= to_number('20080115000000')
                   and to_number(t.send_time) <= to_number('20080331000000')
                   and t.mms_zx_id = '28') tab1,
(select distinct src_terminal_id
                  from mms_seed_info_cdr_history t
                 where to_number(t.send_time) >= to_number('20080115000000')
                   and to_number(t.send_time) <= to_number('20080331000000')
                   and t.mms_zx_id = '29') tab2,
......
                                                       tab10
where tab1.src_terminal_id = tab2.src_terminal_id
   and tab1.src_terminal_id = tab3.src_terminal_id
   ......
   and tab1.src_terminal_id = tab10.src_terminal_id;

      执行了很长时间，不对。停下来看，检查执行计划发现每一个嵌套的SQL都是基于全表扫描来完成查询！！注意当时的脚本中还没有使用TO_NUMBER这个函数。当时发现有时间范围搜索，立刻建立压缩索引，但是执行计划还是显示全部扫描？仔细看看，原来那个SEND_TIME字段是可空的VARCHAR2。经过询问知道实际业务中该字段是必须有值的。改不能为空，但是基于VARCHAR2的索引一旦使用大于小于这样的操作就会失效而直接使用全表扫描的。只好使用基于函数的INDEX了，于是就建立了基于TO_NUMBER(SEND_TIME)的一个索引。且另一个MMS_ZX_ID为低基数列字段，于是使用了BITMAP索引。
     查看效果，计算的执行成本基本上是原来的10%！返回首行数据的时间由26S左右，下降到6S左右了。
      开始执行，很长时间没有反馈，但是TEMP表空间却开始告急了。这个SQL基本吞噬了好几个G的空间。立刻停止！
      看上去10个表的连接ORACLE处理起来确实费力。换换方法吧。

select tabb1.src_terminal_id
  from (select tab1.src_terminal_id
          from (select distinct src_terminal_id
                  from mms_seed_info_cdr_history t
                 where to_number(t.send_time) >= to_number('20080115000000')
                   and to_number(t.send_time) <= to_number('20080331000000')
                   and t.mms_zx_id = '28') tab1,
               (select distinct src_terminal_id
                  from mms_seed_info_cdr_history t
                 where to_number(t.send_time) >= to_number('20080115000000')
                   and to_number(t.send_time) <= to_number('20080331000000')
                   and t.mms_zx_id = '29') tab2
         where tab1.src_terminal_id = tab2.src_terminal_id) tabb1,
......
       (select tab9.src_terminal_id
          from (select distinct src_terminal_id
                  from mms_seed_info_cdr_history t
                 where to_number(t.send_time) >= to_number('20080115000000')
                   and to_number(t.send_time) <= to_number('20080331000000')
                   and t.mms_zx_id = '37') tab9,
               (select distinct src_terminal_id
                  from mms_seed_info_cdr_history t
                 where to_number(t.send_time) >= to_number('20080115000000')
                   and to_number(t.send_time) <= to_number('20080331000000')
                   and t.mms_zx_id = '38') tab10
         where tab9.src_terminal_id = tab10.src_terminal_id) tabb5
where tabb1.src_terminal_id = tabb2.src_terminal_id
   and tabb1.src_terminal_id = tabb3.src_terminal_id
   and tabb1.src_terminal_id = tabb4.src_terminal_id
   and tabb1.src_terminal_id = tabb5.src_terminal_id;

      我先将两个表连接查询嵌套成一张表，10张表我做成5个嵌套表。然后这5张表再做连接查询。看看执行效果，共执行了75S，输出结果集。
      通过这个SQL操作，我们不难看出，首先，INDEX的使用是需要讲究方式和方法的。但是可以肯定的是，一旦进行了数据类型转换操作，即使是隐性的，INDEX也会失效转而使用全部扫描了。再有就是表连接的数量，还是应该适当减少，可以通过嵌套的方式减少表连接数量，降低ORACLE内部操作复杂性，来提高执行速度。 -:)

------欢迎指正

本文转自Be the miracle!博客51CTO博客，原文链接http://blog.51cto.com/miracle/65485如需转载请自行联系原作者

Larry.Yue

关联表查询和索引使用的探讨一则

热门文章

最新文章

相关课程

相关电子书

相关实验场景