我要从3个表中查数据 worklist ,workbasket ,work
需求是这样的当worlist 和workbasket表中存在跟work表匹配 b.pxInsName = a.pxRefObjectInsName时取worklist ,workbasket 表中的PXASSIGNEDOPERATORID 其他字段都取自work的字段 当 worlist 和workbasket表中不存在跟work表匹配 b.pxInsName = a.pxRefObjectInsName时取空字符'',
由于数据量比较大而且我用了not exists所以现在查询速度很慢请问给位大侠有没有什么好的解决办法
( Select b.pxInsName AS "pxInsName",a.PXASSIGNEDOPERATORID AS "AssignTo",b.THERAPEUTICAREA AS "TherapeuticArea" FROM worklist a , work b WHERE b.pxInsName = a.pxRefObjectInsName AND b.pxInsName is not null UNION
Select b.pxInsName AS "pxInsName",a.PXASSIGNEDOPERATORID AS "AssignTo",b.THERAPEUTICAREA AS "TherapeuticArea" FROM workbasket a , work b WHERE b.pxInsName = a.pxRefObjectInsName AND b.pxInsName is not null
UNION all Select b.pxInsName AS "pxInsName",'' AS "AssignTo",b.THERAPEUTICAREA AS "TherapeuticArea FROM work b WHERE b.pxInsName is not null and not exists (select a.pxRefObjectInsName from workbasket a where b.PXINSNAME=a.PXREFOBJECTINSNAME union all select c.pxRefObjectInsName from worklist c where b.PXINSNAME=c.PXREFOBJECTINSNAME) )ORDER BY "pxInsName" DESC
需求是这样的当worlist 和workbasket表中存在跟work表匹配 b.pxInsName = a.pxRefObjectInsName时取worklist ,workbasket 表中的PXASSIGNEDOPERATORID 其他字段都取自work的字段 当 worlist 和workbasket表中不存在跟work表匹配 b.pxInsName = a.pxRefObjectInsName时取空字符'',
由于数据量比较大而且我用了not exists所以现在查询速度很慢请问给位大侠有没有什么好的解决办法
( Select b.pxInsName AS "pxInsName",a.PXASSIGNEDOPERATORID AS "AssignTo",b.THERAPEUTICAREA AS "TherapeuticArea" FROM worklist a , work b WHERE b.pxInsName = a.pxRefObjectInsName AND b.pxInsName is not null UNION
Select b.pxInsName AS "pxInsName",a.PXASSIGNEDOPERATORID AS "AssignTo",b.THERAPEUTICAREA AS "TherapeuticArea" FROM workbasket a , work b WHERE b.pxInsName = a.pxRefObjectInsName AND b.pxInsName is not null
UNION all Select b.pxInsName AS "pxInsName",'' AS "AssignTo",b.THERAPEUTICAREA AS "TherapeuticArea FROM work b WHERE b.pxInsName is not null and not exists (select a.pxRefObjectInsName from workbasket a where b.PXINSNAME=a.PXREFOBJECTINSNAME union all select c.pxRefObjectInsName from worklist c where b.PXINSNAME=c.PXREFOBJECTINSNAME) )ORDER BY "pxInsName" DESC
not exists (select a.pxRefObjectInsName from workbasket a where b.PXINSNAME=a.PXREFOBJECTINSNAME)
and not exists(select c.pxRefObjectInsName from worklist c where b.PXINSNAME=c.PXREFOBJECTINSNAME)
对你比较的字段增加索引试试呢?
worklist 表的 pxRefObjectInsName 和 work表的pxInsName 以及workbasket表的 pxRefObjectInsName
--在关联字段建个索引
--试试 这样
Select b.pxInsName AS "pxInsName",
case when b.pxInsName = a.pxRefObjectInsName then a.PXASSIGNEDOPERATORID
when b.pxInsName = c.pxRefObjectInsName then c.PXASSIGNEDOPERATORID end AS "AssignTo",
b.THERAPEUTICAREA AS "TherapeuticArea"
FROM worklist a , workbasket c ,work b
where b.pxInsName = a.pxRefObjectInsName AND b.pxInsName = c.pxRefObjectInsName AND
b.pxInsName is not null
union all
Select b.pxInsName AS "pxInsName",'' AS "AssignTo",b.THERAPEUTICAREA AS "TherapeuticArea
FROM work b
WHERE b.pxInsName is not null
and not exists (select 1 from workbasket a where b.PXINSNAME=a.PXREFOBJECTINSNAME)
and not exists (select c.pxRefObjectInsName from worklist c where b.PXINSNAME=c.PXREFOBJECTINSNAME)