关于分区表跨分区的count函数查询速度太慢。

由于公司业务，采用了大表分区的方案，我创建了组合分区，子分区是按照天进行分区，现在是想统计1个月的数据，也就是需要跨30个分区，数据量大概在2000万左右，分区字段都变成分区索引了，统计总数的时候，如果只用分区索引，速度很快，但是加上一些别的字段速度特别慢，新加的字段只有1，0两种数据，所以不适合加索引，试过了分区的本地索引和全局索引，效果都不佳，贴上sql，求哪路大神帮忙。select  count(1) 
  from send_new2  t
 where 1 = 1
   and t.send_status = '6'
   and t.sys_time between
       to_date('2017-02-02 04:02:07', 'yyyy-mm-dd hh24:mi:ss') and
       to_date('2017-03-20 03:02:07', 'yyyy-mm-dd hh24:mi:ss')
   and t.IS_ORIGINAL_SMS=0 ---》这个is_original_sms 不是分区字段，只有0，1 两种形式，send_status 是父分区键，sys_time是子分区键。只用分区索引的话，2000万以上的数据统计在2-3秒，加上这个字段200秒都查不出来。

解决方案 »

  1.   

    你的数据量并不算大，我们这里生产的历史数据几十亿条记录。
    关键是有没有建好索引。表中最好有个用Sequences记录的字段。
    你这是单表不是多表关联查询本不会很慢。你将你的SQL放到
    PL/SQL Developer里按F5看下有没有走索引。
    另外建议建个索引表空间，并且send_status字段用数字型，不要用字符型
    建一个send_status、sys_time、IS_ORIGINAL_SMS三个字段的组合索引将索引放到索引表空间里。
    不要放到数据的表空间里
      

  2.   

    几个疑问或需要提供的信息：
    1、你很快的时候，有send_status字典吗？还是就只有sys_time？
    2、sys_time是不是个非空字段？
    3、你的第一层分区键是什么字段？为什么时间字段会是第二层的分区键而不是第一层的？
    4、“分区字段都变成分区索引了”，这意思是不是分区字段上都创建了分区索引？也就是说至少分区字段sys_time上有单字段索引？还是它与第一层的分区键上创建了组合索引？顺序如何？如果是的话，那么这个组合索引中的字段是不是不可能全部为空？
    5、单月总数据量为2000w，那么加上其他字段过滤后，这个数据量是否也可控？比如缩小到了20万？或者根本不可控，可能是200条，也可能还是200万？
    6、给出快的时候的执行计划。
      

  3.   


    1.很快的时候用的是两个字段，send_status,sys_time 
    2.不是非空字段，但是数据不会为空。
    3.第一层用的是send_status,应为业务需要，有其他的项目同时用到同一张表的send_status字段，但是没有用到sys_time字段，考虑到如果用时间的话可能需要重写一部分sql，因为用到的项目比较多，考虑到部署的问题，就用send_status作为第一层。
    4.是组合索引，send_status sys_time的顺序，一定不会全为空。
    5.是不可控的 ，因为查询的话可能不一定查询多大的时间跨度，而且还有一些其他的无法建成索引的字段，查询量很不固定。
    6.执行计划如下 这个是快的分区索引：
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1600035421--------------------------------------------------------------------------------
    ----------------------------------------| Id  | Operation                  | Name                      | Rows  | Bytes |
     Cost (%CPU)| Time     | Pstart| Pstop |--------------------------------------------------------------------------------
    ----------------------------------------|   0 | SELECT STATEMENT           |                           |     1 |    11 |
      7864   (1)| 00:01:35 |       |       ||   1 |  SORT AGGREGATE            |                           |     1 |    11 |
                |          |       |       ||   2 |   PARTITION LIST SINGLE    |                           |  2557K|    26M|
      7864   (1)| 00:01:35 |   KEY |   KEY ||   3 |    PARTITION RANGE ITERATOR|                           |  2557K|    26M|
      7864   (1)| 00:01:35 |    34 |    80 ||*  4 |     INDEX RANGE SCAN       | IDX_SEND_PAR_NEW_20150501 |  2557K|    26M|
      7864   (1)| 00:01:35 |       |       |--------------------------------------------------------------------------------
    ----------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   4 - access("T"."SEND_STATUS"=6 AND "T"."SYS_TIME">=TO_DATE(' 2017-02-02 04:02
    :07', 'syyyy-mm-dd hh24:mi:ss')              AND "T"."SYS_TIME"<=TO_DATE(' 2017-03-20 03:02:07', 'syyyy-mm-dd h
    h24:mi:ss'))
    这个是慢的执行计划：Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3591182293--------------------------------------------------------------------------------
    --------------------------------------------------| Id  | Operation                            | Name                      | Rows
     | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |--------------------------------------------------------------------------------
    --------------------------------------------------|   0 | SELECT STATEMENT                     |                           |     1
     |    14 |  1847   (1)| 00:00:23 |       |       ||   1 |  SORT AGGREGATE                      |                           |     1
     |    14 |            |          |       |       ||   2 |   PARTITION LIST SINGLE              |                           | 10797
     |   147K|  1847   (1)| 00:00:23 |   KEY |   KEY ||   3 |    PARTITION RANGE ITERATOR          |                           | 10797
     |   147K|  1847   (1)| 00:00:23 |    34 |    52 ||*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID| SEND_NEW2                 | 10797
     |   147K|  1847   (1)| 00:00:23 |       |       ||*  5 |      INDEX RANGE SCAN                | IDX_SEND_PAR_NEW_20150501 | 10797
     |       |    36   (0)| 00:00:01 |       |       |--------------------------------------------------------------------------------
    --------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------   4 - filter("T"."IS_ORIGINAL_SMS"=0)
       5 - access("T"."SEND_STATUS"=6 AND "T"."SYS_TIME">=TO_DATE(' 2017-02-02 04:02
    :07', 'syyyy-mm-dd hh24:mi:ss') AND              "T"."SYS_TIME"<=TO_DATE(' 2017-02-20 03:02:07', 'syyyy-mm-dd hh24:
    mi:ss'))
      

  4.   

    两个计划的区别就是，第二个差的有返表操作：TABLE ACCESS BY LOCAL INDEX ROWID
    所以，能做的优化就是把额外的条件字段也加入到组合索引中，但是，如果如果这个额外的条件也是很多变的话，除了位图索引（当然原有的组合索引也要拆成两条位图索引比较好），还真不好优化了。、
    另外，你的主键呢？主键包含分区键吗？还是你现在主键就是两个分区键的组合？
      

  5.   


    主键跟分区键无关，这个表只有一个主键，不是联合主键。主要是考虑表的字段较多，开发人员用到的筛选条件比较杂，不一定会用到哪些字段，而且不能把他们用到的字段都加到索引，所以这个问题不太好解决。还好是count，否则连只有两个分区键条件的查询都会比较惨，而一旦加入了其他字段，那么这个返表操作肯定是逃不掉了，除非用分区扫描来替代索引，但至少也要扫描2000万数据，想来性能也不会好……
    如果条件组合非常多，那么位图索引可以说是不多的选择了。