海量数据查询问题

Question

数据库大概情况如下;
1,每天新建一张表，按照时间范围分为24个区，
2，每分钟向表里导入十万条数据，目前测试时表中大概250万条数据，进行查询时用时需要20s，感觉有点太慢了，请教各位高手，应该如何优化呢？

Answer 1

查询语句如下(其中start_time,report_time,imsi为联合索引，索引类型为local)：/* Formatted on 2011/10/24 17:07 (Formatter Plus v4.8.8) */
SELECT a.start_time, a.report_time, a.imsi, a.cdrid
  FROM cdr_data_a20111024 a
 WHERE EXISTS (
          SELECT cdrid
            FROM cdr_data_a20111024 b
           WHERE (b.cdrid = a.cdrid OR b.base_cdrid = a.cdrid)
             AND (    (b.start_time BETWEEN   TO_DATE ('2011-10-24 08:07:47',
                                                       'yyyy-mm-dd hh24:mi:ss'
                                                      )
                                            - 2 / 24
                                        AND TO_DATE ('2011-10-24 23:57:47',
                                                     'yyyy-mm-dd hh24:mi:ss'
                                                    )
                      )
                  AND (b.report_time >
                          TO_DATE ('2011-10-24 08:07:47',
                                   'yyyy-mm-dd hh24:mi:ss'
                                  )
                      )
                 )
             AND (   b.imsi IN ('460022517717646')
                  OR b.peer_number IN ('460022517717646')
                 ))
   AND (    (a.start_time BETWEEN   TO_DATE ('2011-10-24 08:07:47',
                                             'yyyy-mm-dd hh24:mi:ss'
                                            )
                                  - 2 / 24
                              AND TO_DATE ('2011-10-24 23:57:47',
                                           'yyyy-mm-dd hh24:mi:ss'
                                          )
            )
        AND (a.report_time >
                      TO_DATE ('2011-10-24 08:07:47', 'yyyy-mm-dd hh24:mi:ss')
            )
       )
   AND (a.imsi='460022517717646' OR a.peer_number ='460022517717646'
       )

Answer 2

执行计划如下：| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                    |                        |     1 |
 193 |     0   (0)| 00:00:01 |       |       ||*  1 |  FILTER                             |                        |       |
     |            |          |       |       ||   2 |   PARTITION RANGE ITERATOR          |                        |     1 |
 193 |     0   (0)| 00:00:01 |     7 |    24 ||*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID| CDR_DATA_A20111024     |     1 |
 193 |     0   (0)| 00:00:01 |     7 |    24 ||*  4 |     INDEX RANGE SCAN                | IDX_CDR_DATA_A20111024 |     1 |
     |     0   (0)| 00:00:01 |     7 |    24 ||   5 |   PARTITION RANGE ITERATOR          |                        |     1 |
 206 |     0   (0)| 00:00:01 |     7 |    24 ||*  6 |    TABLE ACCESS BY LOCAL INDEX ROWID| CDR_DATA_A20111024     |     1 |
 206 |     0   (0)| 00:00:01 |     7 |    24 ||*  7 |     INDEX RANGE SCAN                | IDX_CDR_DATA_A20111024 |     1 |
     |     0   (0)| 00:00:01 |     7 |    24 |--------------------------------------------------------------------------------

Answer 3

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   11163067  consistent gets
          0  physical reads
       1128  redo size
       1163  bytes sent via SQL*Net to client
        349  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         16  rows processed

Answer 4

试下改成With的方式呢
WITH w1 AS (
SELECT b.CDRID, b.BASE_CDRID, b.START_TIME, b.REPORT_TIME, b.IMSI
  FROM CDR_DATA_A20111024 B
 WHERE ((B.START_TIME BETWEEN
       TO_DATE('2011-10-24 08:07:47', 'yyyy-mm-dd hh24:mi:ss') - 2 / 24 AND
       TO_DATE('2011-10-24 23:57:47', 'yyyy-mm-dd hh24:mi:ss')) AND
       (B.REPORT_TIME >
       TO_DATE('2011-10-24 08:07:47', 'yyyy-mm-dd hh24:mi:ss')))
   AND (B.IMSI IN ('460022517717646') OR
       B.PEER_NUMBER IN ('460022517717646')))
SELECT T1.START_TIME, T1.REPORT_TIME, T1.IMSI, T1.CDRID
  FROM W1 T1
 WHERE EXISTS (SELECT T2.CDRID
          FROM W1 T2
         WHERE T1. CDRID = T2.CDRID
            OR T1.BASE_CDRID = T2.BASE_CDRID)

Answer 5

你的sql需求是什么呢？表a的条件和表b的条件除了（b.cdrid = a.cdrid OR b.base_cdrid = a.cdrid）其他都一样的，因为表A和表B同一个表，（b.cdrid = a.cdrid OR b.base_cdrid = a.cdrid）在cdrid不为null的时候永远是true,所以你的sql等价：SELECT a.start_time,
       a.report_time,
       a.imsi,
       a.cdrid
  FROM cdr_data_a20111024 a
 WHERE (
           (a.start_time BETWEEN TO_DATE('2011-10-24 08:07:47', 'yyyy-mm-dd hh24:mi:ss') - 2 / 24 AND TO_DATE('2011-10-24 23:57:47', 'yyyy-mm-dd hh24:mi:ss')) AND
           (a.report_time > TO_DATE('2011-10-24 08:07:47', 'yyyy-mm-dd hh24:mi:ss'))
       )
   AND (a.imsi = '460022517717646' OR a.peer_number = '460022517717646')

Answer 6

sql已简化为楼上所说，但是执行计划显示突然又不走索引了，这又是为何呢？SELECT a.start_time,
       a.report_time,
       a.imsi,
       a.cdrid
  FROM cdr_data_a20111024 a
 WHERE (
           (a.start_time BETWEEN TO_DATE('2011-10-24 08:07:47', 'yyyy-mm-dd hh24:mi:ss') - 2 / 24 AND TO_DATE('2011-10-24 23:57:47', 'yyyy-mm-dd hh24:mi:ss')) AND
           (a.report_time > TO_DATE('2011-10-24 08:07:47', 'yyyy-mm-dd hh24:mi:ss'))
       )
   AND (a.imsi = '460022517717646' OR a.peer_number = '460022517717646')

Answer 7

根据业务建立的是start_time,report_time,imsi联合索引，执行计划是全表扫描