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

      今天碰到一个小的查询统计需求，总结一番确实有些意思。

      在一张超多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内部操作复杂性，来提高执行速度。 -:)

      ------欢迎指正

©著作权归作者所有：来自51CTO博客作者Larry.Yue的原创作品，如需转载，请注明出处，否则将追究法律责任

ORACLE数据库休闲ORACLE足迹