異構資料庫遷移 sql等價改寫
原SQL跑不出結果,因為有or只能走nested loop
select a.busin_code_chg, a.prd_code_chg, a.share_class, a.seller_code, a.targ_prd_code_chg, a.targ_share_class from tbfund_c5navtmp3 a, tbfundchangelimit b where a.busin_code_chg ='13' and (a.prd_code_chg = b.prd_code or b.prd_code = '*' ) and (a.share_class = b.share_class or b.share_class = '*') and (a.seller_code = b.seller_code or b.seller_code = '*') and ((case when a.targ_prd_code_chg = '*' then ' ' else a.targ_prd_code_chg end) = b.targ_prd_code or b.targ_prd_code = '*') and (a.targ_share_class = b.targ_share_class or b.targ_share_class = '*') and (case when b.client_type = ' ' then '*' else b.client_type end) = '*';
將SQL改成union all結構,走or expansion 執行計劃
SELECT vm_tmp.i1 BUSIN_CODE_CHG, vm_tmp.i2 PRD_CODE_CHG, vm_tmp.i3 SHARE_CLASS, vm_tmp.i4 SELLER_CODE, vm_tmp.i5 TARG_PRD_CODE_CHG, vm_tmp.i6 TARG_SHARE_CLASS FROM ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND B.SHARE_CLASS = '*' AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = '*' AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*') UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND B.SHARE_CLASS = '*' AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = '*' AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND A.SHARE_CLASS = B.SHARE_CLASS AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = '*' AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND B.SHARE_CLASS = '*' AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = '*' AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.SELLER_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND B.SHARE_CLASS = '*' AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND B.SHARE_CLASS = '*' AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = '*' AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND A.SHARE_CLASS = B.SHARE_CLASS AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = '*' AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.SHARE_CLASS = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND B.SHARE_CLASS = '*' AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = '*' AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND B.SHARE_CLASS = '*' AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND B.SHARE_CLASS = '*' AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = '*' AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND A.SHARE_CLASS = B.SHARE_CLASS AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = '*' AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND A.SHARE_CLASS = B.SHARE_CLASS AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND A.SHARE_CLASS = B.SHARE_CLASS AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = '*' AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND B.SHARE_CLASS = '*' AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SELLER_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND B.SHARE_CLASS = '*' AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = '*' AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.SELLER_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND B.SHARE_CLASS = '*' AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.TARG_PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND A.SHARE_CLASS = B.SHARE_CLASS AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND A.SHARE_CLASS = B.SHARE_CLASS AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = '*' AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND B.SHARE_CLASS = '*' AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = '*' AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND A.SHARE_CLASS = B.SHARE_CLASS AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND A.SHARE_CLASS = B.SHARE_CLASS AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = '*' AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.SHARE_CLASS = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND B.SHARE_CLASS = '*' AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND B.SHARE_CLASS = '*' AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SELLER_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND B.SHARE_CLASS = '*' AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND A.SHARE_CLASS = B.SHARE_CLASS AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = '*' AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND A.SHARE_CLASS = B.SHARE_CLASS AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*') AND lnnvl(B.PRD_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND A.SHARE_CLASS = B.SHARE_CLASS AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = '*' AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.PRD_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*') AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.TARG_SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND B.PRD_CODE = '*' AND A.SHARE_CLASS = B.SHARE_CLASS AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND A.SHARE_CLASS = B.SHARE_CLASS AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND B.TARG_SHARE_CLASS = '*' AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.PRD_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*') AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SELLER_CODE = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND A.SHARE_CLASS = B.SHARE_CLASS AND B.SELLER_CODE = '*' AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.PRD_CODE = '*') AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*') AND lnnvl(B.TARG_SHARE_CLASS = '*')) UNION ALL ((SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND B.SHARE_CLASS = '*' AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.PRD_CODE = '*') AND lnnvl(B.TARG_SHARE_CLASS = '*')) UNION ALL (SELECT A.BUSIN_CODE_CHG i1, A.PRD_CODE_CHG i2, A.SHARE_CLASS i3, A.SELLER_CODE i4, A.TARG_PRD_CODE_CHG i5, A.TARG_SHARE_CLASS i6 FROM TBFUNDCHANGELIMIT B, TBFUND_C5NAVTMP3 A WHERE A.BUSIN_CODE_CHG = '13' AND A.PRD_CODE_CHG = B.PRD_CODE AND A.SHARE_CLASS = B.SHARE_CLASS AND A.SELLER_CODE = B.SELLER_CODE AND B.TARG_PRD_CODE = CASE A.TARG_PRD_CODE_CHG WHEN '*' THEN ' ' ELSE A.TARG_PRD_CODE_CHG END AND A.TARG_SHARE_CLASS = B.TARG_SHARE_CLASS AND CASE B.CLIENT_TYPE WHEN ' ' THEN '*' ELSE B.CLIENT_TYPE END = '*' AND lnnvl(B.TARG_PRD_CODE = '*') AND lnnvl(B.PRD_CODE = '*') AND lnnvl(B.TARG_SHARE_CLASS = '*') AND lnnvl(B.SELLER_CODE = '*') AND lnnvl(B.SHARE_CLASS = '*'))))))))))))))))))))))))))))))))) vm_tmp
SQL很快便執行完,具體執行計劃略,本文只是給出優化改寫思路
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29990276/viewspace-2900567/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用SQL Developer 遷移異構資料庫到OracleSQLDeveloper資料庫Oracle
- 從 SQL Server 到 MySQL (一):異構資料庫遷移ServerMySql資料庫
- MySQL的SQL等價改寫MySql
- SQL Server資料庫遷移SQLServer資料庫
- 摩杜雲資料庫MySQL,破解異構資料庫遷移難題資料庫MySql
- 異構資料庫遷移工具 - Oracle SQL Developer Migrations資料庫OracleSQLDeveloper
- 使用RMAN遷移資料庫到異機資料庫
- 今晚遷移資料庫異常順利資料庫
- 異構資料庫系統遷移到Oracle 工具 - Oracle SQL Developer資料庫OracleSQLDeveloper
- 寫有效的歷史資料遷移sqlSQL
- ZT 寫有效的歷史資料遷移sqlSQL
- 異構資料庫遷移埋下的 9 個大坑怎麼躲開?資料庫
- Oracle到PostgreSQL等價改寫OracleSQL
- 【遷移】使用rman遷移資料庫資料庫
- 資料庫遷移資料庫
- 1.1資料庫物件結構遷移方法資料庫物件
- 華為雲UGO:醒醒!你的異構資料庫遷移難題有救了Go資料庫
- 異構資料庫資料遷移 oracle to mysql之oracle sqlloader和mysql load data資料庫OracleMySql
- SQL 遷移資料庫至ORACLE簡易方法SQL資料庫Oracle
- 資料庫遷移 :理解資料庫
- laravel資料庫遷移Laravel資料庫
- Odoo遷移資料庫Odoo資料庫
- redis資料庫遷移Redis資料庫
- Cacti 遷移資料庫資料庫
- 資料庫遷移方案資料庫
- ORACLE資料庫遷移Oracle資料庫
- 遷移資料庫成功!資料庫
- 建立資料庫遷移資料庫
- 資料庫-oracle-資料庫遷移資料庫Oracle
- SQL Server 資料庫最小當機遷移方案GESQLServer資料庫
- Oracle資料庫遷移 - 異構傳輸表空間TTS HP-UX遷移至Redhat Linux 7.7Oracle資料庫TTSRedhatLinux
- Oracle資料庫資料遷移流程Oracle資料庫
- 資料庫課程作業筆記 - 編寫資料庫遷移檔案資料庫筆記
- 資料遷移(1)——通過資料泵表結構批量遷移
- 資料庫遷移神器——Flyway資料庫
- 遷移MySQL 5.7資料庫MySql資料庫
- 用rman遷移資料庫資料庫
- 資料庫遷移手記資料庫