select mm.C_MeasureDocId,
mm.C_MeasurebeginDate,
C_SendUnit,
C_ReceiveUnitID,
mm.I_MeasureFashion,
mm.C_SupplyierDes,
mm.C_CarrytoolMainId,
mm.N_GrossWeight,
mm.N_TareWeight,
mm.C_OrderID,
mm.C_MaterielID,
mm.C_MaterielDes,
mm.C_ReceiveUnitID,
mm.N_NetWeight,
mm.N_ContainerWeight,
mm.C_GrossDateTime,
mm.C_TareDateTime,
mm.D_ContainerDate,
mm.D_NetDateTime,
mm.C_MeasureProperties,
mm.C_ContainerId,
mm.C_NetWeightStation,
mm.C_Maintainer,
mm.C_ClientDes,
mm.C_State,
mm.C_UPLOADPHOTO,
fh.c_factorydes,
(select USERNAME from TS_USER where USERID = ms.C_GSTAFFID) as C_EXTENDFIELDH,
(select USERNAME from TS_USER where USERID = ms.C_TSTAFFID) as C_EXTENDFIELDG
from TM_MeasureMain mm
inner join TM_MeasureSub ms on mm.C_MeasureDocId = ms.C_MeasureDocId
left join tb_factory fh on mm.C_ReceiveUnitID = fh.c_factoryid
where (mm.C_State = '0' or
(mm.C_TRAINTOCARFLAG = '1' and mm.C_State = '1'))
and mm.C_CarryToolsTypeID = 'QC'
and (mm.C_GrossDateTime like '2013-11-10%' or
mm.C_TareDateTime like '2013-11-10%' or
mm.D_CONTAINERDATE like '2013-11-10%' or
mm.D_NetDateTime like '2013-11-10%')
order by mm.c_measuredocid desc
oracleoracle查询查询语句优化优化查询
mm.C_MeasurebeginDate,
C_SendUnit,
C_ReceiveUnitID,
mm.I_MeasureFashion,
mm.C_SupplyierDes,
mm.C_CarrytoolMainId,
mm.N_GrossWeight,
mm.N_TareWeight,
mm.C_OrderID,
mm.C_MaterielID,
mm.C_MaterielDes,
mm.C_ReceiveUnitID,
mm.N_NetWeight,
mm.N_ContainerWeight,
mm.C_GrossDateTime,
mm.C_TareDateTime,
mm.D_ContainerDate,
mm.D_NetDateTime,
mm.C_MeasureProperties,
mm.C_ContainerId,
mm.C_NetWeightStation,
mm.C_Maintainer,
mm.C_ClientDes,
mm.C_State,
mm.C_UPLOADPHOTO,
fh.c_factorydes,
(select USERNAME from TS_USER where USERID = ms.C_GSTAFFID) as C_EXTENDFIELDH,
(select USERNAME from TS_USER where USERID = ms.C_TSTAFFID) as C_EXTENDFIELDG
from TM_MeasureMain mm
inner join TM_MeasureSub ms on mm.C_MeasureDocId = ms.C_MeasureDocId
left join tb_factory fh on mm.C_ReceiveUnitID = fh.c_factoryid
where (mm.C_State = '0' or
(mm.C_TRAINTOCARFLAG = '1' and mm.C_State = '1'))
and mm.C_CarryToolsTypeID = 'QC'
and (mm.C_GrossDateTime like '2013-11-10%' or
mm.C_TareDateTime like '2013-11-10%' or
mm.D_CONTAINERDATE like '2013-11-10%' or
mm.D_NetDateTime like '2013-11-10%')
order by mm.c_measuredocid desc
oracleoracle查询查询语句优化优化查询
解决方案 »
- 32位的PLSQL登录64位的ORA11g问题
- 求几个oracle的dll文件
- 大家帮忙 -oracle安装!
- 触发器问题
- 如何进行快速update
- 一个关于oracle的存储过程该怎么调用啊,为什么我总是出错?
- SQL里面的 a:=4 这个样子是啥意思?
- {开了三贴了}求一sql语句.@@@@@@@@@@。。。。高人进呀!!!
- 如何将一个表的所有数据记录用 insert 语句插入另一个表中,表功能相近
- 提示:ORA-01031;insufficient privileges什么原因
- 求一条sql语句
- 机器上已经安装SQL Sever2005,请问在机器上再安装Oracle9i或Oracle10会不会有冲突?
我也不是很懂优化,但是就你的SQL来看:where (mm.C_State = '0' or (mm.C_TRAINTOCARFLAG = '1' and mm.C_State = '1'))
像这种 不知道你的列到底是什么类型的,这样写可能有隐式的转换,这样就不会用到索引。还有
mm.C_GrossDateTime like '2013-11-10%' or mm.C_TareDateTime like '2013-11-10%' or mm.D_CONTAINERDATE like '2013-11-10%' or mm.D_NetDateTime like '2013-11-10%'
这个 我觉得你完全可以用日期的比较实现, 不要用like 这种东西。而且你上面inner join TM_MeasureSub ms on mm.C_MeasureDocId = ms.C_MeasureDocId left join tb_factory fh on mm.C_ReceiveUnitID = fh.c_factoryid
这个我觉得很乱。
你可以自己调整。
mm.C_GrossDateTime like '2013-11-10%' ,如果mm.C_GrossDateTime是varchar2类型的,
改为instr(mm.C_GrossDateTime,'2013-11-10') = 1 如何?其他地方太乱....
(select USERNAME from TS_USER where USERID = ms.C_GSTAFFID) as C_EXTENDFIELDH,
(select USERNAME from TS_USER where USERID = ms.C_TSTAFFID) as C_EXTENDFIELDG可以用连接吧,这样比子查询稍微快一点。
mm.C_GrossDateTime like '2013-11-10%' ,如果mm.C_GrossDateTime是varchar2类型的,
改为instr(mm.C_GrossDateTime,'2013-11-10') = 1 如何?其他地方太乱....
前面写错了- -!mm.C_GrossDateTime like '2013-11-10%' ,如果mm.C_GrossDateTime是varchar2类型的,
改为instr(mm.C_GrossDateTime,'2013-11-10') = 0 如何?
如果是NVARCHAR 那这一写就没问题, 你的这些时间字段是varchar类型还是date类型? 有没有索引?如果是varchar类型 像这种前端匹配也是可以的。如果是date类型 并且有索引,你用这种就可能有问题, 你要取得'2013-11-10' 这一天的数据, 你可以用mm.C_GrossDateTime>to_date('2013-11-09 23:59:59','YYYY-MM-DD HH24:MI:SS') AND mm.C_GrossDateTime<to_date('2013-11-11 00:00:00','YYYY-MM-DD HH24:MI:SS')去替代 mm.C_GrossDateTime like '2013-11-10%'。 如果相关字段有索引 用union all 替代or。
把你觉得能过滤较多数据的where 条件放到下面去,一般where条件的解析是自下而上的,也就是从你最下的一个where条件开始过滤。我也不是很了解SQL 调优, 但是这些应该是比较基本的。
这些字段都加索引
mm.C_GrossDateTime like '2013-11-10%' ,如果mm.C_GrossDateTime是varchar2类型的,
改为instr(mm.C_GrossDateTime,'2013-11-10') = 1 如何?其他地方太乱....这个方法也不一定好啊,假如这个字段上有索引,那进行instr进行转换后就根本走不到索引的,速度会更慢。
(select USERNAME from TS_USER where USERID = ms.C_GSTAFFID) as C_EXTENDFIELDH,
(select USERNAME from TS_USER where USERID = ms.C_TSTAFFID) as C_EXTENDFIELDG可以用连接吧,这样比子查询稍微快一点。
这个也得看数据吧还有userid是否有索引,不一定子查询就是更慢的、
mm.C_GrossDateTime like '2013-11-10%' ,如果mm.C_GrossDateTime是varchar2类型的,
改为instr(mm.C_GrossDateTime,'2013-11-10') = 1 如何?其他地方太乱....
前面写错了- -!mm.C_GrossDateTime like '2013-11-10%' ,如果mm.C_GrossDateTime是varchar2类型的,
改为instr(mm.C_GrossDateTime,'2013-11-10') = 0 如何?
不行。。更慢了 里面date字段都是nvarchar2 类型