select b.AreaName as 比,b.BuildName as dd,b.UnitNo as dds,b.FloorNo as fd43,b.DoorName as hgf,a.MeterNo as rwe,a.devicetypeno as dfs3,0 as fd from tdevice a,VAreaInfo b
where a.MeterNo = b.MeterNo and a.AreaGuid = b.AreaGuid and a.DEVICETYPENO = b.MeterType and a.AREAGUID=414 AND a.devicetypeno=16 and a.METERNO not in (select METERID
from twater where devicestatus=0 and to_char(DDate,'yyyymmdd') = to_char(to_date('2009-7-18 12:12:12','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')) order by b.AreaName,b.BuildName,b.UnitNo,b.FloorNo,b.DoorName
tdevice一个表, VAreaInfo 一个视图,各1万多条记录,
上面语句N慢,要2分钟左右,太受不了 了
where a.MeterNo = b.MeterNo and a.AreaGuid = b.AreaGuid and a.DEVICETYPENO = b.MeterType and a.AREAGUID=414 AND a.devicetypeno=16 and a.METERNO not in (select METERID
from twater where devicestatus=0 and to_char(DDate,'yyyymmdd') = to_char(to_date('2009-7-18 12:12:12','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')) order by b.AreaName,b.BuildName,b.UnitNo,b.FloorNo,b.DoorName
tdevice一个表, VAreaInfo 一个视图,各1万多条记录,
上面语句N慢,要2分钟左右,太受不了 了
解决方案 »
- 如何解决left join 被关联表不能带条件的问题
- ubuntu下装instant client的问题
- 我在ListCtrl中用SHGetFilelnfo插入系统图标为什么关闭后第二次打开就没有图标了
- oracle客户端如何建表?
- 【哭瞎了】怎麼也裝不上11g的數據庫
- oracle查询中一个超级郁闷的问题,急啊!!!
- 原数据库是AIX上的,我要导入到windows上
- 怎么写oracle的批处理文件!
- oracle performance manager数据库乱码??急!
- 如何删除刚刚插入表的那几行数据
- exp中关于设定filesize大小及压缩问题?在线等谢谢了!
- statspack报告里面的recursive cpu usage比较高是怎么回事?
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例子:
(高效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')
select b.AreaName as 比,b.BuildName as dd,b.UnitNo as dds,b.FloorNo as fd43,b.DoorName as hgf,a.MeterNo as rwe,a.devicetypeno as dfs3,0 as fd
from tdevice a,VAreaInfo b
where a.MeterNo = b.MeterNo and a.AreaGuid = b.AreaGuid and a.DEVICETYPENO = b.MeterType and a.AREAGUID=414 AND a.devicetypeno=16
and
not exists
(select METERID
from twater
where devicestatus=0 and to_char(DDate,'yyyymmdd') = to_char(to_date('2009-7-18 12:12:12','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')
and a.METERNO=METERID
)
order by b.AreaName,b.BuildName,b.UnitNo,b.FloorNo,b.DoorName
create index idx_t1 on twater(to_char(DDate, 'yyyymmdd'),devicestatus);-- 这个很重要
create index idx_t2 on tdevice(AREAGUID,devicetypeno);
create index idx_t3 on VAreaInfo(MeterNo,AreaGuid,MeterType);
建议:
你不是会用实体视图吗。把下面内容也建个视图。然就可以 不用not in 了,直接 inner join 到新的 实体视图。select METERNO from tdevice a where
a.METERNO not in
(select METERID
from twater
where devicestatus = 0
and to_char(DDate, 'yyyymmdd') =
to_char(to_date('2009-7-18 12:12:12', 'yyyy-mm-dd hh24:mi:ss'),
'yyyymmdd'))
to_char(DDate,'yyyymmdd') =
to_char(to_date('2009-7-18 12:12:12','yyyy-mm-dd hh24:mi:ss'),'yyyymmdd')
可以改成DDate>=to_date(...) and DDate<to_date(...)+1