求高效Sql查询-oracle

[soar110]

首先介绍下表结构

表先后进行过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

[soar110]

[Quote=引用 1 楼 chenhuizhouhb 的回复:]

看了兄弟的sql语句，提一下几点意见：
1：select v.*及select vt.* 应写成相应的字段名出来，要不让还要Oracle数据库解析，最好都写成大写的字符
2：因为表包含的列值不是太多所以，结构不用调整，主要是记录条数太多，对该表的建议是创建相应的索引，但不要太多
3：rownum是oracle中内置的序列速度快，还有一个Rowid可以从这里考虑一下
如果按上边的方法优化至……
[/Quote]
1、例子是我测试的sql，现场sql是没有用* 的。
2、这个我也没辙啊，客户要至少一年内的详细数据（一年就有1亿左右数据）。（我们做了一个数据仓库对数据进行了分级汇总日，周，月，年）可是客户还是要看这个详细信息表，目前我们也在跟客户争取查看汇聚数
据。
3、这个我看看看看。通过Rowid相当于冲数据块中直接查询应该速度上快不少。
先谢谢哦！

还有那位大虾有更好的建议么

[soar110]

[Quote=引用 1 楼 chenhuizhouhb 的回复:]

看了兄弟的sql语句，提一下几点意见：
1：select v.*及select vt.* 应写成相应的字段名出来，要不让还要Oracle数据库解析，最好都写成大写的字符
2：因为表包含的列值不是太多所以，结构不用调整，主要是记录条数太多，对该表的建议是创建相应的索引，但不要太多
3：rownum是oracle中内置的序列速度快，还有一个Rowid可以从这里考虑一下
如果按上边的方法优化至……
[/Quote]

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

还是我理解的有错误。rowid有其他的用法

[zty598416146]

[Quote=引用 1 楼 chenhuizhouhb 的回复:]

看了兄弟的sql语句，提一下几点意见：
1：select v.*及select vt.* 应写成相应的字段名出来，要不让还要Oracle数据库解析，最好都写成大写的字符
2：因为表包含的列值不是太多所以，结构不用调整，主要是记录条数太多，对该表的建议是创建相应的索引，但不要太多
3：rownum是oracle中内置的序列速度快，还有一个Rowid可以从这里考虑一下
如果按上边的方法优化至……
[/Quote]
还有看你的SQL语句用了很多函数啊，建议建立函数索引或者建合适的索引，就是如果表中的数据不经常修改的话建议使用MV，如果经常排序的话，ＰＧＡ也要适当的增大。

[soar110]

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

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

[chenhuizhouhb]

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

[rickfeng]

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

[changhe325]

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