SQL> SELECT CLIENT_CODE, SUM(AMT) 2 FROM 3 ( 4 SELECT C.GL_SL_CODE CLIENT_CODE, C.AMOUNT AMT 5 FROM GL_VOUCHERS_DETAILS C, GL_VOUCHERS A, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 6 WHERE A.GL_VOUCHER_NO = C.GL_VOUCHER_NO AND C.GL_SL_TYPE = 'ALL'--:B2 7 AND C.GL_SL_CODE = ETC.CLIENT_CODE AND A.GL_FORM_DATE <= '01-Jan-2007'--:B1 8 AND NVL(A.GL_FORM_TYPE_CODE, 'X') <> 'ETB' AND ROWNUM > 0 9 UNION ALL 10 SELECT ET.CLIENT_CODE, DECODE(ET.BUY_OR_SELL, 'B', (ET.VOLUME * ET.RATE) + (ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0)), ((ET.VOLUME * ET.RATE) - (ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0))) * -1) AMT 11 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, EQUITY_TRADE_INFO ETI, SECURITY, EQUITY_SYSTEM ES, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 12 WHERE ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) AND ET.ISIN = SECURITY.ISIN AND ET.CLEARING_NO = CC.CLEARING_NO AND ET.CLIENT_CODE = ETC.CLIENT_CODE AND ET.TRADE_DATE <= '6-SEP-2008' --:B1 13 AND DECODE('F' || NVL(ET.BILL_NUMBER, -1), 'F-1', CC.CLEARING_TYPE, 0) <> DECODE('F' || NVL(ET.BILL_NUMBER, -1), 'F-1', ES.FORWARD_CLR_TYPE, 1) AND DECODE(SUBSTR(UPPER(SECURITY.SYMBOL), LENGTH(UPPER(SECURITY.SYMBOL)) - 2), 'PRO', DECODE(ET.BILL_NUMBER, NULL, 0, 1), 1) = 1) GROUP BY CLIENT_CODE 14 / no rows selected Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 221 | 10274 | | 1 | SORT GROUP BY | | 13 | 221 | 10274 | | 2 | VIEW | | 13 | 221 | 10271 | | 3 | UNION-ALL | | | | | | 4 | COUNT | | | | | | 5 | FILTER | | | | | | 6 | NESTED LOOPS | | 8 | 296 | 177 | | 7 | NESTED LOOPS | | 8 | 160 | 169 | | 8 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 9 | TABLE ACCESS BY INDEX ROWID| GL_VOUCHERS_DETAILS | 8 | 128 | 168 | | 10 | INDEX RANGE SCAN | REF_150932_VOUCHERS_DETAILS | 3 | | 2 | | 11 | TABLE ACCESS BY INDEX ROWID | GL_VOUCHERS | 1 | 17 | 1 | | 12 | INDEX UNIQUE SCAN | PK_GL_VOUCHERS | 1 | | | | 13 | NESTED LOOPS OUTER | | 5 | 460 | 10094 | | 14 | NESTED LOOPS | | 5 | 385 | 10084 | | 15 | NESTED LOOPS | | 95 | 6650 | 9989 | | 16 | NESTED LOOPS | | 9540 | 475K| 449 | | 17 | MERGE JOIN CARTESIAN | | 1 | 6 | 2 | | 18 | INDEX FULL SCAN | FK_EQ_SYS_FORWARD_CLR_TYPE_FK | 1 | 2 | 1 | | 19 | BUFFER SORT | | 1 | 4 | 1 | | 20 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 21 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 9540 | 419K| 447 | | 22 | INDEX RANGE SCAN | REF_22636_FK | 9540 | | 438 | | 23 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 24 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | | 25 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 7 | 1 | | 26 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 27 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 28 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | ---------------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1418 recursive calls 20 db block gets 541 consistent gets 10 physical reads 0 redo size 300 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 32 sorts (memory) 0 sorts (disk) 0 rows processed SQL> alter system flush shared_pool; System altered. SQL> / System altered. SQL> INSERT INTO TEMP_TBL (CLIENT_CODE, CL_AMT) 2 SELECT CLIENT_CODE, SUM(AMT) 3 FROM 4 ( 5 SELECT C.GL_SL_CODE CLIENT_CODE, C.AMOUNT AMT 6 FROM GL_VOUCHERS_DETAILS C, GL_VOUCHERS A, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 7 WHERE A.GL_VOUCHER_NO = C.GL_VOUCHER_NO AND C.GL_SL_TYPE = 'ALL'--:B2 8 AND C.GL_SL_CODE = ETC.CLIENT_CODE AND A.GL_FORM_DATE <= '01-Jan-2007'--:B1 9 AND NVL(A.GL_FORM_TYPE_CODE, 'X') <> 'ETB' AND ROWNUM > 0 10 UNION ALL 11 SELECT ET.CLIENT_CODE, DECODE(ET.BUY_OR_SELL, 'B', (ET.VOLUME * ET.RATE) + (ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0)), ((ET.VOLUME * ET.RATE) - (ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0))) * -1) AMT 12 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, EQUITY_TRADE_INFO ETI, SECURITY, EQUITY_SYSTEM ES, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 13 WHERE ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) AND ET.ISIN = SECURITY.ISIN AND ET.CLEARING_NO = CC.CLEARING_NO AND ET.CLIENT_CODE = ETC.CLIENT_CODE AND ET.TRADE_DATE <= '6-SEP-2008' --:B1 14 AND DECODE('F' || NVL(ET.BILL_NUMBER, -1), 'F-1', CC.CLEARING_TYPE, 0) <> DECODE('F' || NVL(ET.BILL_NUMBER, -1), 'F-1', ES.FORWARD_CLR_TYPE, 1) AND DECODE(SUBSTR(UPPER(SECURITY.SYMBOL), LENGTH(UPPER(SECURITY.SYMBOL)) - 2), 'PRO', DECODE(ET.BILL_NUMBER, NULL, 0, 1), 1) = 1) GROUP BY CLIENT_CODE 15 / 0 rows created. Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 13 | 221 | 10274 | | 1 | SORT GROUP BY | | 13 | 221 | 10274 | | 2 | VIEW | | 13 | 221 | 10271 | | 3 | UNION-ALL | | | | | | 4 | COUNT | | | | | | 5 | FILTER | | | | | | 6 | NESTED LOOPS | | 8 | 296 | 177 | | 7 | NESTED LOOPS | | 8 | 160 | 169 | | 8 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 9 | TABLE ACCESS BY INDEX ROWID| GL_VOUCHERS_DETAILS | 8 | 128 | 168 | | 10 | INDEX RANGE SCAN | REF_150932_VOUCHERS_DETAILS | 3 | | 2 | | 11 | TABLE ACCESS BY INDEX ROWID | GL_VOUCHERS | 1 | 17 | 1 | | 12 | INDEX UNIQUE SCAN | PK_GL_VOUCHERS | 1 | | | | 13 | NESTED LOOPS OUTER | | 5 | 460 | 10094 | | 14 | NESTED LOOPS | | 5 | 385 | 10084 | | 15 | NESTED LOOPS | | 95 | 6650 | 9989 | | 16 | NESTED LOOPS | | 9540 | 475K| 449 | | 17 | MERGE JOIN CARTESIAN | | 1 | 6 | 2 | | 18 | INDEX FULL SCAN | FK_EQ_SYS_FORWARD_CLR_TYPE_FK | 1 | 2 | 1 | | 19 | BUFFER SORT | | 1 | 4 | 1 | | 20 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 21 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 9540 | 419K| 447 | | 22 | INDEX RANGE SCAN | REF_22636_FK | 9540 | | 438 | | 23 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 24 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | | 25 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 7 | 1 | | 26 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 27 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 28 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | ---------------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 8254 recursive calls 0 db block gets 3145 consistent gets 0 physical reads 0 redo size 559 bytes sent via SQL*Net to client 1838 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 83 sorts (memory) 0 sorts (disk) 0 rows processed SQL> SELECT CLIENT_CODE, SUM(AMOUNT) 2 FROM 3 ( SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, CC.FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) 4 +NVL(ETI.WHT_COT, 0))) )) AMOUNT 5 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, SECURITY SC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 6 WHERE ET.TRADE_DATE <=:B1 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.CLEARING_NO=CC.CLEARING_NO AND ET.ISIN=SC.ISIN AND (NVL(CC.FUTURE_PERIOD_DESC, 'N') <> 'N' OR SC.SYMBOL LIKE '%PRO') AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 7 GROUP BY ET.CLIENT_CODE, ET.ISIN, CC.FUTURE_PERIOD_DESC, SC.SYMBOL 8 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 9 UNION ALL 10 SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, CC.FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0)+NVL(ETI.WHT_COT, 0))) )) AMOUNT 11 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 12 WHERE ET.TRADE_DATE > :B1 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.CLEARING_NO=CC.CLEARING_NO AND CC.FUTURE_PERIOD_DESC IS NOT NULL AND ET.TICKET_NUMBER LIKE 'FTR%' AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 13 GROUP BY ET.CLIENT_CODE, ET.ISIN, CC.FUTURE_PERIOD_DESC 14 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 15 UNION ALL 16 SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, NULL FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0)+NVL(ETI.WHT_COT, 0))) )) AMOUNT 17 FROM EQUITY_TRADE ET, SECURITY SC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 18 WHERE ET.TRADE_DATE > :B1 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.TICKET_NUMBER LIKE 'FTR%' AND ET.ISIN = SC.ISIN AND SC.SYMBOL LIKE '%PRO' AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 19 GROUP BY ET.CLIENT_CODE, ET.ISIN 20 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 21 ) 22 GROUP BY CLIENT_CODE 23 / SP2-0552: Bind variable "B1" not declared. SQL> SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, CC.FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) 2 +NVL(ETI.WHT_COT, 0))) )) AMOUNT 3 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, SECURITY SC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 4 WHERE ET.TRADE_DATE <= '06-SEP-2008' --:B1 5 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.CLEARING_NO=CC.CLEARING_NO AND ET.ISIN=SC.ISIN AND (NVL(CC.FUTURE_PERIOD_DESC, 'N') <> 'N' OR SC.SYMBOL LIKE '%PRO') AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 6 GROUP BY ET.CLIENT_CODE, ET.ISIN, CC.FUTURE_PERIOD_DESC, SC.SYMBOL 7 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 8 / no rows selected Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 91 | 125 | | 1 | FILTER | | | | | | 2 | SORT GROUP BY | | 1 | 91 | 125 | | 3 | NESTED LOOPS OUTER | | 1 | 91 | 122 | | 4 | NESTED LOOPS | | 1 | 76 | 120 | | 5 | NESTED LOOPS | | 1 | 70 | 119 | | 6 | MERGE JOIN CARTESIAN | | 1 | 23 | 115 | | 7 | TABLE ACCESS FULL | SECURITY | 1160 | 22040 | 3 | | 8 | BUFFER SORT | | 1 | 4 | 112 | | 9 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 10 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 47 | 4 | | 11 | INDEX RANGE SCAN | IDX$$_00560002 | 293 | | 2 | | 12 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 13 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 14 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 15 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | ------------------------------------------------------------------------------------------------ Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 178 recursive calls 0 db block gets 74 consistent gets 0 physical reads 0 redo size 466 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 0 rows processed SQL> alter system flush shared_pool 2 / System altered. SQL> SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, CC.FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) 2 +NVL(ETI.WHT_COT, 0))) )) AMOUNT 3 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, SECURITY SC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 4 WHERE ET.TRADE_DATE <= '06-SEP-2008' --:B1 5 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.CLEARING_NO=CC.CLEARING_NO AND ET.ISIN=SC.ISIN AND (NVL(CC.FUTURE_PERIOD_DESC, 'N') <> 'N' OR SC.SYMBOL LIKE '%PRO') AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 6 GROUP BY ET.CLIENT_CODE, ET.ISIN, CC.FUTURE_PERIOD_DESC, SC.SYMBOL 7 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 8 / no rows selected Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 91 | 125 | | 1 | FILTER | | | | | | 2 | SORT GROUP BY | | 1 | 91 | 125 | | 3 | NESTED LOOPS OUTER | | 1 | 91 | 122 | | 4 | NESTED LOOPS | | 1 | 76 | 120 | | 5 | NESTED LOOPS | | 1 | 70 | 119 | | 6 | MERGE JOIN CARTESIAN | | 1 | 23 | 115 | | 7 | TABLE ACCESS FULL | SECURITY | 1160 | 22040 | 3 | | 8 | BUFFER SORT | | 1 | 4 | 112 | | 9 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 10 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 47 | 4 | | 11 | INDEX RANGE SCAN | IDX$$_00560002 | 293 | | 2 | | 12 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 13 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 14 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 15 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | ------------------------------------------------------------------------------------------------ Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 4970 recursive calls 0 db block gets 1990 consistent gets 0 physical reads 0 redo size 466 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 47 sorts (memory) 0 sorts (disk) 0 rows processed SQL> alter system flush shared_pool; System altered. SQL> SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, CC.FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) 2 +NVL(ETI.WHT_COT, 0))) )) AMOUNT 3 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, SECURITY SC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 4 WHERE ET.TRADE_DATE <= '06-SEP-2008' --:B1 5 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.CLEARING_NO=CC.CLEARING_NO AND ET.ISIN=SC.ISIN AND (NVL(CC.FUTURE_PERIOD_DESC, 'N') <> 'N' OR SC.SYMBOL LIKE '%PRO') AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 6 GROUP BY ET.CLIENT_CODE, ET.ISIN, CC.FUTURE_PERIOD_DESC, SC.SYMBOL 7 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 8 UNION ALL 9 SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, CC.FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0)+NVL(ETI.WHT_COT, 0))) )) AMOUNT 10 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 11 WHERE ET.TRADE_DATE > '06-SEP-2008' -- :B1 12 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.CLEARING_NO=CC.CLEARING_NO AND CC.FUTURE_PERIOD_DESC IS NOT NULL AND ET.TICKET_NUMBER LIKE 'FTR%' AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 13 GROUP BY ET.CLIENT_CODE, ET.ISIN, CC.FUTURE_PERIOD_DESC 14 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 15 UNION ALL 16 SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, NULL FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0)+NVL(ETI.WHT_COT, 0))) )) AMOUNT 17 FROM EQUITY_TRADE ET, SECURITY SC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 18 WHERE ET.TRADE_DATE > '06-SEP-2008' -- :B1 19 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.TICKET_NUMBER LIKE 'FTR%' AND ET.ISIN = SC.ISIN AND SC.SYMBOL LIKE '%PRO' AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 20 GROUP BY ET.CLIENT_CODE, ET.ISIN 21 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 22 / no rows selected Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 261 | 147 | | 1 | UNION-ALL | | | | | | 2 | FILTER | | | | | | 3 | SORT GROUP BY | | 1 | 91 | 125 | | 4 | NESTED LOOPS OUTER | | 1 | 91 | 122 | | 5 | NESTED LOOPS | | 1 | 76 | 120 | | 6 | NESTED LOOPS | | 1 | 70 | 119 | | 7 | MERGE JOIN CARTESIAN | | 1 | 23 | 115 | | 8 | TABLE ACCESS FULL | SECURITY | 1160 | 22040 | 3 | | 9 | BUFFER SORT | | 1 | 4 | 112 | | 10 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 11 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 47 | 4 | | 12 | INDEX RANGE SCAN | IDX$$_00560002 | 293 | | 2 | | 13 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 14 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 15 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 16 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | | 17 | FILTER | | | | | | 18 | SORT GROUP BY | | 1 | 80 | 11 | | 19 | NESTED LOOPS OUTER | | 1 | 80 | 8 | | 20 | NESTED LOOPS | | 1 | 65 | 6 | | 21 | NESTED LOOPS | | 1 | 59 | 5 | | 22 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 23 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 55 | 4 | | 24 | INDEX RANGE SCAN | IDX$$_00560001 | 1 | | 3 | | 25 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 26 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 27 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 28 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | | 29 | FILTER | | | | | | 30 | SORT GROUP BY | | 1 | 90 | 11 | | 31 | NESTED LOOPS OUTER | | 1 | 90 | 8 | | 32 | NESTED LOOPS | | 1 | 75 | 6 | | 33 | NESTED LOOPS | | 1 | 56 | 5 | | 34 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 35 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 52 | 4 | | 36 | INDEX RANGE SCAN | IDX$$_00560001 | 1 | | 3 | | 37 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 38 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | | 39 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 40 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | ------------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 5136 recursive calls 0 db block gets 2034 consistent gets 0 physical reads 0 redo size 466 bytes sent via SQL*Net to client 1176 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 49 sorts (memory) 0 sorts (disk) 0 rows processed SQL> alter system flush shared_pool; System altered. SQL> SELECT CLIENT_CODE, SUM(AMOUNT) 2 FROM 3 ( SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, CC.FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) 4 +NVL(ETI.WHT_COT, 0))) )) AMOUNT 5 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, SECURITY SC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 6 WHERE ET.TRADE_DATE <= '06-SEP-2008' --:B1 7 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.CLEARING_NO=CC.CLEARING_NO AND ET.ISIN=SC.ISIN AND (NVL(CC.FUTURE_PERIOD_DESC, 'N') <> 'N' OR SC.SYMBOL LIKE '%PRO') AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 8 GROUP BY ET.CLIENT_CODE, ET.ISIN, CC.FUTURE_PERIOD_DESC, SC.SYMBOL 9 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 10 UNION ALL 11 SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, CC.FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0)+NVL(ETI.WHT_COT, 0))) )) AMOUNT 12 FROM EQUITY_TRADE ET, CLEARING_CALENDAR CC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 13 WHERE ET.TRADE_DATE > '06-SEP-2008' -- :B1 14 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.CLEARING_NO=CC.CLEARING_NO AND CC.FUTURE_PERIOD_DESC IS NOT NULL AND ET.TICKET_NUMBER LIKE 'FTR%' AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 15 GROUP BY ET.CLIENT_CODE, ET.ISIN, CC.FUTURE_PERIOD_DESC 16 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 17 UNION ALL 18 SELECT ET.CLIENT_CODE CLIENT_CODE, ET.ISIN ISIN, NULL FUTURE_PERIOD_DESC, SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) QUANTITY, SUM(DECODE(ET.BUY_OR_SELL, 'B', ((ET.VOLUME*ET.RATE)+(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0) + NVL(ETI.WHT_COT, 0) )), -((ET.VOLUME*ET.RATE)-(ET.BRK_AMOUNT + NVL(ETI.CVT, 0) + NVL(ETI.WHT, 0)+NVL(ETI.WHT_COT, 0))) )) AMOUNT 19 FROM EQUITY_TRADE ET, SECURITY SC, EQUITY_TRADE_INFO ETI, EQUITY_TEMP_CAPITAL_G_L_SUM ETC 20 WHERE ET.TRADE_DATE > '06-SEP-2008' -- :B1 21 AND NVL(ET.BILL_NUMBER, 0) = 0 AND ET.CLIENT_CODE= ETC.CLIENT_CODE AND ET.TICKET_NUMBER LIKE 'FTR%' AND ET.ISIN = SC.ISIN AND SC.SYMBOL LIKE '%PRO' AND ET.POST=1 AND ET.TRADE_NUMBER = ETI.TRADE_NUMBER(+) 22 GROUP BY ET.CLIENT_CODE, ET.ISIN 23 HAVING SUM(DECODE(ET.BUY_OR_SELL, 'B', ET.VOLUME, -ET.VOLUME)) = 0 24 ) 25 GROUP BY CLIENT_CODE 26 / no rows selected Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 51 | 148 | | 1 | SORT GROUP BY | | 3 | 51 | 148 | | 2 | VIEW | | 3 | 51 | 147 | | 3 | UNION-ALL | | | | | | 4 | FILTER | | | | | | 5 | SORT GROUP BY | | 1 | 91 | 125 | | 6 | NESTED LOOPS OUTER | | 1 | 91 | 122 | | 7 | NESTED LOOPS | | 1 | 76 | 120 | | 8 | NESTED LOOPS | | 1 | 70 | 119 | | 9 | MERGE JOIN CARTESIAN | | 1 | 23 | 115 | | 10 | TABLE ACCESS FULL | SECURITY | 1160 | 22040 | 3 | | 11 | BUFFER SORT | | 1 | 4 | 112 | | 12 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 13 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 47 | 4 | | 14 | INDEX RANGE SCAN | IDX$$_00560002 | 293 | | 2 | | 15 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 16 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 17 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 18 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | | 19 | FILTER | | | | | | 20 | SORT GROUP BY | | 1 | 80 | 11 | | 21 | NESTED LOOPS OUTER | | 1 | 80 | 8 | | 22 | NESTED LOOPS | | 1 | 65 | 6 | | 23 | NESTED LOOPS | | 1 | 59 | 5 | | 24 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 25 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 55 | 4 | | 26 | INDEX RANGE SCAN | IDX$$_00560001 | 1 | | 3 | | 27 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 28 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 29 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 30 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | | 31 | FILTER | | | | | | 32 | SORT GROUP BY | | 1 | 90 | 11 | | 33 | NESTED LOOPS OUTER | | 1 | 90 | 8 | | 34 | NESTED LOOPS | | 1 | 75 | 6 | | 35 | NESTED LOOPS | | 1 | 56 | 5 | | 36 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 37 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 52 | 4 | | 38 | INDEX RANGE SCAN | IDX$$_00560001 | 1 | | 3 | | 39 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 40 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | | 41 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE_INFO | 1 | 15 | 2 | | 42 | INDEX UNIQUE SCAN | PK_EQUITY_TRADE_INFO | 1 | | 1 | --------------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 5116 recursive calls 0 db block gets 2032 consistent gets 0 physical reads 0 redo size 303 bytes sent via SQL*Net to client 1241 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 50 sorts (memory) 0 sorts (disk) 0 rows processed SQL> ed Wrote file afiedt.buf 1 SELECT MAX(PRICE_DATE) 2 FROM TEST_EQUITY_MARGIN TEM, SECURITY S, FORWARD_MARKET FEM WHERE FEM.SYMBOL = S.SYMBOL||TEM.FUTURE_PERIOD_DESC AND TEM.FUTURE_PERIOD_DESC IS NOT NULL 3 --AND TEM.CLIENT_CODE = TEM2.CLIENT_CODE 4* AND TEM.ISIN = S.ISIN AND FEM.PRICE_DATE <='06-SEP-2008' SQL> / MAX(PRICE --------- Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 18 | | 1 | SORT AGGREGATE | | 1 | 49 | | | 2 | HASH JOIN | | 84 | 4116 | 18 | | 3 | NESTED LOOPS | | 1 | 32 | 2 | | 4 | TABLE ACCESS FULL | TEST_EQUITY_MARGIN | 1 | 13 | 1 | | 5 | TABLE ACCESS BY INDEX ROWID| SECURITY | 1 | 19 | 1 | | 6 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | | 7 | INDEX FAST FULL SCAN | PK_FORWARD_MARKET | 34283 | 569K| 15 | ------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 457 recursive calls 0 db block gets 317 consistent gets 4 physical reads 0 redo size 340 bytes sent via SQL*Net to client 376 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 17 sorts (memory) 0 sorts (disk) 1 rows processed SQL> alter system flush shared_pool; System altered. SQL> UPDATE TEST_EQUITY_MARGIN TEM2 2 SET (TEM2.MARKET_DATE,TEM2.MARKET_RATE) = 3 ( 4 SELECT FEM.PRICE_DATE,FEM.CLOSE_RATE FROM TEST_EQUITY_MARGIN TEM, SECURITY S, FORWARD_MARKET FEM WHERE FEM.SYMBOL = S.SYMBOL||TEM.FUTURE_PERIOD_DESC AND TEM.FUTURE_PERIOD_DESC IS NOT NULL AND TEM.ISIN = S.ISIN AND TEM2.ISIN = TEM.ISIN AND TEM2.CLIENT_CODE=TEM.CLIENT_CODE AND S.SYMBOL||TEM2.FUTURE_PERIOD_DESC = FEM.SYMBOL AND FEM.PRICE_DATE = 5 ( 6 SELECT MAX(PRICE_DATE) 7 FROM TEST_EQUITY_MARGIN TEM, SECURITY S, FORWARD_MARKET FEM WHERE FEM.SYMBOL = S.SYMBOL||TEM.FUTURE_PERIOD_DESC AND TEM.FUTURE_PERIOD_DESC IS NOT NULL 8 AND TEM.CLIENT_CODE = TEM2.CLIENT_CODE 9 AND TEM.ISIN = S.ISIN AND FEM.PRICE_DATE <='06-SEP-2008' ) 10 ) 11 WHERE TEM2.FUTURE_PERIOD_DESC IS NOT NULL 12 / 0 rows updated. Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------ | 0 | UPDATE STATEMENT | | 1 | 39 | 1 | | 1 | UPDATE | TEST_EQUITY_MARGIN | | | | | 2 | TABLE ACCESS FULL | TEST_EQUITY_MARGIN | 1 | 39 | 1 | | 3 | NESTED LOOPS | | 1 | 58 | 4 | | 4 | NESTED LOOPS | | 1 | 41 | 3 | | 5 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 2 | | 6 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | 1 | | 7 | TABLE ACCESS BY INDEX ROWID | FORWARD_MARKET | 45 | 990 | 1 | | 8 | INDEX UNIQUE SCAN | PK_FORWARD_MARKET | 1 | | | | 9 | SORT AGGREGATE | | 1 | 53 | | | 10 | HASH JOIN | | 84 | 4452 | 18 | | 11 | NESTED LOOPS | | 1 | 36 | 2 | | 12 | TABLE ACCESS FULL | TEST_EQUITY_MARGIN | 1 | 17 | 1 | | 13 | TABLE ACCESS BY INDEX ROWID| SECURITY | 1 | 19 | 1 | | 14 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | | 15 | INDEX FAST FULL SCAN | PK_FORWARD_MARKET | 34283 | 569K| 15 | | 16 | TABLE ACCESS FULL | TEST_EQUITY_MARGIN | 1 | 17 | 1 | ------------------------------------------------------------------------------------------ Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 2159 recursive calls 0 db block gets 944 consistent gets 0 physical reads 0 redo size 559 bytes sent via SQL*Net to client 1276 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 42 sorts (memory) 0 sorts (disk) 0 rows processed SQL> ed Wrote file afiedt.buf 1 SELECT DISTINCT C.CLIENT_CODE 2 FROM CLIENT C ,CLIENT_INFO CI,EQUITY_TRADE ET 3 WHERE C.CLIENT_CODE = CI.CLIENT_CODE(+) AND C.POST = 1 AND C.CLIENT_CODE=ET.CLIENT_CODE AND ET.TRADE_DATE 4 BETWEEN '01-JAN-2007' -- :B4 5 AND '30-SEP-2008' 6 AND C.BRANCH_CODE = DECODE('ALL' -- :B1 7 ,'%',DECODE('ALL' , 8 ( 9 SELECT BRANCH_CODE 10 FROM LOCATIONS 11 WHERE LOCATION_CODE = 12 (SELECT LOCATION_CODE FROM SYSTEM)),C.BRANCH_CODE,'ALL' -- :B2 13 ), 'ALL' --:B1 14* ) SQL> / no rows selected Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 27 | | 56 | | 1 | SORT UNIQUE | | 1 | 27 | 224K| 56 | | 2 | NESTED LOOPS | | 5765 | 152K| | 24 | | 3 | NESTED LOOPS OUTER | | 1 | 16 | | 2 | | 4 | TABLE ACCESS BY INDEX ROWID| CLIENT | 1 | 11 | | 2 | | 5 | INDEX RANGE SCAN | REF_125536_FK | 1 | | | 1 | | 6 | INDEX UNIQUE SCAN | PK_CLIENT_INFO | 1 | 5 | | | | 7 | INDEX RANGE SCAN | IDX_EQUITY_TR_CLCD_DT | 5765 | 63415 | | 22 | ------------------------------------------------------------------------------------------------ Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 595 recursive calls 0 db block gets 157 consistent gets 6 physical reads 0 redo size 246 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 29 sorts (memory) 0 sorts (disk) 0 rows processed SQL> ed Wrote file afiedt.buf 1 UPDATE TEmp_EQUITY_MARGIN SET FUTURE_CASH_BALANCE = GET_SL_BALANCE_EQUITY_FUTURE( 2 'ALL' -- :B3 3 ,CLIENT_CODE, 'ALL' -- :B2 4 ,'ALL','C', 5 'ALL' -- :B1 6* ) SQL> / 0 rows updated. Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | 17 | 1 | | 1 | UPDATE | TEMP_EQUITY_MARGIN | | | | | 2 | TABLE ACCESS FULL| TEMP_EQUITY_MARGIN | 1 | 17 | 1 | ------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 393 recursive calls 0 db block gets 172 consistent gets 2 physical reads 0 redo size 559 bytes sent via SQL*Net to client 680 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 0 rows processed SQL> spool off SQL> select * from test_equity_margin; no rows selected Execution Plan ---------------------------------------------------------- ------------------------------------------------ | Id | Operation | Name | ------------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | TABLE ACCESS FULL| TEST_EQUITY_MARGIN | ------------------------------------------------ Note ----- - 'PLAN_TABLE' is old version - rule based optimizer used (consider using cbo) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1077 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL> select pname, pval1 2 from sys.aux_stats$ 3 where sname = 'SYSSTATS_MAIN'; PNAME PVAL1 ------------------------------ ---------- CPUSPEED CPUSPEEDNW 587.115789 IOSEEKTIM 10 IOTFRSPEED 4096 MAXTHR MBRC MREADTIM SLAVETHR SREADTIM 9 rows selected. Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 47 | 1 | | 1 | TABLE ACCESS BY INDEX ROWID| AUX_STATS$ | 1 | 47 | 1 | | 2 | INDEX RANGE SCAN | I_AUX_STATS$ | 1 | | | ---------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 268 recursive calls 26 db block gets 121 consistent gets 2 physical reads 0 redo size 669 bytes sent via SQL*Net to client 376 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 9 rows processed SQL> execute dbms_stats.gather_table_stats('TESTKHI', 'TEST_EQUITY_MARGIN', cascade=>true); PL/SQL procedure successfully completed. SQL> select * from test_equity_trade; select * from test_equity_trade * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from test_equity_margin; no rows selected Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 164 | 1 | | 1 | TABLE ACCESS FULL| TEST_EQUITY_MARGIN | 1 | 164 | 1 | ------------------------------------------------------------------------ Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1077 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL> desc test_equity_margin Name Null? Type ----------------------------------------- -------- ---------------------------- CLIENT_CODE VARCHAR2(5) ISIN VARCHAR2(12) CASH_BALANCE NUMBER CUST_BALANCE NUMBER MARKET_DATE NUMBER MARKET_RATE NUMBER OUTSTND_AMOUNT NUMBER CASH_MARGIN NUMBER CUST_MARGIN NUMBER CASH_BUYING_POWER NUMBER CUST_BUYING_POWER NUMBER SHORT_SALE_VALUE NUMBER FUTURE_PERIOD_DESC VARCHAR2(7) EQUITY_CURRENT_POSITION NUMBER PROV_TRADE VARCHAR2(5) SQL> alter table test_equity_margin modify market_date date; Table altered. SQL> insert into test_equity_margin 2 ( 3 CLIENT_CODE,ISIN ,CASH_BALANCE,CUST_BALANCE ,MARKET_DATE ,MARKET_RATE ,OUTSTND_AMOUNT ,CASH_MARGIN,CUST_MARGIN 4 ,CASH_BUYING_POWER ,CUST_BUYING_POWER ,SHORT_SALE_VALUE ,FUTURE_PERIOD_DESC ,EQUITY_CURRENT_POSITION ,PROV_TRADE 5 ) 6 select dq.client_code,dq.isin ,0 ,SUM(dq.quantity) custody_balance ,NULL ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,fpd ,0 ,prov 7 FROM 8 ( 9 SELECT * FROM 10 ( 11 SELECT cm.client_code ,cm.isin ,NULL fpd ,NULL prov ,sum(decode(cm.in_or_out ,'I' 12 ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0))))quantity 13 FROM Custody_master cm ,Equity_temp_capital_g_l_sum etc 14 WHERE cm.client_code = etc.client_code 15 and Exists 16 ( 17 select 1 18 from custody_master cm2 19 where cm.transaction_id = cm2.transaction_id 20 and cm2.transaction_Date<='06-SEP-08' 21 ) 22 AND cm.post = 1 23 Group by cm.client_code,cm.isin 24 ) 25 where quantity <> 0 26 UNION ALL 27 select scm.client_code,scm.isin ,scm.fpd ,NULL prov,sum(scm.quantity) 28 from 29 ( 30 SELECT cm.clearing_no ,cm.client_code ,cm.isin,NULL fpd 31 ,sum(decode(cm.in_or_out,'I' ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)))) quantity 32 FROM custody_master cm , Equity_temp_capital_g_l_sum etc 33 WHERE cm.client_Code = etc.client_code and cm.transaction_Date > '06-SEP-08' 34 and cm.clearing_no is not null AND cm.post = 1 35 group by cm.clearing_no,cm.client_code,cm.isin ,NULL 36 having (sum(decode(cm.in_or_out ,'I' ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0))))<>0) 37 ) scm ,clearing_calendar cc 38 where scm.clearing_no=cc.clearing_no and cc.clearing_end_date <='06-SEP-08' 39 group by scm.client_code ,scm.isin ,scm.fpd 40 UNION ALL 41 SELECT cob.client_code ,cob.isin ,NULL fpd ,NULL prov ,decode(ca.in_or_out ,'I' ,(nvl(cob.un_reg_quantity ,0) + nvl(cob.reg_quantity ,0)) , - (nvl(cob.un_reg_quantity ,0) + nvl(cob.reg_quantity ,0))) quantity 42 FROM custody_opening_balances cob, custody_activity ca, Equity_temp_capital_g_l_sum etc 43 WHERE cob.client_code = etc.client_code and cob.activity_code=ca.activity_code AND cob.post = 1 44 UNION ALL 45 Select et.client_code,et.isin ,cc.future_period_desc fpd, decode(instr(sc.symbol,'-PRO'),0 ,null,'-PRO') prov 46 ,sum(decode(et.buy_or_sell,'B' ,et.volume,-et.volume)) quantity 47 From Equity_trade et, Clearing_calendar cc, Security sc, Equity_temp_capital_g_l_sum etc 48 WHERE et.Client_code = etc.Client_code and et.trade_date <='06-SEP-08' and Nvl(et.bill_number,0) = 0 49 and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 50 group by et.client_code ,et.isin ,cc.future_period_desc ,sc.symbol 51 UNION ALL 52 select et.client_code ,et.isin ,null fpd,'-PROW' prov,sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity 53 from equity_trade et,clearing_calendar cc, security sc, Equity_temp_capital_g_l_sum etc 54 WHERE et.client_code = etc.client_code and et.bill_number is null and et.trade_date > '06-SEP-08' 55 and et.trade_type = 56 ( 57 select es.release_cot_trade from equity_system es 58 ) and sc.symbol like '%-PRO' and et.bill_number is null and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 59 group by et.client_code ,et.isin ,cc.future_period_desc,sc.symbol 60 UNION ALL 61 select et.client_code ,et.isin,cc.future_period_desc fpd ,'-FUTW' prov ,sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity 62 from equity_trade et ,clearing_calendar cc ,security sc, Equity_temp_capital_g_l_sum etc 63 WHERE et.client_code = etc.client_code and et.bill_number is null and et.trade_date > '06-SEP-08' 64 and et.trade_type = 65 ( 66 select es.release_cot_trade from equity_system es 67 ) 68 and sc.symbol not like '%-PRO' and cc.future_period_desc is not null and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 69 group by et.client_code,et.isin ,cc.future_period_desc,sc.symbol 70 ) dq 71 group by dq.client_code ,dq.isin ,fpd ,prov 72 having (SUM(dq.quantity)<>0) 73 / 0 rows created. Execution Plan ---------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------- | Id | Operation | Name | R ows | Bytes | Cost | -------------------------------------------------------------------------------- ---------------------- | 0 | INSERT STATEMENT | | 11 | 374 | 241 | | 1 | FILTER | | | | | | 2 | SORT GROUP BY | | 11 | 374 | 241 | | 3 | VIEW | | 11 | 374 | 240 | | 4 | UNION-ALL | | | | | | 5 | VIEW | | 1 | 29 | 75 | | 6 | FILTER | | | | | | 7 | SORT GROUP BY | | 1 | 39 | 75 | | 8 | FILTER | | | | | | 9 | TABLE ACCESS BY INDEX ROWID | CUSTODY_MASTER | 16 | 560 | 71 | | 10 | NESTED LOOPS | | 16 | 624 | 72 | | 11 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 12 | INDEX RANGE SCAN | REF_23834_FK | 327 | | 2 | | 13 | TABLE ACCESS BY INDEX ROWID | CUSTODY_MASTER | 1 | 13 | 2 | | 14 | INDEX UNIQUE SCAN | PK_CUSTODYMASTER_3__11 | 1 | | 1 | | 15 | SORT GROUP BY | | 1 | 49 | 13 | | 16 | NESTED LOOPS | | 1 | 49 | 11 | | 17 | VIEW | | 1 | 37 | 10 | | 18 | FILTER | | | | | | 19 | SORT GROUP BY | | 1 | 46 | 10 | | 20 | TABLE ACCESS BY INDEX ROWID| CUSTODY_MASTER | 1 | 42 | 6 | | 21 | NESTED LOOPS | | 1 | 46 | 7 | | 22 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 23 | INDEX RANGE SCAN | IDX_CUS_MST_CC_ISIN_TD | 1 | | 5 | | 24 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 12 | 1 | | 25 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 26 | HASH JOIN | | 6 | 240 | 5 | | 27 | MERGE JOIN CARTESIAN | | 16 | 160 | 2 | | 28 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 29 | BUFFER SORT | | 16 | 96 | 1 | | 30 | TABLE ACCESS FULL | CUSTODY_ACTIVITY | 16 | 96 | 1 | | 31 | TABLE ACCESS FULL | CUSTODY_OPENING_BALANCES | 1015 | 30450 | 2 | | 32 | SORT GROUP BY | | 1 | 65 | 123 | | 33 | NESTED LOOPS | | 1 | 65 | 120 | | 34 | NESTED LOOPS | | 1 | 59 | 119 | | 35 | MERGE JOIN CARTESIAN | | 1 | 23 | 115 | | 36 | TABLE ACCESS FULL | SECURITY | 1160 | 22040 | 3 | | 37 | BUFFER SORT | | 1 | 4 | 112 | | 38 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 39 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE | 1 | 36 | 4 | | 40 | INDEX RANGE SCAN | IDX$$_00560002 | 293 | | 2 | | 41 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 42 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 43 | SORT GROUP BY | | 1 | 68 | 12 | | 44 | NESTED LOOPS | | 1 | 68 | 9 | | 45 | NESTED LOOPS | | 1 | 62 | 8 | | 46 | NESTED LOOPS | | 1 | 43 | 7 | | 47 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 48 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE | 1 | 39 | 6 | | 49 | INDEX RANGE SCAN | IDX_EQUITY_TR_CLCD_DT | 17 | | 3 | | 50 | INDEX FULL SCAN | FK_EQ_SYS_RLS_COT_TRD_TYPE_FK | 1 | 4 | 1 | | 51 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 52 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | | 53 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 54 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 55 | SORT GROUP BY | | 1 | 68 | 12 | | 56 | NESTED LOOPS | | 1 | 68 | 9 | | 57 | NESTED LOOPS | | 1 | 49 | 8 | | 58 | NESTED LOOPS | | 1 | 43 | 7 | | 59 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 60 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE | 1 | 39 | 6 | | 61 | INDEX RANGE SCAN | IDX_EQUITY_TR_CLCD_DT | 17 | | 3 | | 62 | INDEX FULL SCAN | FK_EQ_SYS_RLS_COT_TRD_TYPE_FK | 1 | 4 | 1 | | 63 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 64 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 65 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 66 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | -------------------------------------------------------------------------------- ---------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 549 recursive calls 0 db block gets 238 consistent gets 0 physical reads 0 redo size 558 bytes sent via SQL*Net to client 4726 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 14 sorts (memory) 0 sorts (disk) 0 rows processed SQL> set linesize 600 SQL> alter system flush shared_pool; System altered. SQL> / System altered. SQL> insert into test_equity_margin 2 ( 3 CLIENT_CODE,ISIN ,CASH_BALANCE,CUST_BALANCE ,MARKET_DATE ,MARKET_RATE ,OUTSTND_AMOUNT ,CASH_MARGIN,CUST_MARGIN 4 ,CASH_BUYING_POWER ,CUST_BUYING_POWER ,SHORT_SALE_VALUE ,FUTURE_PERIOD_DESC ,EQUITY_CURRENT_POSITION ,PROV_TRADE 5 ) 6 select dq.client_code,dq.isin ,0 ,SUM(dq.quantity) custody_balance ,NULL ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,fpd ,0 ,prov 7 FROM 8 ( 9 SELECT * FROM 10 ( 11 SELECT cm.client_code ,cm.isin ,NULL fpd ,NULL prov ,sum(decode(cm.in_or_out ,'I' 12 ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0))))quantity 13 FROM Custody_master cm ,Equity_temp_capital_g_l_sum etc 14 WHERE cm.client_code = etc.client_code 15 and Exists 16 ( 17 select 1 18 from custody_master cm2 19 where cm.transaction_id = cm2.transaction_id 20 and cm2.transaction_Date<='06-SEP-08' 21 ) 22 AND cm.post = 1 23 Group by cm.client_code,cm.isin 24 ) 25 where quantity <> 0 26 UNION ALL 27 select scm.client_code,scm.isin ,scm.fpd ,NULL prov,sum(scm.quantity) 28 from 29 ( 30 SELECT cm.clearing_no ,cm.client_code ,cm.isin,NULL fpd 31 ,sum(decode(cm.in_or_out,'I' ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)))) quantity 32 FROM custody_master cm , Equity_temp_capital_g_l_sum etc 33 WHERE cm.client_Code = etc.client_code and cm.transaction_Date > '06-SEP-08' 34 and cm.clearing_no is not null AND cm.post = 1 35 group by cm.clearing_no,cm.client_code,cm.isin ,NULL 36 having (sum(decode(cm.in_or_out ,'I' ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0))))<>0) 37 ) scm ,clearing_calendar cc 38 where scm.clearing_no=cc.clearing_no and cc.clearing_end_date <='06-SEP-08' 39 group by scm.client_code ,scm.isin ,scm.fpd 40 UNION ALL 41 SELECT cob.client_code ,cob.isin ,NULL fpd ,NULL prov ,decode(ca.in_or_out ,'I' ,(nvl(cob.un_reg_quantity ,0) + nvl(cob.reg_quantity ,0)) , - (nvl(cob.un_reg_quantity ,0) + nvl(cob.reg_quantity ,0))) quantity 42 FROM custody_opening_balances cob, custody_activity ca, Equity_temp_capital_g_l_sum etc 43 WHERE cob.client_code = etc.client_code and cob.activity_code=ca.activity_code AND cob.post = 1 44 UNION ALL 45 Select et.client_code,et.isin ,cc.future_period_desc fpd, decode(instr(sc.symbol,'-PRO'),0 ,null,'-PRO') prov 46 ,sum(decode(et.buy_or_sell,'B' ,et.volume,-et.volume)) quantity 47 From Equity_trade et, Clearing_calendar cc, Security sc, Equity_temp_capital_g_l_sum etc 48 WHERE et.Client_code = etc.Client_code and et.trade_date <='06-SEP-08' and Nvl(et.bill_number,0) = 0 49 and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 50 group by et.client_code ,et.isin ,cc.future_period_desc ,sc.symbol 51 UNION ALL 52 select et.client_code ,et.isin ,null fpd,'-PROW' prov,sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity 53 from equity_trade et,clearing_calendar cc, security sc, Equity_temp_capital_g_l_sum etc 54 WHERE et.client_code = etc.client_code and et.bill_number is null and et.trade_date > '06-SEP-08' 55 and et.trade_type = 56 ( 57 select es.release_cot_trade from equity_system es 58 ) and sc.symbol like '%-PRO' and et.bill_number is null and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 59 group by et.client_code ,et.isin ,cc.future_period_desc,sc.symbol 60 UNION ALL 61 select et.client_code ,et.isin,cc.future_period_desc fpd ,'-FUTW' prov ,sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity 62 from equity_trade et ,clearing_calendar cc ,security sc, Equity_temp_capital_g_l_sum etc 63 WHERE et.client_code = etc.client_code and et.bill_number is null and et.trade_date > '06-SEP-08' 64 and et.trade_type = 65 ( 66 select es.release_cot_trade from equity_system es 67 ) 68 and sc.symbol not like '%-PRO' and cc.future_period_desc is not null and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 69 group by et.client_code,et.isin ,cc.future_period_desc,sc.symbol 70 ) dq 71 group by dq.client_code ,dq.isin ,fpd ,prov 72 having (SUM(dq.quantity)<>0) 73 / 0 rows created. Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------------------ | 0 | INSERT STATEMENT | | 11 | 374 | 241 | | 1 | FILTER | | | | | | 2 | SORT GROUP BY | | 11 | 374 | 241 | | 3 | VIEW | | 11 | 374 | 240 | | 4 | UNION-ALL | | | | | | 5 | VIEW | | 1 | 29 | 75 | | 6 | FILTER | | | | | | 7 | SORT GROUP BY | | 1 | 39 | 75 | | 8 | FILTER | | | | | | 9 | TABLE ACCESS BY INDEX ROWID | CUSTODY_MASTER | 16 | 560 | 71 | | 10 | NESTED LOOPS | | 16 | 624 | 72 | | 11 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 12 | INDEX RANGE SCAN | REF_23834_FK | 327 | | 2 | | 13 | TABLE ACCESS BY INDEX ROWID | CUSTODY_MASTER | 1 | 13 | 2 | | 14 | INDEX UNIQUE SCAN | PK_CUSTODYMASTER_3__11 | 1 | | 1 | | 15 | SORT GROUP BY | | 1 | 49 | 13 | | 16 | NESTED LOOPS | | 1 | 49 | 11 | | 17 | VIEW | | 1 | 37 | 10 | | 18 | FILTER | | | | | | 19 | SORT GROUP BY | | 1 | 46 | 10 | | 20 | TABLE ACCESS BY INDEX ROWID| CUSTODY_MASTER | 1 | 42 | 6 | | 21 | NESTED LOOPS | | 1 | 46 | 7 | | 22 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 23 | INDEX RANGE SCAN | IDX_CUS_MST_CC_ISIN_TD | 1 | | 5 | | 24 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 12 | 1 | | 25 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 26 | HASH JOIN | | 6 | 240 | 5 | | 27 | MERGE JOIN CARTESIAN | | 16 | 160 | 2 | | 28 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 29 | BUFFER SORT | | 16 | 96 | 1 | | 30 | TABLE ACCESS FULL | CUSTODY_ACTIVITY | 16 | 96 | 1 | | 31 | TABLE ACCESS FULL | CUSTODY_OPENING_BALANCES | 1015 | 30450 | 2 | | 32 | SORT GROUP BY | | 1 | 65 | 123 | | 33 | NESTED LOOPS | | 1 | 65 | 120 | | 34 | NESTED LOOPS | | 1 | 59 | 119 | | 35 | MERGE JOIN CARTESIAN | | 1 | 23 | 115 | | 36 | TABLE ACCESS FULL | SECURITY | 1160 | 22040 | 3 | | 37 | BUFFER SORT | | 1 | 4 | 112 | | 38 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 39 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE | 1 | 36 | 4 | | 40 | INDEX RANGE SCAN | IDX$$_00560002 | 293 | | 2 | | 41 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 42 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 43 | SORT GROUP BY | | 1 | 68 | 12 | | 44 | NESTED LOOPS | | 1 | 68 | 9 | | 45 | NESTED LOOPS | | 1 | 62 | 8 | | 46 | NESTED LOOPS | | 1 | 43 | 7 | | 47 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 48 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE | 1 | 39 | 6 | | 49 | INDEX RANGE SCAN | IDX_EQUITY_TR_CLCD_DT | 17 | | 3 | | 50 | INDEX FULL SCAN | FK_EQ_SYS_RLS_COT_TRD_TYPE_FK | 1 | 4 | 1 | | 51 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 52 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | | 53 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 54 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 55 | SORT GROUP BY | | 1 | 68 | 12 | | 56 | NESTED LOOPS | | 1 | 68 | 9 | | 57 | NESTED LOOPS | | 1 | 49 | 8 | | 58 | NESTED LOOPS | | 1 | 43 | 7 | | 59 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 60 | TABLE ACCESS BY INDEX ROWID | EQUITY_TRADE | 1 | 39 | 6 | | 61 | INDEX RANGE SCAN | IDX_EQUITY_TR_CLCD_DT | 17 | | 3 | | 62 | INDEX FULL SCAN | FK_EQ_SYS_RLS_COT_TRD_TYPE_FK | 1 | 4 | 1 | | 63 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 64 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 65 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 66 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | ------------------------------------------------------------------------------------------------------ Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 10515 recursive calls 0 db block gets 4007 consistent gets 1 physical reads 0 redo size 558 bytes sent via SQL*Net to client 4724 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client 144 sorts (memory) 0 sorts (disk) 0 rows processed SQL> select et.client_code ,et.isin,cc.future_period_desc fpd ,'-FUTW' prov ,sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity 2 from equity_trade et ,clearing_calendar cc ,security sc, Equity_temp_capital_g_l_sum etc 3 WHERE et.client_code = etc.client_code and et.bill_number is null and et.trade_date > '06-SEP-08' 4 and et.trade_type = 5 ( 6 select es.release_cot_trade from equity_system es 7 ) 8 and sc.symbol not like '%-PRO' and cc.future_period_desc is not null and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 9 group by et.client_code,et.isin ,cc.future_period_desc,sc.symbol 10 / no rows selected Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 68 | 12 | | 1 | SORT GROUP BY | | 1 | 68 | 12 | | 2 | NESTED LOOPS | | 1 | 68 | 9 | | 3 | NESTED LOOPS | | 1 | 49 | 8 | | 4 | NESTED LOOPS | | 1 | 43 | 7 | | 5 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 6 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 39 | 6 | | 7 | INDEX RANGE SCAN | IDX_EQUITY_TR_CLCD_DT | 17 | | 3 | | 8 | INDEX FULL SCAN | FK_EQ_SYS_RLS_COT_TRD_TYPE_FK | 1 | 4 | 1 | | 9 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 10 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | | 11 | TABLE ACCESS BY INDEX ROWID | SECURITY | 1 | 19 | 1 | | 12 | INDEX UNIQUE SCAN | PK_SECURITY | 1 | | | ------------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 92 recursive calls 0 db block gets 28 consistent gets 0 physical reads 0 redo size 449 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed SQL> Select et.client_code,et.isin ,cc.future_period_desc fpd, decode(instr(sc.symbol,'-PRO'),0 ,null,'-PRO') prov 2 ,sum(decode(et.buy_or_sell,'B' ,et.volume,-et.volume)) quantity 3 From Equity_trade et, Clearing_calendar cc, Security sc, Equity_temp_capital_g_l_sum etc 4 WHERE et.Client_code = etc.Client_code and et.trade_date <='06-SEP-08' and Nvl(et.bill_number,0) = 0 5 and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 6 group by et.client_code ,et.isin ,cc.future_period_desc ,sc.symbol 7 / no rows selected Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 65 | 123 | | 1 | SORT GROUP BY | | 1 | 65 | 123 | | 2 | NESTED LOOPS | | 1 | 65 | 120 | | 3 | NESTED LOOPS | | 1 | 59 | 119 | | 4 | MERGE JOIN CARTESIAN | | 1 | 23 | 115 | | 5 | TABLE ACCESS FULL | SECURITY | 1160 | 22040 | 3 | | 6 | BUFFER SORT | | 1 | 4 | 112 | | 7 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 8 | TABLE ACCESS BY INDEX ROWID| EQUITY_TRADE | 1 | 36 | 4 | | 9 | INDEX RANGE SCAN | IDX$$_00560002 | 293 | | 2 | | 10 | TABLE ACCESS BY INDEX ROWID | CLEARING_CALENDAR | 1 | 6 | 1 | | 11 | INDEX UNIQUE SCAN | PK_CLEARINGDATES | 1 | | | ---------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 92 recursive calls 0 db block gets 59 consistent gets 0 physical reads 0 redo size 449 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 0 rows processed SQL> SELECT cob.client_code ,cob.isin ,NULL fpd ,NULL prov ,decode(ca.in_or_out ,'I' ,(nvl(cob.un_reg_quantity ,0) + nvl(cob.reg_quantity ,0)) , - (nvl(cob.un_reg_quantity ,0) + nvl(cob.reg_quantity ,0))) quantity 2 FROM custody_opening_balances cob, custody_activity ca, Equity_temp_capital_g_l_sum etc 3 WHERE cob.client_code = etc.client_code and cob.activity_code=ca.activity_code AND cob.post = 1 4 / no rows selected Execution Plan ---------------------------------------------------------- ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 240 | 5 | | 1 | HASH JOIN | | 6 | 240 | 5 | | 2 | MERGE JOIN CARTESIAN| | 16 | 160 | 2 | | 3 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 4 | BUFFER SORT | | 16 | 96 | 1 | | 5 | TABLE ACCESS FULL | CUSTODY_ACTIVITY | 16 | 96 | 1 | | 6 | TABLE ACCESS FULL | CUSTODY_OPENING_BALANCES | 1015 | 30450 | 2 | ------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 36 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 449 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL> SELECT cm.clearing_no ,cm.client_code ,cm.isin,NULL fpd 2 ,sum(decode(cm.in_or_out,'I' ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)))) quantity 3 FROM custody_master cm , Equity_temp_capital_g_l_sum etc 4 WHERE cm.client_Code = etc.client_code and cm.transaction_Date > '06-SEP-08' 5 and cm.clearing_no is not null AND cm.post = 1 6 group by cm.clearing_no,cm.client_code,cm.isin ,NULL 7 having (sum(decode(cm.in_or_out ,'I' ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0))))<>0) 8 / no rows selected Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 46 | 10 | | 1 | FILTER | | | | | | 2 | SORT GROUP BY | | 1 | 46 | 10 | | 3 | TABLE ACCESS BY INDEX ROWID| CUSTODY_MASTER | 1 | 42 | 6 | | 4 | NESTED LOOPS | | 1 | 46 | 7 | | 5 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 6 | INDEX RANGE SCAN | IDX_CUS_MST_CC_ISIN_TD | 1 | | 5 | --------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 16 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 456 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed SQL> ed Wrote file afiedt.buf 1 SELECT cm.client_code ,cm.isin ,NULL fpd ,NULL prov ,sum(decode(cm.in_or_out ,'I' 2 ,(nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0)) , - (nvl(cm.un_reg_quantity ,0) + nvl(cm.reg_quantity ,0))))quantity 3 FROM Custody_master cm ,Equity_temp_capital_g_l_sum etc 4 WHERE cm.client_code = etc.client_code 5 and Exists 6 ( 7 select 1 8 from custody_master cm2 9 where cm.transaction_id = cm2.transaction_id 10 and cm2.transaction_Date<='06-SEP-08' 11 ) 12 AND cm.post = 1 13* Group by cm.client_code,cm.isin SQL> / no rows selected Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16 | 624 | 75 | | 1 | SORT GROUP BY | | 16 | 624 | 75 | | 2 | FILTER | | | | | | 3 | TABLE ACCESS BY INDEX ROWID| CUSTODY_MASTER | 16 | 560 | 71 | | 4 | NESTED LOOPS | | 16 | 624 | 72 | | 5 | TABLE ACCESS FULL | EQUITY_TEMP_CAPITAL_G_L_SUM | 1 | 4 | 1 | | 6 | INDEX RANGE SCAN | REF_23834_FK | 327 | | 2 | | 7 | TABLE ACCESS BY INDEX ROWID| CUSTODY_MASTER | 1 | 13 | 2 | | 8 | INDEX UNIQUE SCAN | PK_CUSTODYMASTER_3__11 | 1 | | 1 | --------------------------------------------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 29 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 449 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed SQL> spool off