select * from (
select v.*,rownum rnm from (
select distinct(a.CALL_ID),a.OPERATOR_NO,a.OPERATOR_NAME,a.SRC_PHONE_NO,a.QUOTE_PHONE_NO,d.TEL_NAME,d.UNIT_NAME,d.FIRST_CODE || ' ' || d.SECOND_CODE,
e.Dest_Id,e.CALL_LEN,a.QUERY_COUNT,b.FILE_NAME,c.satisfy_degree,to_char(to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'),
b.is_vip,b.sound_id,rownum rn, a.QUOTE_TIME, a.QUOTE_TYPE, d.parent_tel_id, a.CREATE_TIME, a.CUR_CODE_VALUE
from PNDD_QUOTE_DETAIL a,FULLTIME_TAPE_SOUND b,callin_satisfy c, PNDD_TEL_INFO d, callin_detail e
where a.CALL_ID=b.CALL_ID(+)
and a.CALL_ID=c.CALL_ID(+)
and a.CALL_ID=e.CALL_ID(+)
and a.QUOTE_PHONE_NO = d.TEL_NUMBER(+)
and 0 = e.TYPE(+)
and a.create_time >= to_date('2007-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_time <= to_date('2007-09-26 23:59:59','yyyy-mm-dd hh24:mi:ss')
and (to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss')-a.create_time)*24*3600 >= 0
and (to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss')-a.create_time)*24*3600 <= 99999999
order by a.create_time desc
) v
where rownum <= 40)
where rnm >= 21
我的速度是13’现在还想更快点
select v.*,rownum rnm from (
select distinct(a.CALL_ID),a.OPERATOR_NO,a.OPERATOR_NAME,a.SRC_PHONE_NO,a.QUOTE_PHONE_NO,d.TEL_NAME,d.UNIT_NAME,d.FIRST_CODE || ' ' || d.SECOND_CODE,
e.Dest_Id,e.CALL_LEN,a.QUERY_COUNT,b.FILE_NAME,c.satisfy_degree,to_char(to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'),
b.is_vip,b.sound_id,rownum rn, a.QUOTE_TIME, a.QUOTE_TYPE, d.parent_tel_id, a.CREATE_TIME, a.CUR_CODE_VALUE
from PNDD_QUOTE_DETAIL a,FULLTIME_TAPE_SOUND b,callin_satisfy c, PNDD_TEL_INFO d, callin_detail e
where a.CALL_ID=b.CALL_ID(+)
and a.CALL_ID=c.CALL_ID(+)
and a.CALL_ID=e.CALL_ID(+)
and a.QUOTE_PHONE_NO = d.TEL_NUMBER(+)
and 0 = e.TYPE(+)
and a.create_time >= to_date('2007-08-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and a.create_time <= to_date('2007-09-26 23:59:59','yyyy-mm-dd hh24:mi:ss')
and (to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss')-a.create_time)*24*3600 >= 0
and (to_date(a.QUOTE_TIME,'yyyy-mm-dd hh24:mi:ss')-a.create_time)*24*3600 <= 99999999
order by a.create_time desc
) v
where rownum <= 40)
where rnm >= 21
我的速度是13’现在还想更快点
解决方案 »
- 数据库编程平台。。!!
- 求一个存储过程
- Oracle数据库备份的问题 :(
- ORACLE 引索 是什么意思?? 怎么创建 基本操作,谁能告诉我
- 新手问题2:在设置了pre_page_sga = true后重起Oracle失败,错误是 ORA-00445:background process "PMON" did not start after 120 seco
- 哪里有oracle 9i 补丁的下载?
- 请问如何计算oracle表容量?
- 关于oracle中SQL语句中 Distinct 的用法求助!
- 谁能帮忙看看这个存储过程错在那里?
- 如何判断两个时间差小于5分钟的记录,并将他们合并只取时间较早的记录???
- 请教一个关于合计的问题
- 关于Oracle SQL速度优化!急!谢谢!!
第二,and (to_date(a.QUOTE_TIME, "yyyy-mm-dd hh24:mi:ss ")-a.create_time)*24*3600 >= 0
and (to_date(a.QUOTE_TIME, "yyyy-mm-dd hh24:mi:ss ")-a.create_time)*24*3600 <= 99999999
可做如下修改:将to_date(a.QUOTE_TIME, "yyyy-mm-dd hh24:mi:ss ") 赋给一个变量v_QUOTE_TIME ,然后变为:and v_QUOTE_TIME >=a.create_time
and v_QUOTE_TIME- 99999999/(24*3600) <=a.create_time 如果create_time有index,将会有很大提高
第三:oracle新语法:
select * from(
select * row_number() OVER(order by cof.createdatetime) as rank from co_cof cof
) a
where rank between 2 and 5 这是按照createdatetime 升序排列然后取第二到第五条记录,你可以自己仿照着改改看
and a.create_time <= to_date( "2007-09-26 23:59:59 ", "yyyy-mm-dd hh24:mi:ss ")
替换为
and a.create_time (between to_date( "2007-08-01 00:00:00 ", "yyyy-mm-dd hh24:mi:ss ") and a.create_time <= to_date( "2007-09-26 23:59:59 ", "yyyy-mm-dd hh24:mi:ss "))
试试