the order of join condition will change the execution

 

Old SQL:

==========================================

select a.swaptradeid, a.tradedate selltradedate, a.settledate sellsettledate, a.counterpartycd,  a.quantity sellquantity,

                a.price sellprice, b.facilitydesc facilitytype, b.loanxid, b.facilitycusip, c.issuername, d.CURRPERIODEFFECTIVESTARTDATE,

                e.liquidationamount, e.realizedgain, f.positionid, f.tradedate buytradedate, f.settledate buysettledate, f.purchaseprice,

                f.commitmentamount, g.TRSSETTLEDDATE,

                a.tradeccy, d.currency,

                pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency, f.tradedate) init_spotrate,

                pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency, a.settledate) settle_spotrate,

                pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency) latest_spotrate,

                pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency, a.tradedate) sell_spotrate

                  from swaptrade_eod a,

                loanfacility_eod b,

                loandeal_eod c,

                pvtrs_eod d,

                ptrsliquidation_eod e,

                ptrsposition_eod f,

                TradeExtend g

                  where a.ptrsid = 7

                and a.tradeside = 'S'

                and a.loanfacilityid = b.loanfacilityid

                and b.loandealid = c.loandealid

                and a.ptrsid = d.pvtrsid

                and a.swaptradeid = e.SELLTRADEID(+) 

                and e.positionid = f.positionid (+)

                and a.closedate = b.closedate

                and a.closedate = c.closedate

                and a.closedate = d.closedate

                and a.closedate = e.closedate(+)

                and e.closedate = f.closedate(+)

                and a.closedate = pkg_eod_util.getlastclosedate()

                and a.LOANTRADEID = g.LOANTRADEID (+)

                  order by a.tradedate asc, a.tradeside asc, a.lqtticketid asc, e.positionid asc

                 

 

execution plan for old SQL

==============================

---------------------------------------------------------------------------------------------------------------------

| Id  | Operation                           | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                    |                       |  8750 |  2349K|       | 23689   (2)| 00:04:45 |

|   1 |  SORT ORDER BY                      |                       |  8750 |  2349K|  2608K| 23689   (2)| 00:04:45 |

|*  2 |   HASH JOIN OUTER                   |                       |  8750 |  2349K|  2088K| 23168   (2)| 00:04:39 |

|*  3 |    HASH JOIN OUTER                  |                       |  8750 |  1982K|       | 18074   (3)| 00:03:37 |

|*  4 |     HASH JOIN OUTER                 |                       |  3859 |   753K|       |  2280   (2)| 00:00:28 |

|*  5 |      HASH JOIN                      |                       |  3859 |   693K|       |  2141   (2)| 00:00:26 |

|*  6 |       HASH JOIN                     |                       |  3898 |   563K|       |  1599   (1)| 00:00:20 |

|   7 |        NESTED LOOPS                 |                       |       |       |       |            |          |

|   8 |         NESTED LOOPS                |                       |  3898 |   392K|       |   493   (1)| 00:00:06 |

|   9 |          TABLE ACCESS BY INDEX ROWID| PVTRS_EOD             |    24 |   600 |       |    19   (0)| 00:00:01 |

|* 10 |           INDEX SKIP SCAN           | SYS_C00435176         |    24 |       |       |    12   (0)| 00:00:01 |

|* 11 |          INDEX RANGE SCAN           | SWAPTRD_EOD_COMP1_IDX |   162 |       |       |     2   (0)| 00:00:01 |

|  12 |         TABLE ACCESS BY INDEX ROWID | SWAPTRADE_EOD         |   162 | 12636 |       |    25   (0)| 00:00:01 |

|  13 |        TABLE ACCESS FULL            | LOANFACILITY_EOD      |   105K|  4638K|       |  1104   (1)| 00:00:14 |

|  14 |       TABLE ACCESS FULL             | LOANDEAL_EOD          | 59924 |  2106K|       |   540   (2)| 00:00:07 |

|  15 |      TABLE ACCESS FULL              | TRADEEXTEND           | 55289 |   863K|       |   138   (2)| 00:00:02 |

|  16 |     TABLE ACCESS FULL               | PTRSLIQUIDATION_EOD   |  5281K|   161M|       | 15729   (2)| 00:03:09 |

|  17 |    TABLE ACCESS FULL                | PTRSPOSITION_EOD      |   609K|    24M|       |  3390   (2)| 00:00:41 |

---------------------------------------------------------------------------------------------------------------------

 

 

 

New SQL:

================================

select a.swaptradeid, a.tradedate selltradedate, a.settledate sellsettledate, a.counterpartycd

                              , a.quantity sellquantity, a.price sellprice, a.tradeccy

                              , b.facilitydesc facilitytype, b.loanxid, b.facilitycusip

                              , c.issuername

                              , d.CURRPERIODEFFECTIVESTARTDATE, d.currency

                              , e.liquidationamount, e.realizedgain

                              , f.positionid, f.tradedate buytradedate, f.settledate buysettledate, f.purchaseprice, f.commitmentamount

                              , g.TRSSETTLEDDATE

                              , pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency, f.tradedate) init_spotrate

                              , pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency, a.settledate) settle_spotrate

                              , pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency) latest_spotrate

                              , pkg_asset_eod.getlastfxrate(a.tradeccy, d.currency, a.tradedate) sell_spotrate

                  from swaptrade_eod a

                        , loanfacility_eod b

                        , loandeal_eod c

                        , pvtrs_eod d

                        , ptrsliquidation_eod e

                        , ptrsposition_eod f

                        , TradeExtend g

                  where a.ptrsid = 7

                and a.tradeside = 'S'

                and a.closedate = pkg_eod_util.getlastclosedate()

               

                and b.closedate = pkg_eod_util.getlastclosedate()

                and b.loanfacilityid = a.loanfacilityid

               

                and c.closedate = pkg_eod_util.getlastclosedate()

                and c.loandealid = b.loandealid

               

                and d.closedate = pkg_eod_util.getlastclosedate()

                and d.pvtrsid = a.ptrsid

               

                and e.closedate(+) = pkg_eod_util.getlastclosedate()

                and e.SELLTRADEID(+) = a.swaptradeid

               

                and f.closedate(+) = pkg_eod_util.getlastclosedate()

                and f.positionid (+) = e.positionid

                               

                and g.LOANTRADEID(+) = a.LOANTRADEID

                  order by a.tradedate asc, a.tradeside asc, a.lqtticketid asc

                        , e.positionid asc

 

Execution Plan for new SQL:

========================================

-------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                          | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                   |                            | 26922 |  7230K|       |  8872   (1)| 00:01:47 |

|   1 |  SORT ORDER BY                     |                            | 26922 |  7230K|  7704K|  8872   (1)| 00:01:47 |

|*  2 |   HASH JOIN                        |                            | 26922 |  7230K|       |  7274   (1)| 00:01:28 |

|   3 |    TABLE ACCESS BY INDEX ROWID     | LOANDEAL_EOD               |  2497 | 89892 |       |   322   (1)| 00:00:04 |

|*  4 |     INDEX RANGE SCAN               | SYS_C00435101              |  2497 |       |       |     9   (0)| 00:00:01 |

|*  5 |    HASH JOIN                       |                            | 22622 |  5279K|       |  6952   (1)| 00:01:24 |

|   6 |     TABLE ACCESS BY INDEX ROWID    | LOANFACILITY_EOD           |  4398 |   193K|       |   301   (1)| 00:00:04 |

|*  7 |      INDEX RANGE SCAN              | SYS_C00435106              |  4398 |       |       |    15   (0)| 00:00:01 |

|*  8 |     HASH JOIN RIGHT OUTER          |                            | 14525 |  2751K|  1368K|  6650   (1)| 00:01:20 |

|   9 |      TABLE ACCESS BY INDEX ROWID   | PTRSPOSITION_EOD           | 25394 |  1066K|       |  2276   (1)| 00:00:28 |

|* 10 |       INDEX RANGE SCAN             | SYS_C00435126              | 25394 |       |       |    86   (2)| 00:00:02 |

|* 11 |      HASH JOIN OUTER               |                            | 12422 |  1831K|       |  4210   (1)| 00:00:51 |

|* 12 |       HASH JOIN OUTER              |                            |  3897 |   452K|       |   697   (1)| 00:00:09 |

|  13 |        NESTED LOOPS                |                            |  3897 |   391K|       |   558   (1)| 00:00:07 |

|  14 |         TABLE ACCESS BY INDEX ROWID| PVTRS_EOD                  |     1 |    25 |       |     2   (0)| 00:00:01 |

|* 15 |          INDEX UNIQUE SCAN         | SYS_C00435176              |     1 |       |       |     1   (0)| 00:00:01 |

|  16 |         TABLE ACCESS BY INDEX ROWID| SWAPTRADE_EOD              |  3897 |   296K|       |   556   (1)| 00:00:07 |

|* 17 |          INDEX RANGE SCAN          | SWAPTRD_EOD_COMP1_IDX      |  3897 |       |       |    15   (0)| 00:00:01 |

|  18 |        TABLE ACCESS FULL           | TRADEEXTEND                | 55289 |   863K|       |   138   (2)| 00:00:02 |

|  19 |       TABLE ACCESS BY INDEX ROWID  | PTRSLIQUIDATION_EOD        |   220K|  6877K|       |  3509   (1)| 00:00:43 |

|* 20 |        INDEX RANGE SCAN            | PTRSLIQUIDATION_EOD_CD_IDX |   220K|       |       |   605   (1)| 00:00:08 |

-------------------------------------------------------------------------------------------------------------------------