对了,呵呵,这个SQL语句还是你写的呢,看这个帖子,就是你帮我写的这个SQL语句
http://expert.csdn.net/Expert/topic/1965/1965160.xml?temp=8.419436E-02
http://expert.csdn.net/Expert/topic/1965/1965160.xml?temp=8.419436E-02
解决方案 »
- 9i 2003 群集 思科 ASA-5520-K8 远程连接超时问题
- 关于dml语句返回值的问题~急啊
- 求教:根据表A,中Name列的值,显示表B中,在a.name里的属性的值,两表建关联.
- sqlload log Errors allowed: 50不解
- -----------在java中执行SQL的问题,奇怪的ora-01843: not a valid month,100分求助《急》《急》-------------
- SQL查询问题
- 数据库设计问题:是使用一个超大表还是使用多个小表?
- 这个表达式的结果是什么?
- 如何用sql语名创建序列?
- oracle里我用system连上去,建的表,用别的账户连上去看不到啊!
- oracle 数据迁移的 sqlServer的问题
- erwin在win2k下中文怎么是乱码?(在线等)
原:
15:54:05 SQL> select sj1,sj2 from
15:54:24 2 ( select t1.bbb sj1,t2.bbb sj2,t2.bbb-t1.bbb sjc from
15:54:27 3 (select rownum id1,bbb from tcd) t1,
15:54:27 4 (select rownum-1 id2,bbb from tcd) t2
15:54:27 5 where t1.id1=t2.id2(+)
15:54:27 6 ) where sjc>5/(24*60);修改:
11:20:42 SQL> select t1.bbb sj1,t2.bbb sj2 from
11:21:57 2 (select rownum id1,bbb from tcd) t1,
11:22:00 3 (select rownum-1 id2,bbb from tcd) t2
11:22:00 4 where t1.id1=t2.id2(+) and t2.bbb-t1.bbb>5/(24*60);SJ1 SJ2
------------------- -------------------
2003-06-25 12:15:00 2003-06-25 12:30:00
2003-06-25 12:45:00 2003-06-25 12:55:00实际:40建议:
mettime,metid,meastype,valuetype 这几个字段创建索引(如果里面没有null的话)
我根据你的SQL语句再修改一下我的sql语句试试先.
我没有大量的数据来测,
刚才的语句有可能比原先的更慢sql语句先不考虑
数据库sga设置是怎样的
服务器内存多少?
from (select t1.mettime sj1,t2.mettime sj2,t2.mettime-t1.mettime sjc
from (select rownum id1,mettime
from cmetvalue1
where metid =:an_metid
and meastype = :an_meastype
and valuetype = :an_valuetype
and mettime > :as_query_time1
and mettime <=:as_query_time2 ) t1,
(select id1-1,mettime
from t1 ) t2
where t1.id1=t2.id2(+))
where sjc>:an_sjc
order by sj1
to:bzszp(SongZip)
关于服务器的设置我一概不知,我才来这个公司一个星期,:(
我修改你的之后提示表或者视图不存在
select sj1 starttime,sj2 endtime
from (select t1.mettime sj1,t2.mettime sj2,t2.mettime-t1.mettime sjc
from (select rownum id1,mettime
from cmetvalue1
where metid =:an_metid
and meastype = :an_meastype
and valuetype = :an_valuetype
and mettime > :as_query_time1
and mettime <=:as_query_time2 ) t1,
(select id1-1 id2,mettime
from t1 ) t2
where t1.id1=t2.id2(+))
where sjc>:an_sjc
order by sj1
-1的话,t2.id从0开始,
跟后面t2.mettime-t1.mettime 有关系增大init.ora中shared_pool_size的值试试
看这个我感觉应该是 +1吧?init.ora这个文件在我的机子上没有找到.
13:36:34 SQL> select t1.bbb sj1,t2.bbb sj2,t2.bbb-t1.bbb sjc from
13:36:39 2 (select rownum id1,bbb from tcd) t1,
13:36:41 3 (select rownum-1 id2,bbb from tcd) t2
13:36:41 4 where t1.id1=t2.id2(+);SJ1 SJ2 SJC
------------------- ------------------- ---------
2003-06-25 12:00:00 2003-06-25 12:05:00 .00347222
2003-06-25 12:05:00 2003-06-25 12:10:00 .00347222
2003-06-25 12:10:00 2003-06-25 12:15:00 .00347222
2003-06-25 12:15:00 2003-06-25 12:30:00 .01041667
2003-06-25 12:30:00 2003-06-25 12:35:00 .00347222
2003-06-25 12:35:00 2003-06-25 12:40:00 .00347222
2003-06-25 12:40:00 2003-06-25 12:45:00 .00347222
2003-06-25 12:45:00 2003-06-25 12:55:00 .00694444
2003-06-25 12:55:00已选择9行。实际:2403
13:36:45 SQL> select t1.bbb sj1,t2.bbb sj2,t2.bbb-t1.bbb sjc from
13:36:55 2 (select rownum id1,bbb from tcd) t1,
13:36:55 3 (select rownum+1 id2,bbb from tcd) t2
13:37:02 4 where t1.id1=t2.id2(+);SJ1 SJ2 SJC
------------------- ------------------- ---------
2003-06-25 12:00:00
2003-06-25 12:05:00 2003-06-25 12:00:00 -.0034722
2003-06-25 12:10:00 2003-06-25 12:05:00 -.0034722
2003-06-25 12:15:00 2003-06-25 12:10:00 -.0034722
2003-06-25 12:30:00 2003-06-25 12:15:00 -.0104167
2003-06-25 12:35:00 2003-06-25 12:30:00 -.0034722
2003-06-25 12:40:00 2003-06-25 12:35:00 -.0034722
2003-06-25 12:45:00 2003-06-25 12:40:00 -.0034722
2003-06-25 12:55:00 2003-06-25 12:45:00 -.0069444已选择9行。