此sql语句如何优化,1千万条数据中查需要7，8分钟

Question

此sql语句如何优化。
有表多个同结构表命名为dev_log_table1，dev_log_table2，dev_log_table3，结构详细如下：
+---------------------+------+----------+-----------+-----------+------------+----------+---------+
| time                | type | subtype  | src_ip    | dst_ip    | log_repeat | dev_name | dev_pri |
+---------------------+------+----------+-----------+-----------+------------+----------+---------+
| 2009-06-29 16:25:31 | ips  | IPSEVENT | 100.1.1.2 | 200.1.1.2 |          1 | mjl.cc  |      0 |
| 2009-06-29 16:25:32 | ips  | IPSEVENT | 100.1.1.2 | 200.1.4.2 |          4 | mjl.cc  |      0 |
| 2009-06-29 16:25:33 | ips  | IPSEVENT | 100.1.1.2 | 200.1.1.2 |          1 | mjl.cc  |      0 |
| 2009-06-29 16:25:33 | ips  | IPSEVENT | 100.3.1.2 | 200.1.0.2 |          4 | mjl.cc  |      0 |
| 2009-06-29 16:25:33 | ips  | IPSEVENT | 100.1.1.2 | 200.1.1.2 |          1 | mjl.cc  |      0 |
+---------------------+------+----------+-----------+-----------+------------+----------+---------+
(字段意思分别为，时间，类型，子类型，源IP，目的IP，事件发生次数,设备名,设备优先级)
time ,type加上了索引 .需求：求出一段时间内，某个设备，发生的事件总数，事件是由多少个源IP，多少目的IP产生的。如果用：SELECT distinct src_ip ,distinct dst_ip, sum(log_repeat) from  （满足条件后的union多表)
当 union多表得出的数据很大时，C盘马上暴满。所以没有使用.用VIEW的话，数据量大，也不会快，我是动太生在的SQL，因为dev_log_table可以有多个，在生成时才知道数据存在那张表.所在存储过程也用不了
下面是我写的SQL，在三个表1千万条数据中查需要7，8分钟。显然用户受不了。大家帮忙看一下如何优化，或如果写。
SELECT   
(
SELECT  COUNT(distinct src_ip) SRCCOUNT
FROM 
(
(
SELECT distinct src_ip
FROM dev_log_table3
WHERE  type='ips'
AND (time>=' 2009-06-29 00:00:17' AND time <'2009-06-29 23:41:17') 
AND subtype='ipsevent'
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
)
UNION ALL
(
SELECT distinct src_ip
FROM dev_log_table2
WHERE  type='ips'
AND (time>=' 2009-06-29 00:00:17' AND time <'2009-06-29 23:41:17') 
AND subtype='ipsevent'
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
)
UNION ALL
(
SELECT distinct src_ip
FROM dev_log_table1
WHERE  type='ips'
AND (time>=' 2009-06-29 00:00:17' AND time <'2009-06-29 23:41:17') 
AND subtype='ipsevent'
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
)
)  temptb ) SRCCOUNT, COUNT(distinct dst_ip) DSTCOUNT, SUM(log_repeat) SUMEVENTCOUNT, CEIL(SUM(log_repeat) / 23) AVGCOUNT FROM 
(
(
SELECT  dst_ip, sum(log_repeat)  log_repeat
FROM dev_log_table3
WHERE  type='ips'
AND (time>=' 2009-06-29 00:00:17' AND time <'2009-06-29 23:41:17') 
AND subtype='ipsevent'
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
GROUP BY dst_ip
)
UNION ALL
(
SELECT  dst_ip, sum(log_repeat)  log_repeat
FROM dev_log_table2
WHERE  type='ips'
AND (time>=' 2009-06-29 00:00:17' AND time <'2009-06-29 23:41:17') 
AND subtype='ipsevent'
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
GROUP BY dst_ip
)
UNION ALL
(
SELECT  dst_ip, sum(log_repeat)  log_repeat
FROM dev_log_table1
WHERE  type='ips'
AND (time>=' 2009-06-29 00:00:17' AND time <'2009-06-29 23:41:17') 
AND subtype='ipsevent'
AND ( (dev_pri = 0 AND dev_name = 'mjl.cc'))
GROUP BY dst_ip
)
)  temptb 

Answer 1

还没有弄过这样大的数据量的sql.up.

Answer 2

SRCCOUNT 那个字段，是个固定的值，每次查询都要 重新计算。建议先查出它 放到一个变量，
再把它放到查询列。
如查还是很慢
subtype dev_pri dev_name 各自试 bitmap索引， bitmap 会影响 insert 性能。建议在 time 按天分区。

Answer 3

楼主做安全的吧，ids or ips?这个量不大，应该不会用7，8分钟吧，尝试一下分析函数
SQL> select * from test1;TIME        TYPE       SUBTYPE              SRC_IP          DST_IP          LOG_REPEAT DEV_NAME                DEV_PRI
----------- ---------- -------------------- --------------- --------------- ---------- -------------------- ----------
29-06-2009  ips        IPSEVENT             100.1.1.2       200.1.1.2                1 mjl.cc                        0
29-06-2009  ips        IPSEVENT             100.1.1.2       200.1.1.2                4 mjl.cc                        0
29-06-2009  ips        IPSEVENT             100.1.1.2       200.1.4.2                1 mjl.cc                        0
29-06-2009  ips        IPSEVENT             100.3.1.2       200.1.0.2                4 mjl.cc                        0
29-06-2009  ips        IPSEVENT             100.1.1.2       200.1.1.2                1 mjl.cc                        0SQL> 
SQL> select TIFcnt,sum(decode(srccount,1,1,0)) src_count,sum(decode(dstcount,1,1,0)) dst_count from (
  2    select count(1)over() TIFCnt,
  3    row_number()over(partition by src_ip order by src_ip) srccount,
  4    row_number()over(partition by dst_ip order by dst_ip) dstcount
  5    from test1
  6    Where (time>=to_date('2009-06-29 00:00:17','yyyy-mm-dd hh24:mi:ss') AND time <to_date('2009-06-29 23:41:17','yyyy-mm-dd hh24:mi:ss'))
  7    AND subtype='IPSEVENT'
  8    AND dev_pri = 0
  9    AND dev_name = 'mjl.cc'
 10  ) tmp group by TIFcnt;    TIFCNT  SRC_COUNT  DST_COUNT
---------- ---------- ----------
         5          2          3SQL> 

Answer 4

SQL> select * from test1;TIME        TYPE       SUBTYPE              SRC_IP          DST_IP          LOG_REPEAT DEV_NAME                DEV_PRI
----------- ---------- -------------------- --------------- --------------- ---------- -------------------- ----------
29-06-2009  ips        IPSEVENT             100.1.1.2       200.1.1.2                1 mjl.cc                        0
29-06-2009  ips        IPSEVENT             100.1.1.2       200.1.1.2                4 mjl.cc                        0
29-06-2009  ips        IPSEVENT             100.1.1.2       200.1.4.2                1 mjl.cc                        0
29-06-2009  ips        IPSEVENT             100.3.1.2       200.1.0.2                4 mjl.cc                        0
29-06-2009  ips        IPSEVENT             100.1.1.2       200.1.1.2                1 mjl.cc                        0SQL> 
SQL> select TIFcnt,sum(decode(srccount,1,1,0)) src_count,sum(decode(dstcount,1,1,0)) dst_count from (
  2    select count(1)over() TIFCnt,
  3    row_number()over(partition by src_ip order by src_ip) srccount,
  4    row_number()over(partition by dst_ip order by dst_ip) dstcount
  5    from test1
  6    Where (time>=to_date('2009-06-29 00:00:17','yyyy-mm-dd hh24:mi:ss') AND time <to_date('2009-06-29 23:41:17','yyyy-mm-dd hh24:mi:ss'))
  7    AND subtype='IPSEVENT'
  8    AND dev_pri = 0
  9    AND dev_name = 'mjl.cc'
 10  ) tmp group by TIFcnt;    TIFCNT  SRC_COUNT  DST_COUNT
---------- ---------- ----------
         5          2          3SQL>