SELECT prem.business_unit, prem.direct_assumed_ceded_code, prem.ledger_currency_code, prem.mcc_code, prem.management_unit, prem.product, prem.financial_product, nvl(prem.premium_in_ledger_currency,0) premium_in_ledger_currency, -- NVL added Aug 21, 2008 nvl(prem.premium_in_original_currency,0) premium_in_original_currency, -- Added Aug 21, 2008 M4640 nvl(prem.premium_in_us_dollars,0) premium_in_us_dollars, -- NVL added Aug 21, 2008 prem.rcc, prem.pre_explosion_rcc, -- Issue M1997 Laks 09/15 prem.acc_code, prem.transaction_code, prem.accounting_period, prem.exposure_period, prem.policy_effective_month, prem.policy_key, prem.policy_line_of_business, prem.producer_code, prem.underwriting_year, prem.ledger_key, prem.account_key, prem.policy_coverage_key, nvl(prem.original_currency_code,' ') original_currency_code, -- NVL added Issue M4640 Aug 21, 2008 prem.authorized_unauthorized, prem.transaction_effective_date, prem.transaction_expiration_date, prem.exposure_year, prem.reinsurance_mask, prem.rcccategory, prem.rccclassification, prem.detail_level_indicator, prem.bermbase_level_indicator, prem.padb_level_indicator, prem.sas_level_indicator, -- Issue 443 prem.cnv_adjust_indicator, -- Issue 413 prem.originating_business_unit, prem.actuarial_blend_indicator, ml1.management_line_of_business_id management_lob_id_act, mu1.reserving_line_id mgt_unit_reserving_lob_id_act, bu1.reserving_line_id business_unit_lob_id_act, ml1.reserving_class_id mgt_unit_res_class_lob_id_act, -- added A12 bl1.reserving_class_id business_unit_class_lob_id_act,-- added A12 ml2.management_line_of_business_id management_lob_id_fin, mu2.reserving_line_id mgt_unit_reserving_lob_id_fin, bu2.reserving_line_id business_unit_lob_id_fin, ml2.reserving_class_id mgt_unit_res_class_lob_id_fin, -- added A12 bl2.reserving_class_id business_unit_class_lob_id_fin -- added A12 FROM rhs_premium prem, bu_res_line_map bu1, mgt_unit_res_line_map mu1, mgt_unit_reserving_lines ml1, bu_reserving_lines bl1, -- added A12 bu_res_line_map bu2, mgt_unit_res_line_map mu2, mgt_unit_reserving_lines ml2, bu_reserving_lines bl2 -- added A12 WHERE ( prem.business_unit = bu1.business_unit (+) AND prem.product = bu1.product (+) ) AND ( prem.management_unit = mu1.management_unit (+) AND prem.product = mu1.product(+) ) AND mu1.reserving_line_id = ml1.reserving_line_id (+) AND bu1.reserving_line_id = bl1.reserving_line_id (+) -- added A12 AND ( prem.business_unit = bu2.business_unit (+) AND prem.financial_product = bu2.product (+) ) AND ( prem.management_unit = mu2.management_unit (+) AND prem.financial_product = mu2.product(+) ) AND mu2.reserving_line_id = ml2.reserving_line_id (+) AND bu2.reserving_line_id = bl2.reserving_line_id (+); PLAN_TABLE_OUTPUT Plan hash value: 3475794837 ----------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 346M| 124G| 416K (2)| 01:37:13 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10008 | 346M| 124G| 416K (2)| 01:37:13 | | | Q1,08 | P->S | QC (RAND) | |* 3 | HASH JOIN RIGHT OUTER | | 346M| 124G| 416K (2)| 01:37:13 | | | Q1,08 | PCWP | | | 4 | BUFFER SORT | | | | | | | | Q1,08 | PCWC | | | 5 | PX RECEIVE | | 46 | 1196 | 7 (0)| 00:00:01 | | | Q1,08 | PCWP | | | 6 | PX SEND BROADCAST | :TQ10000 | 46 | 1196 | 7 (0)| 00:00:01 | | | | S->P | BROADCAST | | 7 | VIEW | BU_RESERVING_LINES | 46 | 1196 | 7 (0)| 00:00:01 | | | | | | | 8 | NESTED LOOPS OUTER | | 46 | 1748 | 7 (0)| 00:00:01 | | | | | | |* 9 | TABLE ACCESS FULL | RESERVING_LINES | 46 | 1564 | 7 (0)| 00:00:01 | | | | | | |* 10 | INDEX UNIQUE SCAN | RESERVING_CLASSES_PK | 1 | 4 | 0 (0)| 00:00:01 | | | | | | |* 11 | HASH JOIN RIGHT OUTER | | 346M| 116G| 415K (2)| 01:37:04 | | | Q1,08 | PCWP | | | 12 | BUFFER SORT | | | | | | | | Q1,08 | PCWC | | | 13 | PX RECEIVE | | 124K| 2182K| 87 (2)| 00:00:02 | | | Q1,08 | PCWP | | | 14 | PX SEND BROADCAST | :TQ10001 | 124K| 2182K| 87 (2)| 00:00:02 | | | | S->P | BROADCAST | | 15 | TABLE ACCESS FULL | BU_RES_LINE_MAP | 124K| 2182K| 87 (2)| 00:00:02 | | | | | | |* 16 | HASH JOIN RIGHT OUTER | | 346M| 110G| 415K (2)| 01:36:54 | | | Q1,08 | PCWP | | | 17 | BUFFER SORT | | | | | | | | Q1,08 | PCWC | | | 18 | PX RECEIVE | | 46 | 1196 | 7 (0)| 00:00:01 | | | Q1,08 | PCWP | | | 19 | PX SEND BROADCAST | :TQ10002 | 46 | 1196 | 7 (0)| 00:00:01 | | | | S->P | BROADCAST | | 20 | VIEW | BU_RESERVING_LINES | 46 | 1196 | 7 (0)| 00:00:01 | | | | | | | 21 | NESTED LOOPS OUTER | | 46 | 1748 | 7 (0)| 00:00:01 | | | | | | |* 22 | TABLE ACCESS FULL | RESERVING_LINES | 46 | 1564 | 7 (0)| 00:00:01 | | | | | | |* 23 | INDEX UNIQUE SCAN | RESERVING_CLASSES_PK | 1 | 4 | 0 (0)| 00:00:01 | | | | | | |* 24 | HASH JOIN RIGHT OUTER | | 346M| 101G| 414K (1)| 01:36:45 | | | Q1,08 | PCWP | | | 25 | BUFFER SORT | | | | | | | | Q1,08 | PCWC | | | 26 | PX RECEIVE | | 124K| 2182K| 87 (2)| 00:00:02 | | | Q1,08 | PCWP | | | 27 | PX SEND BROADCAST | :TQ10003 | 124K| 2182K| 87 (2)| 00:00:02 | | | | S->P | BROADCAST | | 28 | TABLE ACCESS FULL | BU_RES_LINE_MAP | 124K| 2182K| 87 (2)| 00:00:02 | | | | | | |* 29 | HASH JOIN RIGHT OUTER | | 346M| 96G| 413K (1)| 01:36:35 | | | Q1,08 | PCWP | | | 30 | BUFFER SORT | | | | | | | | Q1,08 | PCWC | | | 31 | PX RECEIVE | | 46 | 1794 | 11 (10)| 00:00:01 | | | Q1,08 | PCWP | | | 32 | PX SEND BROADCAST | :TQ10004 | 46 | 1794 | 11 (10)| 00:00:01 | | | | S->P | BROADCAST | | 33 | VIEW | MGT_UNIT_RESERVING_LINES | 46 | 1794 | 11 (10)| 00:00:01 | | | | | | |* 34 | HASH JOIN OUTER | | 46 | 1886 | 11 (10)| 00:00:01 | | | | | | |* 35 | TABLE ACCESS FULL | RESERVING_LINES | 46 | 1564 | 7 (0)| 00:00:01 | | | | | | | 36 | TABLE ACCESS FULL | RESERVING_CLASSES | 107 | 749 | 3 (0)| 00:00:01 | | | | | | |* 37 | HASH JOIN RIGHT OUTER | | 346M| 83G| 413K (1)| 01:36:26 | | | Q1,08 | PCWP | | | 38 | BUFFER SORT | | | | | | | | Q1,08 | PCWC | | | 39 | PX RECEIVE | | 93763 | 1648K| 59 (0)| 00:00:01 | | | Q1,08 | PCWP | | | 40 | PX SEND BROADCAST | :TQ10005 | 93763 | 1648K| 59 (0)| 00:00:01 | | | | S->P | BROADCAST | | 41 | INDEX FAST FULL SCAN | MGT_UNIT_RES_LINE_MAP_UK | 93763 | 1648K| 59 (0)| 00:00:01 | | | | | | |* 42 | HASH JOIN RIGHT OUTER | | 346M| 77G| 412K (1)| 01:36:17 | | | Q1,08 | PCWP | | | 43 | BUFFER SORT | | | | | | | | Q1,08 | PCWC | | | 44 | PX RECEIVE | | 46 | 1794 | 11 (10)| 00:00:01 | | | Q1,08 | PCWP | | | 45 | PX SEND BROADCAST | :TQ10006 | 46 | 1794 | 11 (10)| 00:00:01 | | | | S->P | BROADCAST | | 46 | VIEW | MGT_UNIT_RESERVING_LINES | 46 | 1794 | 11 (10)| 00:00:01 | | | | | | |* 47 | HASH JOIN OUTER | | 46 | 1886 | 11 (10)| 00:00:01 | | | | | | |* 48 | TABLE ACCESS FULL | RESERVING_LINES | 46 | 1564 | 7 (0)| 00:00:01 | | | | | | | 49 | TABLE ACCESS FULL | RESERVING_CLASSES | 107 | 749 | 3 (0)| 00:00:01 | | | | | | |* 50 | HASH JOIN RIGHT OUTER | | 346M| 65G| 411K (1)| 01:36:08 | | | Q1,08 | PCWP | | | 51 | BUFFER SORT | | | | | | | | Q1,08 | PCWC | | | 52 | PX RECEIVE | | 93763 | 1648K| 59 (0)| 00:00:01 | | | Q1,08 | PCWP | | | 53 | PX SEND BROADCAST | :TQ10007 | 93763 | 1648K| 59 (0)| 00:00:01 | | | | S->P | BROADCAST | | 54 | INDEX FAST FULL SCAN| MGT_UNIT_RES_LINE_MAP_UK | 93763 | 1648K| 59 (0)| 00:00:01 | | | | | | | 55 | PX BLOCK ITERATOR | | 346M| 59G| 411K (1)| 01:35:58 | 1 | 385 | Q1,08 | PCWC | | | 56 | TABLE ACCESS FULL | RHS_PREMIUM_1 | 346M| 59G| 411K (1)| 01:35:58 | 1 | 385 | Q1,08 | PCWP | | ----------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("BU2"."RESERVING_LINE_ID"="BL2"."RESERVING_LINE_ID"(+)) 9 - filter("RL"."RESERVING_LINE_TYPE"='BU') 10 - access("RL"."RESERVING_CLASS_ID"="RC"."RESERVING_CLASS_ID"(+)) 11 - access("PREM"."BUSINESS_UNIT"="BU2"."BUSINESS_UNIT"(+) AND "PREM"."FINANCIAL_PRODUCT"="BU2"."PRODUCT"(+)) 16 - access("BU1"."RESERVING_LINE_ID"="BL1"."RESERVING_LINE_ID"(+)) 22 - filter("RL"."RESERVING_LINE_TYPE"='BU') 23 - access("RL"."RESERVING_CLASS_ID"="RC"."RESERVING_CLASS_ID"(+)) 24 - access("PREM"."BUSINESS_UNIT"="BU1"."BUSINESS_UNIT"(+) AND "PREM"."PRODUCT"="BU1"."PRODUCT"(+)) 29 - access("MU2"."RESERVING_LINE_ID"="ML2"."RESERVING_LINE_ID"(+)) 34 - access("RL"."RESERVING_CLASS_ID"="RC"."RESERVING_CLASS_ID"(+)) 35 - filter("RL"."RESERVING_LINE_TYPE"='MCC') 37 - access("PREM"."MANAGEMENT_UNIT"="MU2"."MANAGEMENT_UNIT"(+) AND "PREM"."FINANCIAL_PRODUCT"="MU2"."PRODUCT"(+)) 42 - access("MU1"."RESERVING_LINE_ID"="ML1"."RESERVING_LINE_ID"(+)) 47 - access("RL"."RESERVING_CLASS_ID"="RC"."RESERVING_CLASS_ID"(+)) 48 - filter("RL"."RESERVING_LINE_TYPE"='MCC') 50 - access("PREM"."MANAGEMENT_UNIT"="MU1"."MANAGEMENT_UNIT"(+) AND "PREM"."PRODUCT"="MU1"."PRODUCT"(+))