select distinct(i.SEQ_ID),i.VALUE_TEXT
from ORDER h, INSTANCE i
where
to_date(h.TIMEIN) = to_date(sysdate-1)
and
h.TASKID = 10062
and
h.STATUSID = 10003
and
i.ORDERTYPEID = 10009
and
i.DATAID = 10092
and
(i.VALUE_TEXT ='13' or i.VALUE_TEXT ='16')
and
i.SEQ_ID = h.SEQ_IDsql如上是两表联合查询,条件多数又是海量数据,所以查询巨慢,有没有优化的办法,请牛人指教,3Q~~~
from ORDER h, INSTANCE i
where
to_date(h.TIMEIN) = to_date(sysdate-1)
and
h.TASKID = 10062
and
h.STATUSID = 10003
and
i.ORDERTYPEID = 10009
and
i.DATAID = 10092
and
(i.VALUE_TEXT ='13' or i.VALUE_TEXT ='16')
and
i.SEQ_ID = h.SEQ_IDsql如上是两表联合查询,条件多数又是海量数据,所以查询巨慢,有没有优化的办法,请牛人指教,3Q~~~
2.尽量不用distinct ,order by 等,因为排序需要时间
3.做组合索引,需要用到函数判断的做函数索引
4.where子句尽量不要用or ,最好改为union all
5.说到底,还是建议你多看看执行计划,分析以上几点在做之前和之后的差别
from ORDER h, INSTANCE i
where i.SEQ_ID = h.SEQ_ID
and h.TIMEIN = sysdate-1 ---都是date类型字段的话,没有必要转化
and h.TASKID = 10062
and h.STATUSID = 10003
and i.ORDERTYPEID = 10009
and i.DATAID = 10092
and (i.VALUE_TEXT ='13' or i.VALUE_TEXT ='16')表连接的字段上分别建立索引:INSTANCE表SEQ_ID字段上单独建立索引,ORDER表SEQ_ID字段上单独建立索引;ORDER表考虑是否在TIMEIN字段上单独建立索引,通过时间限定返回的数据量;TASKID和STATUSID字段上建立联合索引或者分别单独建立索引,根据数据量和数据分布而定;INSTANCE表考虑是否在ORDERTYPEID字段上和DATAID字段上分别单独建立索引;
(也可以写成 and h.TIMEIN between to_char(sysdate-1,'yyyy-mm-dd) and to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss')-0.00001 )
and (i.VALUE_TEXT ='13' or i.VALUE_TEXT ='16') 这个子句改用in会稍微提高性能
from (select SEQ_ID
from ORDER
where TASKID = 10062
and STATUSID = 10003
and to_date(h.TIMEIN) = to_date(sysdate-1)
) h,
(select SEQ_ID,VALUE_TEXT
from INSTANCE
where ORDERTYPEID = 10009
and DATAID = 10092
and (VALUE_TEXT ='13' or VALUE_TEXT ='16' )
) i
where i.SEQ_ID = h.SEQ_ID