ALTER SESSION SET STATISTICS_LEVEL=ALL; Session altered. SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SHOW PARAMETER optimizer_features_enable NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_features_enable string 11.2.0.4 VAR REPPERIOD NUMBER exec :REPPERIOD := 201312 PL/SQL procedure successfully completed. @"C:\Users\guptad\Desktop\ENH106755_SQL.sql" COUNT(*) ---------- 1949 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- SQL_ID fgy0rt0vywdua, child number 2 ------------------------------------- SELECT COUNT(*) FROM ( WITH "T" AS (SELECT "T0"."C0" "Claim_Status_Number", "T0"."C1" "Inwards___Outwards_Policy", "T0"."C2" "Policy_Number", "T0"."C3" "Claim_Reference", "T0"."C4" "Company_Code", "T0"."C5" "Branch_Code", "T0"."C6" "LoB_Code", "T0"."C7" "Sub_LoB_Code_ID", "T0"."C8" "Business_Segment_Code", SUM("T0"."C10") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C8", "T0"."C3", "T0"."C4", "T0"."C5", "T0"."C6", "T0"."C7", "T0"."C9")/NULLIF(SUM("T0"."C11") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C8", "T0"."C3", "T0"."C4", "T0"."C5", "T0"."C6", "T0"."C7", "T0"."C9"), 0) "AGCS_Share__", "T0"."C9" "Original_Currency", SUM("T0"."C12") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C8", "T0"."C3", "T0"."C4", "T0"."C5", "T0"."C6", "T0"."C7", "T0"."C9") "c12", SUM("T0"."C13") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C8", "T0"."C3", "T0"."C4", "T0"."C5", "T0"."C6", "T0"."C7", "T0"."C9" Plan hash value: 555052544 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:17:45.57 | 4228K| 3666K| 55 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:17:45.57 | 4228K| 3666K| 55 | | | | | 2 | VIEW | | 1 | 573K| 1949 |00:17:45.57 | 4228K| 3666K| 55 | | | | | 3 | HASH UNIQUE | | 1 | 573K| 1949 |00:17:45.57 | 4228K| 3666K| 55 | 2825K| 923K| 36M (0)| | 4 | WINDOW SORT | | 1 | 573K| 2083 |00:17:45.54 | 4228K| 3666K| 55 | 903K| 523K| 802K (0)| |* 5 | VIEW | | 1 | 573K| 2083 |00:17:45.52 | 4228K| 3666K| 55 | | | | | 6 | WINDOW SORT | | 1 | 573K| 2083 |00:17:45.52 | 4228K| 3666K| 55 | 832K| 511K| 739K (0)| |* 7 | HASH JOIN OUTER | | 1 | 573K| 2083 |00:17:45.49 | 4228K| 3666K| 55 | 960K| 960K| 36M (0)| | 8 | VIEW | | 1 | 573K| 2083 |00:13:20.46 | 3140K| 2742K| 0 | | | | | 9 | HASH GROUP BY | | 1 | 573K| 2083 |00:13:20.46 | 3140K| 2742K| 0 | 1941K| 941K| 30M (0)| | 10 | VIEW | VW_FOJ_0 | 1 | 573K| 2209 |00:13:20.44 | 3140K| 2742K| 0 | | | | |* 11 | HASH JOIN FULL OUTER | | 1 | 573K| 2209 |00:13:20.44 | 3140K| 2742K| 0 | 973K| 973K| 2019K (0)| | 12 | VIEW | | 1 | 44984 | 1547 |00:04:35.09 | 1046K| 914K| 0 | | | | | 13 | WINDOW BUFFER | | 1 | 44984 | 1547 |00:04:35.09 | 1046K| 914K| 0 | 302K| 302K| 268K (0)| | 14 | SORT GROUP BY | | 1 | 44984 | 1547 |00:04:35.08 | 1046K| 914K| 0 | 478K| 478K| 424K (0)| |* 15 | HASH JOIN | | 1 | 44984 | 9392 |00:04:35.04 | 1046K| 914K| 0 | 1393K| 1393K| 1332K (0)| |* 16 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | 0 | | | | |* 17 | HASH JOIN | | 1 | 46082 | 9392 |00:04:35.03 | 1046K| 914K| 0 | 1040K| 1040K| 1254K (0)| |* 18 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | 0 | | | | |* 19 | HASH JOIN | | 1 | 47316 | 9392 |00:04:35.02 | 1046K| 914K| 0 | 1679K| 1279K| 2277K (0)| |* 20 | HASH JOIN | | 1 | 47316 | 9392 |00:04:18.81 | 754K| 621K| 0 | 109M| 10M| 128M (0)| |* 21 | TABLE ACCESS BY INDEX ROWID | GG_F_POLICY_FACTS_FR55 | 1 | 63860 | 1342K|00:04:15.32 | 735K| 603K| 0 | | | | | 22 | BITMAP CONVERSION TO ROWIDS | | 1 | | 1466K|00:00:01.09 | 1038 | 0 | 0 | | | | | 23 | BITMAP AND | | 1 | | 73 |00:00:00.58 | 1038 | 0 | 0 | | | | | 24 | BITMAP MERGE | | 1 | | 107 |00:00:00.23 | 534 | 0 | 0 | 4000K| 512K| 4001K (0)| | 25 | BITMAP KEY ITERATION | | 1 | | 1000 |00:00:00.01 | 534 | 0 | 0 | | | | |* 26 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | 0 | | | | |* 27 | BITMAP INDEX RANGE SCAN | GG_F_POL_FACTS_FR55_OFF_IX05 | 12 | | 1000 |00:00:00.01 | 523 | 0 | 0 | | | | | 28 | BITMAP MERGE | | 1 | | 73 |00:00:00.24 | 504 | 0 | 0 | 1024K| 512K| | | 29 | BITMAP KEY ITERATION | | 1 | | 846 |00:00:00.01 | 504 | 0 | 0 | | | | |* 30 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | 0 | | | | |* 31 | BITMAP INDEX RANGE SCAN | GG_F_POL_FACTS_FR55_LOB_IX04 | 25 | | 846 |00:00:00.01 | 499 | 0 | 0 | | | | |* 32 | TABLE ACCESS FULL | COM_D_CLAIM_FR55 | 1 | 141K| 69000 |00:00:01.86 | 18394 | 18390 | 0 | | | | |* 33 | TABLE ACCESS FULL | COM_D_POLICY_FR55 | 1 | 2214K| 2214K|00:00:15.03 | 292K| 292K| 0 | | | | | 34 | VIEW | | 1 | 528K| 2083 |00:08:45.35 | 2093K| 1828K| 0 | | | | | 35 | HASH GROUP BY | | 1 | 528K| 2083 |00:08:45.35 | 2093K| 1828K| 0 | 1719K| 949K| 19M (0)| | 36 | VIEW | VW_FOJ_1 | 1 | 528K| 2092 |00:08:45.33 | 2093K| 1828K| 0 | | | | |* 37 | HASH JOIN FULL OUTER | | 1 | 528K| 2092 |00:08:45.33 | 2093K| 1828K| 0 | 841K| 841K| 2597K (0)| | 38 | VIEW | | 1 | 11150 | 1063 |00:04:16.61 | 1046K| 914K| 0 | | | | | 39 | WINDOW BUFFER | | 1 | 11150 | 1063 |00:04:16.61 | 1046K| 914K| 0 | 267K| 267K| 237K (0)| | 40 | SORT GROUP BY | | 1 | 11150 | 1063 |00:04:16.60 | 1046K| 914K| 0 | 372K| 372K| 330K (0)| |* 41 | HASH JOIN | | 1 | 11150 | 3902 |00:04:16.57 | 1046K| 914K| 0 | 1294K| 1090K| 2885K (0)| |* 42 | HASH JOIN | | 1 | 11150 | 3902 |00:04:00.66 | 754K| 621K| 0 | 10M| 2331K| 12M (0)| |* 43 | HASH JOIN | | 1 | 11150 | 73858 |00:03:58.74 | 735K| 603K| 0 | 1393K| 1393K| 1332K (0)| |* 44 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | 0 | | | | |* 45 | HASH JOIN | | 1 | 11422 | 73858 |00:03:58.64 | 735K| 603K| 0 | 1483K| 1483K| 1573K (0)| |* 46 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | 0 | | | | |* 47 | TABLE ACCESS BY INDEX ROWID | GG_F_POLICY_FACTS_FR55 | 1 | 11728 | 73858 |00:03:58.51 | 735K| 603K| 0 | | | | | 48 | BITMAP CONVERSION TO ROWIDS| | 1 | | 1466K|00:00:01.18 | 1038 | 0 | 0 | | | | | 49 | BITMAP AND | | 1 | | 73 |00:00:00.59 | 1038 | 0 | 0 | | | | | 50 | BITMAP MERGE | | 1 | | 107 |00:00:00.23 | 534 | 0 | 0 | 4000K| 512K| 4001K (0)| | 51 | BITMAP KEY ITERATION | | 1 | | 1000 |00:00:00.01 | 534 | 0 | 0 | | | | |* 52 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | 0 | | | | |* 53 | BITMAP INDEX RANGE SCAN| GG_F_POL_FACTS_FR55_OFF_IX05 | 12 | | 1000 |00:00:00.01 | 523 | 0 | 0 | | | | | 54 | BITMAP MERGE | | 1 | | 73 |00:00:00.25 | 504 | 0 | 0 | 1024K| 512K| | | 55 | BITMAP KEY ITERATION | | 1 | | 846 |00:00:00.01 | 504 | 0 | 0 | | | | |* 56 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | 0 | | | | |* 57 | BITMAP INDEX RANGE SCAN| GG_F_POL_FACTS_FR55_LOB_IX04 | 25 | | 846 |00:00:00.01 | 499 | 0 | 0 | | | | |* 58 | TABLE ACCESS FULL | COM_D_CLAIM_FR55 | 1 | 141K| 69000 |00:00:01.76 | 18393 | 18390 | 0 | | | | |* 59 | TABLE ACCESS FULL | COM_D_POLICY_FR55 | 1 | 2214K| 2214K|00:00:14.71 | 292K| 292K| 0 | | | | | 60 | VIEW | | 1 | 528K| 2083 |00:04:28.71 | 1046K| 914K| 0 | | | | | 61 | WINDOW BUFFER | | 1 | 528K| 2083 |00:04:28.71 | 1046K| 914K| 0 | 337K| 337K| 299K (0)| | 62 | SORT GROUP BY | | 1 | 528K| 2083 |00:04:28.70 | 1046K| 914K| 0 | 478K| 478K| 424K (0)| |* 63 | HASH JOIN | | 1 | 528K| 13294 |00:04:28.64 | 1046K| 914K| 0 | 1393K| 1393K| 1360K (0)| |* 64 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | 0 | | | | |* 65 | HASH JOIN | | 1 | 541K| 13294 |00:04:28.63 | 1046K| 914K| 0 | 1483K| 1483K| 1573K (0)| |* 66 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | 0 | | | | |* 67 | HASH JOIN | | 1 | 556K| 13294 |00:04:28.62 | 1046K| 914K| 0 | 2069K| 1274K| 11M (0)| |* 68 | HASH JOIN | | 1 | 565K| 13294 |00:04:12.72 | 754K| 621K| 0 | 5256K| 1683K| 6794K (0)| |* 69 | TABLE ACCESS FULL | COM_D_CLAIM_FR55 | 1 | 141K| 69000 |00:00:00.54 | 18393 | 18390 | 0 | | | | |* 70 | TABLE ACCESS BY INDEX ROWID | GG_F_POLICY_FACTS_FR55 | 1 | 1172K| 1416K|00:04:11.08 | 735K| 603K| 0 | | | | | 71 | BITMAP CONVERSION TO ROWIDS| | 1 | | 1466K|00:00:01.10 | 1038 | 0 | 0 | | | | | 72 | BITMAP AND | | 1 | | 73 |00:00:00.59 | 1038 | 0 | 0 | | | | | 73 | BITMAP MERGE | | 1 | | 107 |00:00:00.23 | 534 | 0 | 0 | 4000K| 512K| 4001K (0)| | 74 | BITMAP KEY ITERATION | | 1 | | 1000 |00:00:00.01 | 534 | 0 | 0 | | | | |* 75 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | 0 | | | | |* 76 | BITMAP INDEX RANGE SCAN| GG_F_POL_FACTS_FR55_OFF_IX05 | 12 | | 1000 |00:00:00.01 | 523 | 0 | 0 | | | | | 77 | BITMAP MERGE | | 1 | | 73 |00:00:00.25 | 504 | 0 | 0 | 1024K| 512K| | | 78 | BITMAP KEY ITERATION | | 1 | | 846 |00:00:00.01 | 504 | 0 | 0 | | | | |* 79 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | 0 | | | | |* 80 | BITMAP INDEX RANGE SCAN| GG_F_POL_FACTS_FR55_LOB_IX04 | 25 | | 846 |00:00:00.01 | 499 | 0 | 0 | | | | |* 81 | TABLE ACCESS FULL | COM_D_POLICY_FR55 | 1 | 2214K| 2214K|00:00:14.57 | 292K| 292K| 0 | | | | | 82 | VIEW | | 1 | 655K| 2633 |00:04:24.99 | 1088K| 924K| 55 | | | | | 83 | TEMP TABLE TRANSFORMATION | | 1 | | 2633 |00:04:24.99 | 1088K| 924K| 55 | | | | | 84 | LOAD AS SELECT | | 1 | | 0 |00:00:00.18 | 541 | 481 | 55 | 530K| 530K| 530K (0)| |* 85 | TABLE ACCESS FULL | COM_D_PLACING_PARTNER | 1 | 7093 | 9542 |00:00:00.15 | 482 | 480 | 0 | | | | | 86 | HASH UNIQUE | | 1 | 655K| 2633 |00:04:24.80 | 1088K| 923K| 0 | 7453K| 1941K| 30M (0)| | 87 | WINDOW SORT | | 1 | 655K| 16599 |00:04:24.75 | 1088K| 923K| 0 | 9972K| 1220K| 8863K (0)| |* 88 | HASH JOIN | | 1 | 655K| 16599 |00:04:24.42 | 1088K| 923K| 0 | 1185K| 1185K| 1339K (0)| |* 89 | TABLE ACCESS FULL | GG_D_CURRENCY | 1 | 578 | 1005 |00:00:00.36 | 1348 | 1344 | 0 | | | | |* 90 | HASH JOIN | | 1 | 585K| 16736 |00:04:24.03 | 1086K| 922K| 0 | 982K| 982K| 1319K (0)| |* 91 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | 0 | | | | |* 92 | HASH JOIN | | 1 | 600K| 16736 |00:04:24.02 | 1086K| 922K| 0 | 6036K| 1992K| 27M (0)| |* 93 | HASH JOIN | | 1 | 600K| 16736 |00:04:08.27 | 794K| 630K| 0 | 12M| 3091K| 13M (0)| |* 94 | TABLE ACCESS FULL | COM_D_INSURED | 1 | 138K| 163K|00:00:01.31 | 6210 | 6202 | 0 | | | | |* 95 | HASH JOIN | | 1 | 600K| 16736 |00:04:06.80 | 787K| 623K| 0 | 7901K| 2581K| 7833K (0)| |* 96 | TABLE ACCESS FULL | COM_D_CLAIM_FR55 | 1 | 141K| 69000 |00:00:01.88 | 18393 | 18390 | 0 | | | | |* 97 | HASH JOIN | | 1 | 1246K| 1466K|00:04:03.99 | 769K| 605K| 0 | 1540K| 1449K| 1637K (0)| | 98 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6AEB_D9A59F06 | 1 | 7093 | 9542 |00:00:00.01 | 59 | 55 | 0 | | | | |* 99 | HASH JOIN | | 1 | 1246K| 1466K|00:04:02.52 | 769K| 605K| 0 | 960K| 960K| 1019K (0)| |*100 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | 0 | | | | | 101 | VIEW | VW_ST_FA989829 | 1 | 1277K| 1466K|00:04:00.60 | 769K| 605K| 0 | | | | | 102 | NESTED LOOPS | | 1 | 1277K| 1466K|00:03:59.88 | 769K| 605K| 0 | | | | | 103 | BITMAP CONVERSION TO ROWIDS | | 1 | 1277K| 1466K|00:00:03.95 | 34733 | 2230 | 0 | | | | | 104 | BITMAP AND | | 1 | | 73 |00:00:03.50 | 34733 | 2230 | 0 | | | | | 105 | BITMAP MERGE | | 1 | | 107 |00:00:00.23 | 534 | 0 | 0 | 4000K| 512K| 4001K (0)| | 106 | BITMAP KEY ITERATION | | 1 | | 1000 |00:00:00.01 | 534 | 0 | 0 | | | | |*107 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | 0 | | | | |*108 | BITMAP INDEX RANGE SCAN | GG_F_POL_FACTS_FR55_OFF_IX05 | 12 | | 1000 |00:00:00.01 | 523 | 0 | 0 | | | | | 109 | BITMAP MERGE | | 1 | | 73 |00:00:00.24 | 504 | 0 | 0 | 1024K| 512K| | | 110 | BITMAP KEY ITERATION | | 1 | | 846 |00:00:00.01 | 504 | 0 | 0 | | | | |*111 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | 0 | | | | |*112 | BITMAP INDEX RANGE SCAN | GG_F_POL_FACTS_FR55_LOB_IX04 | 25 | | 846 |00:00:00.01 | 499 | 0 | 0 | | | | | 113 | BITMAP MERGE | | 1 | | 107 |00:00:02.88 | 33695 | 2230 | 0 | 1024K| 512K| | | 114 | BITMAP KEY ITERATION | | 1 | | 5128 |00:00:02.39 | 33695 | 2230 | 0 | | | | | 115 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6AEB_D9A59F06 | 1 | 7093 | 9542 |00:00:00.01 | 56 | 0 | 0 | | | | |*116 | BITMAP INDEX RANGE SCAN | GG_F_POL_FACTS_FR55_BRO_IX6 | 9542 | | 5128 |00:00:02.38 | 33639 | 2230 | 0 | | | | | 117 | TABLE ACCESS BY USER ROWID | GG_F_POLICY_FACTS_FR55 | 1466K| 1 | 1466K|00:03:54.31 | 734K| 603K| 0 | | | | |*118 | TABLE ACCESS FULL | COM_D_POLICY_FR55 | 1 | 2214K| 2214K|00:00:14.32 | 292K| 292K| 0 | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("T0"."C43" IS NOT NULL) 7 - access("List___Main_Numbers15"."Original_Currency"="T14"."Currency_Code" AND "List___Main_Numbers15"."Sub_LoB_Code_ID"="T14"."Sub_LoB_Code_ID" AND "List___Main_Numbers15"."Claim_Reference"="T14"."Claim_Reference" AND "List___Main_Numbers15"."Policy_Number"="T14"."Policy_Number") "List___Claims_Data10"."Policy_Number"="T11"."Policy_Number" AND "List___Claims_Data10"."Claim_Reference"="T11"."Claim_Reference" AND "List___Claims_Data10"."Original_Currency"="T11"."Original_Currency" AND "List___Claims_Data10"."Sub_LoB_Code_ID"="T11"."Sub_LoB_Code_ID") "COM_D_OFFICE"."SID_OFFICE"="GG_F_POLICY_FACTS_FR55"."SID_OFFICE") 16 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 17 - access("GG_D_LOB"."SID_LOB"="GG_F_POLICY_FACTS_FR55"."SID_LOB") 18 - filter("GG_D_LOB"."D_LOB_CODE"='06') 19 - access("COM_D_POLICY_FR55"."SID_POLICY"="GG_F_POLICY_FACTS_FR55"."SID_POLICY") 20 - access("COM_D_CLAIM_FR55"."SID_CLAIM"="GG_F_POLICY_FACTS_FR55"."SID_CLAIM") 21 - filter(ROUND("GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"/100,0)<2013) 26 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 27 - access("GG_F_POLICY_FACTS_FR55"."SID_OFFICE"="COM_D_OFFICE"."SID_OFFICE") 30 - filter("GG_D_LOB"."D_LOB_CODE"='06') 31 - access("GG_F_POLICY_FACTS_FR55"."SID_LOB"="GG_D_LOB"."SID_LOB") 32 - filter(("COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'17' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'54' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'14' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF" IS NOT NULL)) "COM_D_POLICY_FR55"."D_POL_INWARDS_OUTWARDS"='Inwards') 37 - access("T"."Policy_Number"="T9"."Policy_Number" AND "T"."Claim_Reference"="T9"."Claim_Reference" AND "T"."Original_Currency"="T9"."Original_Currency" AND "T"."Sub_LoB_Code_ID"="T9"."Sub_LoB_Code_ID") 41 - access("COM_D_POLICY_FR55"."SID_POLICY"="GG_F_POLICY_FACTS_FR55"."SID_POLICY") 42 - access("COM_D_CLAIM_FR55"."SID_CLAIM"="GG_F_POLICY_FACTS_FR55"."SID_CLAIM") 43 - access("COM_D_OFFICE"."SID_OFFICE"="GG_F_POLICY_FACTS_FR55"."SID_OFFICE") 44 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 45 - access("GG_D_LOB"."SID_LOB"="GG_F_POLICY_FACTS_FR55"."SID_LOB") 46 - filter("GG_D_LOB"."D_LOB_CODE"='06') 47 - filter(("GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"<=:REPPERIOD AND ROUND("GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"/100,0)=2013)) 52 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 53 - access("GG_F_POLICY_FACTS_FR55"."SID_OFFICE"="COM_D_OFFICE"."SID_OFFICE") 56 - filter("GG_D_LOB"."D_LOB_CODE"='06') 57 - access("GG_F_POLICY_FACTS_FR55"."SID_LOB"="GG_D_LOB"."SID_LOB") 58 - filter(("COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'17' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'54' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'14' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF" IS NOT NULL)) "COM_D_POLICY_FR55"."D_POL_INWARDS_OUTWARDS"='Inwards') 63 - access("COM_D_OFFICE"."SID_OFFICE"="GG_F_POLICY_FACTS_FR55"."SID_OFFICE") 64 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 65 - access("GG_D_LOB"."SID_LOB"="GG_F_POLICY_FACTS_FR55"."SID_LOB") 66 - filter("GG_D_LOB"."D_LOB_CODE"='06') 67 - access("COM_D_POLICY_FR55"."SID_POLICY"="GG_F_POLICY_FACTS_FR55"."SID_POLICY") 68 - access("COM_D_CLAIM_FR55"."SID_CLAIM"="GG_F_POLICY_FACTS_FR55"."SID_CLAIM") 69 - filter(("COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'17' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'54' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'14' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF" IS NOT NULL)) "GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"<=:REPPERIOD) 75 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 76 - access("GG_F_POLICY_FACTS_FR55"."SID_OFFICE"="COM_D_OFFICE"."SID_OFFICE") 79 - filter("GG_D_LOB"."D_LOB_CODE"='06') 80 - access("GG_F_POLICY_FACTS_FR55"."SID_LOB"="GG_D_LOB"."SID_LOB") 81 - filter("COM_D_POLICY_FR55"."D_POL_INWARDS_OUTWARDS"='Inwards') 85 - filter(("COM_D_PLACING_PARTNER"."D_PLACEMENTTYPEID"=1 OR "COM_D_PLACING_PARTNER"."D_PLACEMENTTYPEID"=10)) 88 - access("ORIGINAL_CURRENCY_VARIABLE"."D_PKSRCSYSTEMID"="ITEM_9" AND "ORIGINAL_CURRENCY_VARIABLE"."D_PKCOMPANYCODE"="ITEM_8" AND "ORIGINAL_CURRENCY_VARIABLE"."D_PKCCYCODE"="ITEM_7") 89 - filter("ORIGINAL_CURRENCY_VARIABLE"."D_RATEGROUPPERIOD"=:REPPERIOD) 90 - access("GG_D_LOB"."SID_LOB"="ITEM_1") 91 - filter("GG_D_LOB"."D_LOB_CODE"='06') 92 - access("COM_D_POLICY_FR55"."SID_POLICY"="ITEM_6") 93 - access("COM_D_INSURED"."SID_INSURED"="ITEM_4") 94 - filter("COM_D_INSURED"."D_PLACEMENTTYPEID"=7) 95 - access("COM_D_CLAIM_FR55"."SID_CLAIM"="ITEM_5") 96 - filter(("COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'17' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'54' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'14' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF" IS NOT NULL)) "C0"="ITEM_3") 99 - access("COM_D_OFFICE"."SID_OFFICE"="ITEM_2") 100 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 107 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 108 - access("GG_F_POLICY_FACTS_FR55"."SID_OFFICE"="COM_D_OFFICE"."SID_OFFICE") 111 - filter("GG_D_LOB"."D_LOB_CODE"='06') 112 - access("GG_F_POLICY_FACTS_FR55"."SID_LOB"="GG_D_LOB"."SID_LOB") 116 - access("GG_F_POLICY_FACTS_FR55"."SID_BROKER"="C0") 118 - filter("COM_D_POLICY_FR55"."D_POL_INWARDS_OUTWARDS"='Inwards') Note ----- - star transformation used for this statement 223 rows selected. ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.2'; Session altered. @"C:\Users\guptad\Desktop\ENH106755_SQL.sql" PL/SQL procedure successfully completed. COUNT(*) ---------- 1949 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST')); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------------------------------------- SQL_ID fgy0rt0vywdua, child number 3 ------------------------------------- SELECT COUNT(*) FROM ( WITH "T" AS (SELECT "T0"."C0" "Claim_Status_Number", "T0"."C1" "Inwards___Outwards_Policy", "T0"."C2" "Policy_Number", "T0"."C3" "Claim_Reference", "T0"."C4" "Company_Code", "T0"."C5" "Branch_Code", "T0"."C6" "LoB_Code", "T0"."C7" "Sub_LoB_Code_ID", "T0"."C8" "Business_Segment_Code", SUM("T0"."C10") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C8", "T0"."C3", "T0"."C4", "T0"."C5", "T0"."C6", "T0"."C7", "T0"."C9")/NULLIF(SUM("T0"."C11") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C8", "T0"."C3", "T0"."C4", "T0"."C5", "T0"."C6", "T0"."C7", "T0"."C9"), 0) "AGCS_Share__", "T0"."C9" "Original_Currency", SUM("T0"."C12") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C8", "T0"."C3", "T0"."C4", "T0"."C5", "T0"."C6", "T0"."C7", "T0"."C9") "c12", SUM("T0"."C13") over (partition BY "T0"."C0", "T0"."C1", "T0"."C2", "T0"."C8", "T0"."C3", "T0"."C4", "T0"."C5", "T0"."C6", "T0"."C7", "T0"."C9" Plan hash value: 3821067763 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:01:46.03 | 4423K| 4413K| | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:46.03 | 4423K| 4413K| | | | | 2 | VIEW | | 1 | 1 | 1949 |00:01:46.03 | 4423K| 4413K| | | | | 3 | HASH UNIQUE | | 1 | 1 | 1949 |00:01:46.03 | 4423K| 4413K| 2825K| 923K| 1370K (0)| | 4 | WINDOW SORT | | 1 | 1 | 2083 |00:01:46.01 | 4423K| 4413K| 903K| 523K| 802K (0)| |* 5 | VIEW | | 1 | 1 | 2083 |00:01:45.99 | 4423K| 4413K| | | | | 6 | WINDOW SORT | | 1 | 1 | 2083 |00:01:45.99 | 4423K| 4413K| 832K| 511K| 739K (0)| |* 7 | HASH JOIN OUTER | | 1 | 1 | 2083 |00:01:45.96 | 4423K| 4413K| 960K| 960K| 1332K (0)| | 8 | VIEW | | 1 | 1 | 2083 |00:01:17.03 | 3240K| 3231K| | | | | 9 | HASH GROUP BY | | 1 | 1 | 2083 |00:01:17.03 | 3240K| 3231K| 1941K| 941K| 2329K (0)| | 10 | VIEW | VW_FOJ_0 | 1 | 599K| 2209 |00:01:17.02 | 3240K| 3231K| | | | |* 11 | HASH JOIN FULL OUTER | | 1 | 599K| 2209 |00:01:17.02 | 3240K| 3231K| 973K| 973K| 2044K (0)| | 12 | VIEW | | 1 | 47029 | 1547 |00:00:38.23 | 1174K| 1174K| | | | | 13 | WINDOW BUFFER | | 1 | 47029 | 1547 |00:00:38.23 | 1174K| 1174K| 302K| 302K| 268K (0)| | 14 | SORT GROUP BY | | 1 | 47029 | 1547 |00:00:38.22 | 1174K| 1174K| 478K| 478K| 424K (0)| |* 15 | HASH JOIN | | 1 | 47029 | 9392 |00:00:38.18 | 1174K| 1174K| 1393K| 1393K| 1324K (0)| |* 16 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | | | | |* 17 | HASH JOIN | | 1 | 48176 | 9392 |00:00:38.17 | 1174K| 1174K| 1944K| 1152K| 3000K (0)| |* 18 | HASH JOIN | | 1 | 48176 | 9392 |00:00:32.95 | 881K| 881K| 5288K| 1660K| 6810K (0)| |* 19 | TABLE ACCESS FULL | COM_D_CLAIM_FR55 | 1 | 141K| 69000 |00:00:01.70 | 18394 | 18390 | | | | |* 20 | HASH JOIN | | 1 | 65021 | 1342K|00:00:30.49 | 863K| 863K| 1040K| 1040K| 1254K (0)| |* 21 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | | | | |* 22 | TABLE ACCESS FULL | GG_F_POLICY_FACTS_FR55 | 1 | 301K| 5214K|00:00:27.41 | 863K| 863K| | | | |* 23 | TABLE ACCESS FULL | COM_D_POLICY_FR55 | 1 | 2214K| 2214K|00:00:03.98 | 292K| 292K| | | | | 24 | VIEW | | 1 | 552K| 2083 |00:00:38.78 | 2066K| 2056K| | | | | 25 | HASH GROUP BY | | 1 | 552K| 2083 |00:00:38.78 | 2066K| 2056K| 1719K| 949K| 19M (0)| | 26 | VIEW | VW_FOJ_1 | 1 | 552K| 2092 |00:00:38.77 | 2066K| 2056K| | | | |* 27 | HASH JOIN FULL OUTER | | 1 | 552K| 2092 |00:00:38.77 | 2066K| 2056K| 841K| 841K| 1598K (0)| | 28 | VIEW | | 1 | 11657 | 1063 |00:00:16.85 | 892K| 882K| | | | | 29 | WINDOW BUFFER | | 1 | 11657 | 1063 |00:00:16.85 | 892K| 882K| 267K| 267K| 237K (0)| | 30 | SORT GROUP BY | | 1 | 11657 | 1063 |00:00:16.84 | 892K| 882K| 372K| 372K| 330K (0)| | 31 | NESTED LOOPS | | 1 | 11657 | 3902 |00:00:16.80 | 892K| 882K| | | | | 32 | NESTED LOOPS | | 1 | 11657 | 3902 |00:00:16.53 | 888K| 882K| | | | |* 33 | HASH JOIN | | 1 | 11657 | 3902 |00:00:14.90 | 881K| 881K| 10M| 2331K| 12M (0)| |* 34 | HASH JOIN | | 1 | 11657 | 73858 |00:00:14.45 | 863K| 863K| 1393K| 1393K| 1343K (0)| |* 35 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | | | | |* 36 | HASH JOIN | | 1 | 11941 | 73858 |00:00:14.38 | 863K| 863K| 1483K| 1483K| 1597K (0)| |* 37 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | | | | |* 38 | TABLE ACCESS FULL | GG_F_POLICY_FACTS_FR55 | 1 | 55441 | 479K|00:00:14.07 | 863K| 863K| | | | |* 39 | TABLE ACCESS FULL | COM_D_CLAIM_FR55 | 1 | 141K| 69000 |00:00:00.31 | 18393 | 18390 | | | | |* 40 | INDEX UNIQUE SCAN | COM_D_POLICY_FR55_UK | 3902 | 1 | 3902 |00:00:01.63 | 6756 | 475 | | | | |* 41 | TABLE ACCESS BY INDEX ROWID| COM_D_POLICY_FR55 | 3902 | 1 | 3902 |00:00:00.27 | 3902 | 641 | | | | | 42 | VIEW | | 1 | 552K| 2083 |00:00:21.91 | 1174K| 1174K| | | | | 43 | WINDOW BUFFER | | 1 | 552K| 2083 |00:00:21.91 | 1174K| 1174K| 337K| 337K| 299K (0)| | 44 | SORT GROUP BY | | 1 | 552K| 2083 |00:00:21.90 | 1174K| 1174K| 478K| 478K| 424K (0)| |* 45 | HASH JOIN | | 1 | 552K| 13294 |00:00:21.85 | 1174K| 1174K| 1393K| 1393K| 1320K (0)| |* 46 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | | | | |* 47 | HASH JOIN | | 1 | 566K| 13294 |00:00:21.83 | 1174K| 1174K| 2098K| 1244K| 11M (0)| |* 48 | HASH JOIN | | 1 | 575K| 13294 |00:00:17.51 | 881K| 881K| 5256K| 1683K| 6816K (0)| |* 49 | TABLE ACCESS FULL | COM_D_CLAIM_FR55 | 1 | 141K| 69000 |00:00:00.37 | 18393 | 18390 | | | | |* 50 | HASH JOIN | | 1 | 1194K| 1416K|00:00:16.35 | 863K| 863K| 1519K| 1519K| 1573K (0)| |* 51 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | | | | |* 52 | TABLE ACCESS FULL | GG_F_POLICY_FACTS_FR55 | 1 | 5544K| 5694K|00:00:13.04 | 863K| 863K| | | | |* 53 | TABLE ACCESS FULL | COM_D_POLICY_FR55 | 1 | 2214K| 2214K|00:00:03.02 | 292K| 292K| | | | | 54 | VIEW | | 1 | 673K| 2633 |00:00:28.92 | 1182K| 1182K| | | | | 55 | HASH UNIQUE | | 1 | 673K| 2633 |00:00:28.92 | 1182K| 1182K| 7453K| 1941K| 30M (0)| | 56 | WINDOW SORT | | 1 | 673K| 16599 |00:00:28.86 | 1182K| 1182K| 9830K| 1213K| 8737K (0)| |* 57 | HASH JOIN | | 1 | 673K| 16599 |00:00:28.52 | 1182K| 1182K| 1185K| 1185K| 1294K (0)| |* 58 | TABLE ACCESS FULL | GG_D_CURRENCY | 1 | 578 | 1005 |00:00:00.29 | 1347 | 1344 | | | | |* 59 | HASH JOIN | | 1 | 601K| 16736 |00:00:28.21 | 1180K| 1180K| 1540K| 1449K| 1652K (0)| | 60 | VIEW | | 1 | 7093 | 9542 |00:00:00.13 | 482 | 480 | | | | |* 61 | TABLE ACCESS FULL | COM_D_PLACING_PARTNER | 1 | 7093 | 9542 |00:00:00.13 | 482 | 480 | | | | |* 62 | HASH JOIN | | 1 | 601K| 16736 |00:00:28.05 | 1180K| 1180K| 6256K| 1983K| 27M (0)| |* 63 | HASH JOIN | | 1 | 611K| 16736 |00:00:22.73 | 887K| 887K| 11M| 3235K| 12M (0)| | 64 | VIEW | | 1 | 138K| 163K|00:00:01.08 | 6209 | 6202 | | | | |* 65 | TABLE ACCESS FULL | COM_D_INSURED | 1 | 138K| 163K|00:00:01.03 | 6209 | 6202 | | | | |* 66 | HASH JOIN | | 1 | 611K| 16736 |00:00:21.50 | 881K| 881K| 7901K| 2581K| 7822K (0)| |* 67 | TABLE ACCESS FULL | COM_D_CLAIM_FR55 | 1 | 141K| 69000 |00:00:00.36 | 18393 | 18390 | | | | |* 68 | HASH JOIN | | 1 | 1269K| 1466K|00:00:20.37 | 863K| 863K| 982K| 982K| 1254K (0)| |* 69 | TABLE ACCESS FULL | GG_D_LOB | 1 | 22 | 25 |00:00:00.01 | 5 | 0 | | | | |* 70 | HASH JOIN | | 1 | 5893K| 6037K|00:00:17.05 | 863K| 863K| 960K| 960K| 1020K (0)| |* 71 | TABLE ACCESS FULL | COM_D_OFFICE | 1 | 14 | 12 |00:00:00.01 | 11 | 0 | | | | | 72 | TABLE ACCESS FULL | GG_F_POLICY_FACTS_FR55 | 1 | 6037K| 6037K|00:00:12.17 | 863K| 863K| | | | |* 73 | TABLE ACCESS FULL | COM_D_POLICY_FR55 | 1 | 2214K| 2214K|00:00:03.85 | 292K| 292K| | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter("T0"."C43" IS NOT NULL) 7 - access("List___Main_Numbers15"."Original_Currency"="T14"."Currency_Code" AND "List___Main_Numbers15"."Sub_LoB_Code_ID"="T14"."Sub_LoB_Code_ID" AND "List___Main_Numbers15"."Claim_Reference"="T14"."Claim_Reference" AND "List___Main_Numbers15"."Policy_Number"="T14"."Policy_Number") "List___Claims_Data10"."Policy_Number"="T11"."Policy_Number" AND "List___Claims_Data10"."Claim_Reference"="T11"."Claim_Reference" AND "List___Claims_Data10"."Original_Currency"="T11"."Original_Currency" AND "List___Claims_Data10"."Sub_LoB_Code_ID"="T11"."Sub_LoB_Code_ID") "COM_D_OFFICE"."SID_OFFICE"="GG_F_POLICY_FACTS_FR55"."SID_OFFICE") 16 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 17 - access("COM_D_POLICY_FR55"."SID_POLICY"="GG_F_POLICY_FACTS_FR55"."SID_POLICY") 18 - access("COM_D_CLAIM_FR55"."SID_CLAIM"="GG_F_POLICY_FACTS_FR55"."SID_CLAIM") 19 - filter(("COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'17' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'54' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'14' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF" IS NOT NULL)) "GG_D_LOB"."SID_LOB"="GG_F_POLICY_FACTS_FR55"."SID_LOB") 21 - filter("GG_D_LOB"."D_LOB_CODE"='06') 22 - filter(ROUND("GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"/100,0)<2013) 23 - filter("COM_D_POLICY_FR55"."D_POL_INWARDS_OUTWARDS"='Inwards') 27 - access("T"."Policy_Number"="T9"."Policy_Number" AND "T"."Claim_Reference"="T9"."Claim_Reference" AND "T"."Original_Currency"="T9"."Original_Currency" AND "T"."Sub_LoB_Code_ID"="T9"."Sub_LoB_Code_ID") 33 - access("COM_D_CLAIM_FR55"."SID_CLAIM"="GG_F_POLICY_FACTS_FR55"."SID_CLAIM") 34 - access("COM_D_OFFICE"."SID_OFFICE"="GG_F_POLICY_FACTS_FR55"."SID_OFFICE") 35 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 36 - access("GG_D_LOB"."SID_LOB"="GG_F_POLICY_FACTS_FR55"."SID_LOB") 37 - filter("GG_D_LOB"."D_LOB_CODE"='06') 38 - filter(("GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"<=:REPPERIOD AND ROUND("GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"/100,0)=2013)) 39 - filter(("COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'17' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'54' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'14' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF" IS NOT NULL)) "COM_D_POLICY_FR55"."SID_POLICY"="GG_F_POLICY_FACTS_FR55"."SID_POLICY") 41 - filter("COM_D_POLICY_FR55"."D_POL_INWARDS_OUTWARDS"='Inwards') 45 - access("COM_D_OFFICE"."SID_OFFICE"="GG_F_POLICY_FACTS_FR55"."SID_OFFICE") 46 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 47 - access("COM_D_POLICY_FR55"."SID_POLICY"="GG_F_POLICY_FACTS_FR55"."SID_POLICY") 48 - access("COM_D_CLAIM_FR55"."SID_CLAIM"="GG_F_POLICY_FACTS_FR55"."SID_CLAIM") 49 - filter(("COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'17' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'54' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'14' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF" IS NOT NULL)) "GG_D_LOB"."SID_LOB"="GG_F_POLICY_FACTS_FR55"."SID_LOB") 51 - filter("GG_D_LOB"."D_LOB_CODE"='06') 52 - filter("GG_F_POLICY_FACTS_FR55"."F_REPORTINGPERIOD"<=:REPPERIOD) 53 - filter("COM_D_POLICY_FR55"."D_POL_INWARDS_OUTWARDS"='Inwards') 57 - access("ORIGINAL_CURRENCY_VARIABLE"."D_PKSRCSYSTEMID"="GG_F_POLICY_FACTS_FR55"."F_POL_SRCSYSTEMID" AND "ORIGINAL_CURRENCY_VARIABLE"."D_PKCOMPANYCODE"="GG_F_POLICY_FACTS_FR55"."F_POL_COMPANYCODE" AND "ORIGINAL_CURRENCY_VARIABLE"."D_PKCCYCODE"="GG_F_POLICY_FACTS_FR55"."F_ORIGINAL_CURRENCY") 58 - filter("ORIGINAL_CURRENCY_VARIABLE"."D_RATEGROUPPERIOD"=:REPPERIOD) 59 - access("BROKER"."SID_PLACING_PARTNER"="GG_F_POLICY_FACTS_FR55"."SID_BROKER") 61 - filter(("COM_D_PLACING_PARTNER"."D_PLACEMENTTYPEID"=1 OR "COM_D_PLACING_PARTNER"."D_PLACEMENTTYPEID"=10)) 62 - access("COM_D_POLICY_FR55"."SID_POLICY"="GG_F_POLICY_FACTS_FR55"."SID_POLICY") 63 - access("INSURED"."SID_INSURED"="GG_F_POLICY_FACTS_FR55"."SID_INSURED") 65 - filter("COM_D_INSURED"."D_PLACEMENTTYPEID"=7) 66 - access("COM_D_CLAIM_FR55"."SID_CLAIM"="GG_F_POLICY_FACTS_FR55"."SID_CLAIM") 67 - filter(("COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'17' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'54' AND "COM_D_CLAIM_FR55"."D_CH_CLMSTATUS"<>'14' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF"<>'N/A' AND "COM_D_CLAIM_FR55"."D_CH_CLMREF" IS NOT NULL)) "GG_D_LOB"."SID_LOB"="GG_F_POLICY_FACTS_FR55"."SID_LOB") 69 - filter("GG_D_LOB"."D_LOB_CODE"='06') 70 - access("COM_D_OFFICE"."SID_OFFICE"="GG_F_POLICY_FACTS_FR55"."SID_OFFICE") 71 - filter("COM_D_OFFICE"."D_BRANCHCODE"='FR55') 73 - filter("COM_D_POLICY_FR55"."D_POL_INWARDS_OUTWARDS"='Inwards') 158 rows selected.