Oracle SQL语句优化之UNION

问题描述：印尼客户发现，Peoplesoft Finance 系统中一个页面存在性能问题。点击页面的一个链接，处于卡住状态。要等十几分钟才可进入链接页面。初步怀疑，SQL语句性能不好导致。

处理步骤：
1）开启peopelosft PIA SQL trace，锁定哪条SQL语句。

在登录Peoplesoft Finance时，url添加参数trace=y。



使用FTP到服务器上，获取trace文件。分析消耗时间列，发现一个view存在性能问题。
VIEW：PS_PV_PERF_SR_VW。该view是7层select嵌套。


2）查看是否在基表存在索引。
    存在。

3）查看执行计划，添加新索引后，没有作用。

4）使用oracle sql tuning advisor (STA)，发现语句使用了union，导致出现性能问题。


PS： 当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以Oracle UNION ALL的方式被合并，然后在输出最终结果前进行排序。假如用 Oracle UNION ALL替代UNION，这样排序就不是必要了。效率就会因此得到提高。需要注意的是，Oracle UNION ALL将重复输出两个结果集合中相同记录。因此各位还是要从业务需求分析使用Oracle UNION ALL的可行性。 UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存。对于这块内存的优化也是相当重要的。（摘自51CTO）

5）从业务角度考虑，不能保存重复数据，所以不能使用UNION ALL 代替 UNION。
仔细研究SQL语句。最终重写。使用左连接代替UNION。之后，运行时间大幅下降。

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ A.SETID

 ,A.INV_ITEM_ID

 ,A.VENDOR_SETID

 ,A.VENDOR_ID

 ,A.CATEGORY_ID

 ,A.UNIT_MEASURE_STD

 ,G.UNIT_OF_MEASURE

 ,A.DESCR

 ,A.DESCR254_MIXED

 ,A.ITM_ID_VNDR

 ,A.TREE_NAME

 ,A.VNDR_CATALOG_ID

 ,A.PRICE_LIST

 ,A.ITM_VNDR_PRIORITY

 ,A.CURRENCY_CD_BASE

 ,A.VNDR_NAME1

 ,G.EFFDT

 ,G.VNDR_LOC

 ,G.PRICE_VNDR

 ,G.CURRENCY_CD

 ,A.PV_MIN_PRIORITY

 ,A.INVENTORY_ITEM

  FROM PS_PV_PERF_V1_VW A

  , PS_ITM_VNDR_UOM_PR G

 WHERE A.INV_ITEM_ID= G.INV_ITEM_ID

   AND A.SETID = G.SETID

   AND A.VENDOR_SETID = G.VENDOR_SETID

   AND A.VENDOR_ID = G.VENDOR_ID

   AND A.VNDR_LOC = G.VNDR_LOC

   AND G.EFFDT = (

 SELECT /* INDEX PS_ITM_VNDR_UOM_PR (PSAITM_VNDR_UOM_PR), ALL_ROWS */ MAX(EFFDT)

  FROM PS_ITM_VNDR_UOM_PR

 WHERE INV_ITEM_ID = G.INV_ITEM_ID

   AND SETID = G.SETID

   AND VEN