石桥曾某
2018-01-22 08:45
采纳率: 28.6%
浏览 839

oracle sql优化的问题 各位大神有什么办法优化一下吗

SELECT a.JIAODU_X AS "xPoint", a.CAIJITIME AS "yPoint", a.SENSORNO AS "sensorno", a.SENSEID AS "sensorid"
FROM WM_DRAWPONIT_DB a where a.CAIJITIME > =to_date( '2017-05-28 14:50:16','yyyy-mm-dd hh24:mi:ss') and
a.CAIJITIME < =to_date('2018-01-22 14:06:08','yyyy-mm-dd hh24:mi:ss') and
a.SENSEID ='a9b78b7a524843be87b254303b3c8bb1' order by a.CAIJITIME
加上时间排序后非常的慢 要20多秒

加上时间排序
图片说明

去掉时间排序
图片说明
各位大神有什么办法优化吗 小弟对sql不太懂

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

8条回答 默认 最新

  • zoujianyun3 2018-01-23 02:15
    已采纳

    SELECT
    a.JIAODU_X AS "xPoint",
    a.CAIJITIME AS "yPoint",
    a.SENSORNO AS "sensorno",
    a.SENSEID AS "sensorid"
    from(select
    JIAODU_X
    CAIJITIME
    SENSORNO
    SENSEID
    FROM WM_DRAWPONIT_DB
    where CAIJITIME > =to_date( '2017-05-28 14:50:16','yyyy-mm-dd hh24:mi:ss')
    and CAIJITIME < =to_date('2018-01-22 14:06:08','yyyy-mm-dd hh24:mi:ss')
    and SENSEID ='a9b78b7a524843be87b254303b3c8bb1' )a
    order by a.CAIJITIME

    点赞 评论
  • wangran181 2018-01-22 08:49

    排序本来就会变慢,加上行数限制试试
    SELECT a.JIAODU_X AS "xPoint",
    a.CAIJITIME AS "yPoint",
    a.SENSORNO AS "sensorno",
    a.SENSEID AS "sensorid"
    FROM WM_DRAWPONIT_DB a
    where a.CAIJITIME > =
    to_date('2017-05-28 14:50:16', 'yyyy-mm-dd hh24:mi:ss')
    and a.CAIJITIME < =
    to_date('2018-01-22 14:06:08', 'yyyy-mm-dd hh24:mi:ss')
    and a.SENSEID = 'a9b78b7a524843be87b254303b3c8bb1'
    and rownum<=10
    order by a.CAIJITIME

    点赞 评论
  • weixin_41367942 2018-01-22 08:54

    排序本来就慢,你的情况看看可不可以ORDER by 排序字段 OFFSET 0 ROW FETCH NEXT 100 ROWS ONLY

    点赞 评论
  • hongtashanggg 2018-01-22 09:06

    select * from (SELECT a.JIAODU_X AS "xPoint", a.CAIJITIME AS "yPoint", a.SENSORNO AS "sensorno", a.SENSEID AS "sensorid"
    FROM WM_DRAWPONIT_DB a where a.CAIJITIME > =to_date( '2017-05-28 14:50:16','yyyy-mm-dd hh24:mi:ss') and
    a.CAIJITIME < =to_date('2018-01-22 14:06:08','yyyy-mm-dd hh24:mi:ss') and
    a.SENSEID ='a9b78b7a524843be87b254303b3c8bb1' ) b order by b.CAIJITIME
    你试一试这个命令

    点赞 评论
  • 行者彡 2018-01-22 09:50

    分组在排序不就可以了么;

    点赞 评论
  • 请叫我站长 2018-01-22 10:05

    如果数据量比较多,而又不长变动的话,可以考虑加索引

    点赞 评论
  • black_night_raid 2018-01-22 10:07

    SQL优化你去试试plsql封装存储过程,虽然我不怎么熟悉

    点赞 评论
  • sinat_41641294 2018-01-23 02:33

    不排序跑的快是因为走的SENSEID的索引,排序跑的慢是因为时间上没有索引跑的全表扫描。你需要在时间上建立索引,然后强制他走时间索引。
    用法:select /*+index (table_name index_name)*/values from table;

    你也可以在排序的时候,强制他走SENSEID的索引,看下能不能变快,不然就得用上面的方法

    点赞 评论

相关推荐 更多相似问题