求高效Sql查询-oracle

Question

首先介绍下表结构   表先后进行过2次修整，不过目前都在不同的客户那是用
1)
CREATE TABLE NETWORK_FLOWTABLE
(
  EQUIP_ID     NUMBER(20) NOT NULL, /*外键 CMDB_CI.id*/
  IFINDEX      NUMBER(10),       /*外键 NETWORK_INTERFACE.id*/
  COLLECTDATE  DATE,                  /*时间*/
  RXUTIL       NUMBER(20,4),
  TXUTIL       NUMBER(20,4),
  RXBPS        NUMBER(20,4),
  TXBPS        NUMBER(20,4),
  CLEANID1 NUMBER(20),
  CLEANID2 NUMBER(20),
  CLEANID3 NUMBER(20),
  PERIOD       NUMBER(4)  DEFAULT '0'
)
PARTITION BY HASH (EQUIP_ID) (
PARTITION p_1,
.....
)2）
CREATE TABLE NETWORK_FLOWTABLE
(
  EQUIP_ID     NUMBER(20) NOT NULL,   /*外键 CMDB_CI.id*/
  IFINDEX      NUMBER(10),       /*外键 NETWORK_INTERFACE.id*/
  COLLECTDATE  DATE,                  /*时间*/
  RXUTIL       NUMBER(20,4),
  TXUTIL       NUMBER(20,4),
  RXBPS        NUMBER(20,4),       
  TXBPS        NUMBER(20,4),
  CLEANID1 NUMBER(20),
  CLEANID2 NUMBER(20),
  CLEANID3 NUMBER(20),
  PERIOD    NUMBER(4)  DEFAULT '0'
)PARTITION BY RANGE (COLLECTDATE) subpartition by hash(EQUIP_ID)(
PARTITION  VALUES LESS THAN (TO_DATE('2008-01-01','YYYY-MM-DD')),
....
);
查询数据要求
1、根据Equip_id,ifindex 进行分组，在一个时间域中 求个分组中RXBPS的最大值和TXBPS的最大值，
2、并求最大值所在的时间（若多个相同最大值取最后的时间） 
3、获得以TXBPS最大值为标准的，最大的n条记录。
4、同时在该时间域中在最大值时间点之后，比RXBPS（TXBPS）最大值小的的最靠前的一个时间点为恢复时间。最终结果 为 Equip_id,ifinde,RXBPS的最大值,RXBPS的最大值所在时间，RXBPS的最大值恢复时间,TXBPS的最大值,TXBPS的最大值所在时间，TXBPS的最大值恢复时间.数据库数据级在 2-3亿条数据之前我们做了一个查询方法不过效率很低。30多分钟都出不来数据。请大家帮帮忙啊。客户成天催，都弄了1个多月了。下面把我们的sql方案给大家看看。抛砖引玉
select vt. *,
       (select to_char(min(collectdate), 'YYYY-MM-DD HH24:mi:ss')
          from NETWORK_FLOWTABLE
         where COLLECTDATE > to_date(maxttime, 'YYYY-MM-DD HH24:mi:ss')
           and COLLECTDATE <=
               to_date('2011-05-05 00:00:00', 'YYYY-MM-DD HH24:mi:ss') --结束时间
           and equip_id = vt.equip_id
           and ifindex = vt.IFINDEX
           and TXBPS < vt.t_max) tptime,
       (select to_char(min(collectdate), 'YYYY-MM-DD HH24:mi:ss')
          from NETWORK_FLOWTABLE
         where COLLECTDATE > to_date(maxrtime, 'YYYY-MM-DD HH24:mi:ss')
           and COLLECTDATE <=
               to_date('2011-05-05 00:00:00', 'YYYY-MM-DD HH24:mi:ss')  --结束时间
           and equip_id = vt.equip_id
           and ifindex = vt.IFINDEX
           and RXBPS < vt.r_max) rptime
  from (select v. *,
               (select to_char(max(collectdate), 'YYYY-MM-DD HH24:mi:ss')
                  from NETWORK_FLOWTABLE
                 where COLLECTDATE >=
                       to_date('2009-05-05 00:00:00', 'YYYY-MM-DD HH24:mi:ss') --开始时间
                   and COLLECTDATE <=
                       to_date('2011-05-05 00:00:00', 'YYYY-MM-DD HH24:mi:ss')  --结束时间
                   and equip_id = v.equip_id
                   and ifindex = v.IFINDEX
                   and TXBPS = v.t_max) maxttime,
               (select to_char(max(collectdate), 'YYYY-MM-DD HH24:mi:ss')
                  from NETWORK_FLOWTABLE
                 where COLLECTDATE >=
                       to_date('2009-05-05 00:00:00', 'YYYY-MM-DD HH24:mi:ss') --开始时间
                   and COLLECTDATE <=
                       to_date('2011-05-05 00:00:00', 'YYYY-MM-DD HH24:mi:ss') -结束时间
                   and equip_id = v.equip_id
                   and ifindex = v.IFINDEX
                   and RXBPS = v.r_max) maxrtime
          from (select EQUIP_ID, r_avg, t_max, r_max, t_avg, IFINDEX
                  from (select EQUIP_ID,
                               IFINDEX,
                               max(TXBPS) as t_max,
                               avg(TXBPS) as t_avg,
                               max(RXBPS) as r_max,
                               avg(RXBPS) as r_avg
                          from NETWORK_FLOWTABLE n
                         where COLLECTDATE >=
                               to_date('2009-05-05 00:00:00',
                                       'YYYY-MM-DD HH24:mi:ss')
                           and COLLECTDATE <=
                               to_date('2011-05-05 00:00:00',
                                       'YYYY-MM-DD HH24:mi:ss')
                         group by IFINDEX, EQUIP_ID
                         order by t_max desc)
                 where rownum <= 10  --TOPN
                   ) v) vt

Answer 1

看了兄弟的sql语句，提一下几点意见：
1：select v.*及select vt.* 应写成相应的字段名出来，要不让还要Oracle数据库解析，最好都写成大写的字符
2：因为表包含的列值不是太多所以，结构不用调整，主要是记录条数太多，对该表的建议是创建相应的索引，但不要太多
3：rownum是oracle中内置的序列速度快，还有一个Rowid可以从这里考虑一下
如果按上边的方法优化至少能提高5%以上（最保守的估计了!）
顺便问一下有必要保留这么多的记录吗？
一般情况下这样的表如果利用率不是太高的话，可以考虑再见一个固定的表来定期保存你查询出的结果，至于定时生成数据你可以考虑Oracle的job和计划任务

Answer 2

1、例子是我测试的sql，现场sql是没有用* 的。
2、这个我也没辙啊，客户要至少一年内的详细数据（一年就有1亿左右数据）。（我们做了一个数据仓库对数据进行了分级汇总日，周，月，年）可是客户还是要看这个详细信息表，目前我们也在跟客户争取查看汇聚数
据。
3、这个我看看看看。通过Rowid相当于冲数据块中直接查询应该速度上快不少。
先谢谢哦！还有那位大虾有更好的建议么

Answer 3

刚才看看 用Rowid 有一个问题耶。  我要求的是TopN，我如何去确认TopN 的Rowid。
如果先用rownum把topN的Rowid求得，还就没有意义了。还是我理解的有错误。rowid有其他的用法

Answer 4

还有看你的SQL语句用了很多函数啊，建议建立函数索引或者建合适的索引，就是如果表中的数据不经常修改的话建议使用MV，如果经常排序的话，ＰＧＡ也要适当的增大。

Answer 5

1、忘了吧索引写上了。 表中的equip_id,ifindex 为主键。
2、此表是采集性能表。基本每5分钟都要插入几百条数据

Answer 6

不太清楚具体的数据分布。个人觉得分区可能还是太大。
看看还有其他适合做分区的么，比如ifindex 。做二级或者三级分区。

Answer 7

能不能用plsql分步骤抓取数据呢