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个回答

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

qq_41669850
qq_41669850 加索引试试
2 年多之前 回复
zoujianyun3
zoujianyun3 子查询里是复制的少了逗号
2 年多之前 回复

排序本来就会变慢,加上行数限制试试
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

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

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
你试一试这个命令

qq_20507067
石桥曾某 提示 b.CAIJITIME 标识符无效
2 年多之前 回复

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

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

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

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

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐