union all 的效率问题

asssf3214 2009-12-18 03:48:04
select Substation.Substation_ID Substation_ID, Substation.NAME SubstationNAME, PowerTransformer.PowerTransformer_ID Equipment_ID, PowerTransformer.NAME EquipmentNAME, Substationuriandid.classid Substationuriandidclassid, Substationuriandid.uri Substationuriandiduri, Equipmenturiandid.classid Equipmenturiandidclassid, Equipmenturiandid.uri Equipmenturiandiduri from Substation join PowerTransformer on exists (select 1 from CONTAINS_EQUI_MEMBEROF_EQUI where CONTAINS_EQUI_MEMBEROF_EQUI.MEMBEROF_EQUIPMENTCONTAINER_ID = Substation.Substation_ID and CONTAINS_EQUI_MEMBEROF_EQUI.CONTAINS_EQUIPMENTS_ID = PowerTransformer.PowerTransformer_ID) join uri_resourceid Substationuriandid on Substationuriandid.resource_id = Substation.Substation_ID join uri_resourceid Equipmenturiandid on Equipmenturiandid.resource_id = PowerTransformer.PowerTransformer_ID
union all
select Substation.Substation_ID Substation_ID, Substation.NAME SubstationNAME, Fuse.Fuse_ID Equipment_ID, Fuse.NAME EquipmentNAME, Substationuriandid.classid Substationuriandidclassid, Substationuriandid.uri Substationuriandiduri, Equipmenturiandid.classid Equipmenturiandidclassid, Equipmenturiandid.uri Equipmenturiandiduri from Substation join Fuse on exists (select 1 from CONTAINS_EQUI_MEMBEROF_EQUI where CONTAINS_EQUI_MEMBEROF_EQUI.MEMBEROF_EQUIPMENTCONTAINER_ID = Substation.Substation_ID and CONTAINS_EQUI_MEMBEROF_EQUI.CONTAINS_EQUIPMENTS_ID = Fuse.Fuse_ID) join uri_resourceid Substationuriandid on Substationuriandid.resource_id = Substation.Substation_ID join uri_resourceid Equipmenturiandid on Equipmenturiandid.resource_id = Fuse.Fuse_ID union all ......union all ...
之后都是类似的查询语句.开始执行都很快,在1秒之内,到第13个union all的时候执行速度变成5秒,14个是20秒,再往后就等不到了,怎么解决啊?
...全文
423 2 打赏 收藏 举报
写回复
2 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zhangwonderful 2009-12-18
  • 打赏
  • 举报
回复
如果union all太多的话,建议使用存储过程,建立一张临时表来存放结果集,每一个union all后的select 语句单独执行插入到此临时表
asssf3214 2009-12-18
  • 打赏
  • 举报
回复
只有第一个union查询结果比较多,后面几乎都没结果
相关推荐
发帖
Oracle 高级技术

3472

社区成员

Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
帖子事件
创建了帖子
2009-12-18 03:48
社区公告
暂无公告