Explain Plan: PLAN_TABLE_OUTPUT Plan hash value: 3849700924 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 821 | 582 (2)| 00:00:01 | | | | | | |* 1 | TABLE ACCESS BY INDEX ROWID | BEN_PLAN_TYPE_D | 1 | 53 | 2 (0)| 00:00:01 | | | | | | |* 2 | INDEX RANGE SCAN | BEN_PLAN_TYPE_D_N03 | 1 | | 1 (0)| 00:00:01 | | | | | | | 3 | PX COORDINATOR | | | | | | | | | | | | 4 | PX SEND QC (ORDER) | :TQ10002 | 1 | 821 | 582 (2)| 00:00:01 | | | Q1,02 | P->S | QC (ORDER) | | 5 | SORT ORDER BY | | 1 | 821 | 582 (2)| 00:00:01 | | | Q1,02 | PCWP | | | 6 | PX RECEIVE | | | | | | | | Q1,02 | PCWP | | | 7 | PX SEND RANGE | :TQ10001 | | | | | | | Q1,01 | P->P | RANGE | | 8 | NESTED LOOPS | | | | | | | | Q1,01 | PCWP | | | 9 | NESTED LOOPS | | 1 | 821 | 581 (2)| 00:00:01 | | | Q1,01 | PCWP | | | 10 | NESTED LOOPS | | 1 | 763 | 581 (2)| 00:00:01 | | | Q1,01 | PCWP | | | 11 | NESTED LOOPS | | 1 | 722 | 580 (2)| 00:00:01 | | | Q1,01 | PCWP | | | 12 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | | | 13 | PX RECEIVE | | | | | | | | Q1,01 | PCWP | | | 14 | PX SEND BROADCAST | :TQ10000 | | | | | | | | S->P | BROADCAST | |* 15 | HASH JOIN | | 1 | 694 | 579 (2)| 00:00:01 | | | | | | | 16 | VIEW | | 8 | 664 | 347 (1)| 00:00:01 | | | | | | | 17 | HASH GROUP BY | | 8 | 944 | 347 (1)| 00:00:01 | | | | | | | 18 | NESTED LOOPS | | | | | | | | | | | | 19 | NESTED LOOPS | | 8 | 944 | 346 (1)| 00:00:01 | | | | | | | 20 | NESTED LOOPS | | 8 | 816 | 345 (1)| 00:00:01 | | | | | | | 21 | NESTED LOOPS | | 148 | 7992 | 176 (1)| 00:00:01 | | | | | | | 22 | MERGE JOIN CARTESIAN | | 1 | 41 | 173 (1)| 00:00:01 | | | | | | | 23 | VIEW | | 1 | 36 | 169 (1)| 00:00:01 | | | | | | |* 24 | COUNT STOPKEY | | | | | | | | | | | |* 25 | TABLE ACCESS BY INDEX ROWID | PAY_REGISTER_F | 1 | 25 | 169 (1)| 00:00:01 | | | | | | |* 26 | INDEX RANGE SCAN | PAY_REGISTER_F_N04 | 268 | | 4 (0)| 00:00:01 | | | | | | | 27 | BUFFER SORT | | 1 | 5 | 173 (1)| 00:00:01 | | | | | | | 28 | TABLE ACCESS BY INDEX ROWID | PAY_DEDUCTION_CLASS_D | 1 | 5 | 3 (0)| 00:00:01 | | | | | | |* 29 | INDEX RANGE SCAN | PAY_DEDUCTION_CLASS_D_N01 | 1 | | 2 (0)| 00:00:01 | | | | | | | 30 | TABLE ACCESS BY INDEX ROWID | SYS_CALENDAR_D | 120 | 1560 | 3 (0)| 00:00:01 | | | | | | |* 31 | INDEX RANGE SCAN | SYS_CALENDAR_DIM_N01 | 215 | | 0 (0)| 00:00:01 | | | | | | |* 32 | TABLE ACCESS BY INDEX ROWID | PAY_REGISTER_F | 1 | 48 | 4 (0)| 00:00:01 | | | | | | |* 33 | INDEX RANGE SCAN | PAY_REGISTER_F_N05 | 1 | | 3 (0)| 00:00:01 | | | | | | |* 34 | INDEX RANGE SCAN | PAY_PAYROLL_REGISTER_D_PK | 1 | | 0 (0)| 00:00:01 | | | | | | | 35 | TABLE ACCESS BY INDEX ROWID | PAY_PAYROLL_REGISTER_D | 1 | 16 | 0 (0)| 00:00:01 | | | | | | | 36 | VIEW | | 47 | 28717 | 231 (2)| 00:00:01 | | | | | | | 37 | WINDOW NOSORT | | 47 | 9635 | 231 (2)| 00:00:01 | | | | | | | 38 | SORT GROUP BY | | 47 | 9635 | 231 (2)| 00:00:01 | | | | | | | 39 | NESTED LOOPS | | | | | | | | | | | | 40 | NESTED LOOPS | | 47 | 9635 | 230 (1)| 00:00:01 | | | | | | | 41 | NESTED LOOPS | | 47 | 8554 | 220 (1)| 00:00:01 | | | | | | | 42 | NESTED LOOPS | | 47 | 6862 | 209 (1)| 00:00:01 | | | | | | |* 43 | HASH JOIN | | 47 | 5546 | 199 (2)| 00:00:01 | | | | | | | 44 | NESTED LOOPS | | | | | | | | | | | | 45 | NESTED LOOPS | | 47 | 5311 | 192 (1)| 00:00:01 | | | | | | | 46 | NESTED LOOPS | | 47 | 3901 | 171 (1)| 00:00:01 | | | | | | | 47 | TABLE ACCESS BY INDEX ROWID| PAY_DEDUCTION_CLASS_D | 1 | 5 | 3 (0)| 00:00:01 | | | | | | |* 48 | INDEX RANGE SCAN | PAY_DEDUCTION_CLASS_D_N01 | 1 | | 2 (0)| 00:00:01 | | | | | | |* 49 | TABLE ACCESS BY INDEX ROWID| PAY_REGISTER_F | 38 | 2964 | 168 (1)| 00:00:01 | | | | | | |* 50 | INDEX RANGE SCAN | PAY_REGISTER_F_N04 | 268 | | 3 (0)| 00:00:01 | | | | | | |* 51 | INDEX RANGE SCAN | HCM_COMPANY_D_PK | 1 | | 0 (0)| 00:00:01 | | | | | | | 52 | TABLE ACCESS BY INDEX ROWID | HCM_COMPANY_D | 1 | 30 | 0 (0)| 00:00:01 | | | | | | | 53 | VIEW | index$_join$_023 | 7 | 35 | 7 (15)| 00:00:01 | | | | | | |* 54 | HASH JOIN | | | | | | | | | | | | 55 | INDEX FAST FULL SCAN | PAY_DEDUCTION_CLASS_D_N01 | 7 | 35 | 5 (0)| 00:00:01 | | | | | | | 56 | INDEX FAST FULL SCAN | PAY_DEDUCTION_CLASS_D_PK | 7 | 35 | 3 (0)| 00:00:01 | | | | | | | 57 | TABLE ACCESS BY INDEX ROWID | PAY_PAYGROUP_D | 1 | 28 | 0 (0)| 00:00:01 | | | | | | |* 58 | INDEX RANGE SCAN | PAY_PAYGROUP_D_PK | 1 | | 0 (0)| 00:00:01 | | | | | | | 59 | TABLE ACCESS BY INDEX ROWID | PAY_PAYROLL_REGISTER_D | 1 | 36 | 0 (0)| 00:00:01 | | | | | | |* 60 | INDEX RANGE SCAN | PAY_PAYROLL_REGISTER_D_PK | 1 | | 0 (0)| 00:00:01 | | | | | | | 61 | PARTITION HASH ITERATOR | | 1 | | 0 (0)| 00:00:01 | KEY | KEY | | | | |* 62 | INDEX UNIQUE SCAN | HCM_PERSON_D_PK | 1 | | 0 (0)| 00:00:01 | KEY | KEY | | | | | 63 | TABLE ACCESS BY GLOBAL INDEX ROWID| HCM_PERSON_D | 1 | 23 | 0 (0)| 00:00:01 | ROWID | ROWID | | | | | 64 | INLIST ITERATOR | | | | | | | | Q1,01 | PCWC | | | 65 | PX PARTITION HASH ITERATOR | | 1 | 28 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) | Q1,01 | PCWC | | |* 66 | INDEX RANGE SCAN | T2_DW_ADMIN_STDRPT_ACCESS_N1 | 1 | 28 | 1 (0)| 00:00:01 |KEY(I) |KEY(I) | Q1,01 | PCWP | | |* 67 | TABLE ACCESS BY INDEX ROWID | PAY_PAYROLL_REGISTER_D | 1 | 41 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | | |* 68 | INDEX RANGE SCAN | PAY_PAYROLL_REGISTER_D_N04 | 1 | | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | | | 69 | PARTITION HASH ITERATOR | | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | | |* 70 | INDEX RANGE SCAN | HCM_EMPLOYEE_D_N03 | 1 | | 0 (0)| 00:00:01 | KEY | KEY | Q1,01 | PCWP | | |* 71 | TABLE ACCESS BY GLOBAL INDEX ROWID | HCM_EMPLOYEE_D | 1 | 58 | 0 (0)| 00:00:01 | ROWID | ROWID | Q1,01 | PCWP | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("PLAN_TYPE_D"."EFF_START_DT"<=:B1 AND "PLAN_TYPE_D"."VALID_END_DT">=SYSDATE@! AND "PLAN_TYPE_D"."VALID_START_DT"<=SYSDATE@!) 2 - access("PLAN_TYPE_D"."BEN_PLAN_TYPE_CD"=:B1 AND "PLAN_TYPE_D"."EFF_END_DT">=:B2) 15 - access("AGG_DATA"."INVOICE"="LIM"."INVOICE_NUMBER" AND "AGG_DATA"."EMPLID"="LIM"."EMPLID" AND "AGG_DATA"."EMPL_RCD"="LIM"."EMPL_RCD" AND "AGG_DATA"."CODE"="LIM"."CODE" AND "AGG_DATA"."DEDUCTION_CLASS"="LIM"."DEDUCTION_CLASS") 24 - filter(ROWNUM=1) 25 - filter("COMPANY"='12C') 26 - access("INVOICE_NUMBER"=1941765 AND "VALID_FLAG"='Y') 29 - access("DED_D"."PAY_DED_CLASS_CD">'K' AND "DED_D"."PAY_DED_CLASS_CD" IS NOT NULL) 31 - access("CK_DT_D"."CALENDAR_DT">=INTERNAL_FUNCTION("INV_DT_D"."START_DT") AND "CK_DT_D"."CALENDAR_DT"<=INTERNAL_FUNCTION("INV_DT_D"."END_DT")) 32 - filter("PRF"."INVOICE_NUMBER"<=1941765) 33 - access("PRF"."COMPANY"="INV_DT_D"."COMPANY" AND "PRF"."VALID_FLAG"='Y' AND "PRF"."PAY_DEDUCTION_CLASS_D_SID"="DED_D"."PAY_DEDUCTION_CLASS_D_SID" AND "PRF"."CHECK_DT_D_SID"="CK_DT_D"."CALENDAR_D_SID") 34 - access("PRF"."PAY_PAYROLL_REGISTER_D_SID"="REG_D"."PAY_PAYROLL_REGISTER_D_SID") 43 - access("PRF2"."PAY_DEDUCTION_CLASS_D_SID"="DC_D"."PAY_DEDUCTION_CLASS_D_SID") 48 - access("DED_D"."PAY_DED_CLASS_CD">'K' AND "DED_D"."PAY_DED_CLASS_CD" IS NOT NULL) 49 - filter("PRF2"."PAY_DEDUCTION_CLASS_D_SID"="DED_D"."PAY_DEDUCTION_CLASS_D_SID") 50 - access("PRF2"."INVOICE_NUMBER"=1941765 AND "PRF2"."VALID_FLAG"='Y') 51 - access("PRF2"."HCM_COMPANY_D_SID"="CO_D"."HCM_COMPANY_D_SID") 54 - access(ROWID=ROWID) 58 - access("PRF2"."PAY_PAYGROUP_D_SID"="PG_D"."PAY_PAYGROUP_D_SID") 60 - access("PRF2"."PAY_PAYROLL_REGISTER_D_SID"="PPRD"."PAY_PAYROLL_REGISTER_D_SID") 62 - access("PRF2"."HCM_PERSON_D_SID"="PER_D"."HCM_PERSON_D_SID") 66 - access("ACC"."COMPANY_ID"="AGG_DATA"."COMPANY" AND "ACC"."LOGIN_USER"='1357626' AND ("ACC"."REPORT_ID"='C10BENREG' OR "ACC"."REPORT_ID"='C10BENREGCAN' OR "ACC"."REPORT_ID"='C10BENREGSB')) 67 - filter("PAY_REG_D"."VALID_START_DT"<=SYSDATE@! AND "PAY_REG_D"."VALID_END_DT">=SYSDATE@!) 68 - access("AGG_DATA"."CODE"="PAY_REG_D"."CODE" AND "AGG_DATA"."INV_DT"<="PAY_REG_D"."EFF_END_DT" AND "AGG_DATA"."INV_DT">="PAY_REG_D"."EFF_START_DT") filter("AGG_DATA"."INV_DT"<="PAY_REG_D"."EFF_END_DT") 70 - access("EE_D"."EMPLID"="AGG_DATA"."EMPLID" AND "EE_D"."EMPL_RCD"="AGG_DATA"."EMPL_RCD" AND "AGG_DATA"."INV_DT"<="EE_D"."EFF_END_DT" AND "AGG_DATA"."INV_DT">="EE_D"."EFF_START_DT") filter("AGG_DATA"."INV_DT">="EE_D"."EFF_START_DT" AND "AGG_DATA"."INV_DT"<="EE_D"."EFF_END_DT") 71 - filter("EE_D"."VALID_FLAG"='Y' AND "EE_D"."COMPANY_CD"="LIM"."COMPANY_CD" AND "DW"."CHECK_EE_REPORT_ACCESS"("AGG_DATA"."COMPANY",'1357626',"ACC"."REPORT_ID","EE_D"."EMPLID","EE_D"."DEPTID","EE_D"."LOCATION_ID")=1) Note ----- - dynamic sampling used for this statement (level=7) TKPROF: Release 11.2.0.3.0 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Sort options: default ******************************************************************************** WITH "Rpt" AS ( SELECT LIM.COMPANY_CD, LIM.COMPANY_NAME, LIM.PAY_END_DT, LIM.PAYGROUP, LIM.PAYGROUP_DESCR_LONG, LIM.INVOICE_NUMBER, LIM.DEDUCTION_CODE, LIM.DESCR_LONG, LIM.DEDUCTION_CLASS, LIM.PLAN_TYPE, (SELECT PLAN_TYPE_D.BEN_PLAN_TYPE_DESCR_LONG FROM DW.BEN_PLAN_TYPE_D PLAN_TYPE_D WHERE PLAN_TYPE_D.BEN_PLAN_TYPE_CD = LIM.PLAN_TYPE AND LIM.INVOICE_DT BETWEEN PLAN_TYPE_D.EFF_START_DT AND PLAN_TYPE_D.EFF_END_DT AND SYSDATE BETWEEN PLAN_TYPE_D.VALID_START_DT AND PLAN_TYPE_D.VALID_END_DT) DEDUCTION_DESCRIPTION, LIM.SEPCHK, LIM.EMPLID, LIM.NAME EE_NAME, LIM.TAXABLE_AMOUNT, LIM.NON_TAXABLE_AMOUNT, LIM.CALCULATED_BASE, (CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.MSUM ELSE 0 END) MSUM, (CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.QSUM ELSE 0 END) QSUM, (CASE WHEN LIM.ROW_NUMBER = 1 THEN AGG_DATA.YSUM ELSE 0 END) YSUM FROM ( SELECT COMPANY, EMPLID, EMPL_RCD, CODE, DEDUCTION_CLASS, INVOICE, INV_DT, SUM ( CASE WHEN MNTH_DT = CHK_MNTH_DT THEN AMOUNT ELSE 0 END) MSUM, SUM ( CASE WHEN MNTH_DT = CHK_MNTH_DT THEN HOURS ELSE 0 END) MHRS, SUM ( CASE WHEN QTR_DT = CHK_QTR_DT THEN AMOUNT ELSE 0 END) QSUM, SUM ( CASE WHEN QTR_DT = CHK_QTR_DT THEN HOURS ELSE 0 END) QHRS, SUM (AMOUNT) YSUM, SUM (NVL (HOURS, 0)) YHRS FROM (SELECT PRF.COMPANY, PRF.PAYGROUP, PRF.PAY_END_DT PAY_END_DT, PRF.EMPLID, PRF.EMPL_RCD, INV_DT_D.INVOICE_DT INV_DT, INV_DT_D.CHECK_DT CHK_DT, REG_D.CODE, PRF.CODE_TYPE, DED_D.PAY_DED_CLASS_CD DEDUCTION_CLASS, PRF.AMOUNT, PRF.HOURS, INV_DT_D.INVOICE, PRF.VALID_FLAG, TRUNC (INV_DT_D.CHECK_DT, 'MM') MNTH_DT, TRUNC (INV_DT_D.CHECK_DT, 'Q') QTR_DT, TRUNC (INV_DT_D.CHECK_DT, 'YEAR') YEAR_DT, TRUNC (CK_DT_D.CALENDAR_DT, 'MM') CHK_MNTH_DT, TRUNC (CK_DT_D.CALENDAR_DT, 'Q') CHK_QTR_DT, TRUNC (CK_DT_D.CALENDAR_DT, 'YEAR') CHK_YEAR_DT FROM DW.PAY_REGISTER_F PRF, DW.PAY_PAYROLL_REGISTER_D REG_D, DW.PAY_DEDUCTION_CLASS_D DED_D, DW.SYS_CALENDAR_D CK_DT_D, (SELECT COMPANY, INVOICE_NUMBER INVOICE, INVOICE_DT, ( PRF.CHECK_DT_D_SID + TO_DATE ('1/1/1900', 'MM/DD/YYYY')) AS CHECK_DT, TRUNC ( ( PRF.CHECK_DT_D_SID + TO_DATE ('1/1/1900', 'MM/DD/YYYY')), 'YYYY') AS START_DT, ( PRF.CHECK_DT_D_SID + TO_DATE ('1/1/1900', 'MM/DD/YYYY')) AS END_DT FROM DW.PAY_REGISTER_F PRF WHERE COMPANY = '12C' AND INVOICE_NUMBER = '1941765' AND VALID_FLAG = 'Y' AND ROWNUM = 1) INV_DT_D WHERE PRF.COMPANY = INV_DT_D.COMPANY AND '1941765' >= PRF.INVOICE_NUMBER AND PRF.PAY_PAYROLL_REGISTER_D_SID = REG_D.PAY_PAYROLL_REGISTER_D_SID AND PRF.PAY_DEDUCTION_CLASS_D_SID = DED_D.PAY_DEDUCTION_CLASS_D_SID AND PRF.CHECK_DT_D_SID = CK_DT_D.CALENDAR_D_SID AND CK_DT_D.CALENDAR_DT >= INV_DT_D.START_DT AND CK_DT_D.CALENDAR_DT <= INV_DT_D.END_DT AND (DED_D.PAY_DED_CLASS_CD > 'K')) YTD_DEDS WHERE YTD_DEDS.VALID_FLAG = 'Y' GROUP BY COMPANY, EMPLID, EMPL_RCD, CODE, DEDUCTION_CLASS, INVOICE, INV_DT) AGG_DATA, DW.HCM_EMPLOYEE_D EE_D, DW.PAY_PAYROLL_REGISTER_D PAY_REG_D, DW.T2_DW_ADMIN_STDRPT_ACCESS ACC, ( SELECT CO_D.COMPANY_CD, CO_D.COMPANY_NAME, PRF2.INVOICE_NUMBER, PRF2.EMPLID, PRF2.EMPL_RCD, PRF2.SEPCHK, PER_D.NAME, SUBSTR (PPRD.DISPLAY_CODE, 1, 2) PLAN_TYPE, SUBSTR (PPRD.DISPLAY_CODE, 4, 10) DEDUCTION_CODE, PPRD.DISPLAY_CODE CODE, PPRD.DESCR_LONG DESCR_LONG, DC_D.PAY_DED_CLASS_CD DEDUCTION_CLASS, PRF2.PAYGROUP, PG_D.PAYGROUP_DESCR_LONG, PRF2.PAY_END_DT, PRF2.INVOICE_DT, PRF2.CALCULATED_BASE, ROW_NUMBER () OVER ( PARTITION BY PRF2.EMPLID, SUBSTR (PPRD.DISPLAY_CODE, 4, 10), DC_D.PAY_DED_CLASS_CD ORDER BY PRF2.EMPLID, SUBSTR (PPRD.DISPLAY_CODE, 4, 10), PRF2.SEPCHK NULLS LAST) ROW_NUMBER, SUM ( CASE WHEN DC_D.PAY_DED_CLASS_CD = 'T' THEN PRF2.AMOUNT ELSE 0 END) TAXABLE_AMOUNT, SUM ( CASE WHEN DC_D.PAY_DED_CLASS_CD IN ('N', 'P') THEN PRF2.AMOUNT ELSE 0 END) NON_TAXABLE_AMOUNT, SUM (PRF2.AMOUNT) AMOUNT FROM DW.PAY_REGISTER_F PRF2 INNER JOIN DW.PAY_PAYROLL_REGISTER_D PPRD ON PRF2.PAY_PAYROLL_REGISTER_D_SID = PPRD.PAY_PAYROLL_REGISTER_D_SID INNER JOIN DW.HCM_COMPANY_D CO_D ON PRF2.HCM_COMPANY_D_SID = CO_D.HCM_COMPANY_D_SID INNER JOIN DW.PAY_PAYGROUP_D PG_D ON PRF2.PAY_PAYGROUP_D_SID = PG_D.PAY_PAYGROUP_D_SID INNER JOIN DW.HCM_PERSON_D PER_D ON PRF2.HCM_PERSON_D_SID = PER_D.HCM_PERSON_D_SID INNER JOIN DW.PAY_DEDUCTION_CLASS_D DC_D ON PRF2.PAY_DEDUCTION_CLASS_D_SID = DC_D.PAY_DEDUCTION_CLASS_D_SID INNER JOIN DW.PAY_DEDUCTION_CLASS_D ded_d ON PRF2.PAY_DEDUCTION_CLASS_D_SID = DED_D.PAY_DEDUCTION_CLASS_D_SID WHERE INVOICE_NUMBER = '1941765' AND PRF2.VALID_FLAG = 'Y' AND (DED_D.PAY_DED_CLASS_CD > 'K') GROUP BY CO_D.COMPANY_CD, PRF2.INVOICE_NUMBER, CO_D.COMPANY_NAME, PRF2.EMPLID, PRF2.EMPL_RCD, PRF2.SEPCHK, PER_D.NAME, SUBSTR (PPRD.DISPLAY_CODE, 1, 2), SUBSTR (PPRD.DISPLAY_CODE, 4, 10), PPRD.DISPLAY_CODE, PPRD.DESCR_LONG, DC_D.PAY_DED_CLASS_CD, PRF2.PAYGROUP, PG_D.PAYGROUP_DESCR_LONG, PRF2.PAY_END_DT, PRF2.INVOICE_DT, PRF2.CALCULATED_BASE) LIM WHERE EE_D.EMPLID = AGG_DATA.EMPLID AND EE_D.EMPL_RCD = AGG_DATA.EMPL_RCD AND EE_D.VALID_FLAG = 'Y' AND EE_D.COMPANY_CD = LIM.COMPANY_CD AND AGG_DATA.INV_DT BETWEEN EE_D.EFF_START_DT AND EE_D.EFF_END_DT AND AGG_DATA.CODE = PAY_REG_D.CODE AND AGG_DATA.INV_DT BETWEEN PAY_REG_D.EFF_START_DT AND PAY_REG_D.EFF_END_DT AND SYSDATE BETWEEN PAY_REG_D.VALID_START_DT AND PAY_REG_D.VALID_END_DT AND AGG_DATA.INVOICE = LIM.INVOICE_NUMBER AND AGG_DATA.EMPLID = LIM.EMPLID AND AGG_DATA.EMPL_RCD = LIM.EMPL_RCD AND AGG_DATA.CODE = LIM.CODE AND AGG_DATA.DEDUCTION_CLASS = LIM.DEDUCTION_CLASS AND ACC.COMPANY_ID = AGG_DATA.COMPANY AND ACC.LOGIN_USER = '1357626' AND ACC.REPORT_ID IN ('C10BENREG', 'C10BENREGCAN', 'C10BENREGSB') AND DW.CHECK_EE_REPORT_ACCESS (AGG_DATA.COMPANY, '1357626', ACC.REPORT_ID, EE_D.EMPLID, EE_D.DEPTID, EE_D.LOCATION_ID) = 1 ORDER BY LIM.NAME, LIM.EMPLID, LIM.DEDUCTION_CODE, LIM.SEPCHK) SELECT "Rpt"."EMPLID" "Employee_ID", "Rpt"."EE_NAME" "Employee_Name", "Rpt"."PLAN_TYPE" "Plan_Type", CASE WHEN "Rpt"."PLAN_TYPE" IS NULL OR "Rpt"."DEDUCTION_DESCRIPTION" IS NULL THEN NULL ELSE "Rpt"."PLAN_TYPE" || ' - ' || "Rpt"."DEDUCTION_DESCRIPTION" END "Type", "Rpt"."DEDUCTION_DESCRIPTION" "Benefit_Plan_Description", "Rpt"."DEDUCTION_CODE" "Deduction_Code", CASE WHEN "Rpt"."DEDUCTION_CODE" IS NULL OR "Rpt"."DESCR_LONG" IS NULL THEN NULL ELSE "Rpt"."DEDUCTION_CODE" || ' - ' || "Rpt"."DESCR_LONG" END "Deduction", "Rpt"."DESCR_LONG" "Payroll_Description", "Rpt"."PAYGROUP_DESCR_LONG" "Pay_Group", CAST ("Rpt"."INVOICE_NUMBER" AS VARCHAR (10)) "Invoice_Number", "Rpt"."PAY_END_DT" "Pay_End_Date", "Rpt"."CALCULATED_BASE" "Calculated_Benefit_Base", "Rpt"."TAXABLE_AMOUNT" "Taxable_Amount", "Rpt"."NON_TAXABLE_AMOUNT" "Non_Taxable_Amount", "Rpt"."MSUM" "MTD", "Rpt"."QSUM" "QTD", "Rpt"."YSUM" "YTD" FROM "Rpt" call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.29 0.31 0 108 0 0 Execute 1 0.00 0.00 0 48 0 0 Fetch 61 0.41 0.42 0 44019 0 887 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 63 0.71 0.74 0 44175 0 887 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 213 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 7 7 7 TABLE ACCESS BY INDEX ROWID BEN_PLAN_TYPE_D (cr=4 pr=0 pw=0 time=92 us cost=2 size=53 card=1) 7 7 7 INDEX RANGE SCAN BEN_PLAN_TYPE_D_N03 (cr=3 pr=0 pw=0 time=61 us cost=1 size=0 card=1)(object id 407480) 887 887 887 PX COORDINATOR (cr=47615 pr=0 pw=0 time=459671 us) 887 887 887 PX SEND QC (ORDER) :TQ10002 (cr=47567 pr=0 pw=0 time=457938 us cost=582 size=821 card=1) 887 887 887 SORT ORDER BY (cr=47567 pr=0 pw=0 time=457338 us cost=582 size=821 card=1) 887 887 887 PX RECEIVE (cr=47563 pr=0 pw=0 time=480091 us) 887 887 887 PX SEND RANGE :TQ10001 (cr=47563 pr=0 pw=0 time=480086 us) 887 887 887 NESTED LOOPS (cr=47563 pr=0 pw=0 time=479830 us) 28928 28928 28928 NESTED LOOPS (cr=43770 pr=0 pw=0 time=253462 us cost=581 size=821 card=1) 887 887 887 NESTED LOOPS (cr=39771 pr=0 pw=0 time=230192 us cost=581 size=763 card=1) 887 887 887 NESTED LOOPS (cr=35661 pr=0 pw=0 time=214198 us cost=580 size=722 card=1) 887 887 887 BUFFER SORT (cr=28564 pr=0 pw=0 time=188469 us) 887 887 887 PX RECEIVE (cr=28564 pr=0 pw=0 time=188378 us) 887 887 887 PX SEND BROADCAST :TQ10000 (cr=28564 pr=0 pw=0 time=188251 us) 887 887 887 HASH JOIN (cr=28564 pr=0 pw=0 time=188250 us cost=579 size=694 card=1) 980 980 980 VIEW (cr=14428 pr=0 pw=0 time=119447 us cost=347 size=664 card=8) 980 980 980 HASH GROUP BY (cr=14428 pr=0 pw=0 time=119199 us cost=347 size=944 card=8) 11056 11056 11056 NESTED LOOPS (cr=14428 pr=0 pw=0 time=84619 us) 11056 11056 11056 NESTED LOOPS (cr=11971 pr=0 pw=0 time=67965 us cost=346 size=944 card=8) 11056 11056 11056 NESTED LOOPS (cr=11461 pr=0 pw=0 time=42160 us cost=345 size=816 card=8) 708 708 708 NESTED LOOPS (cr=59 pr=0 pw=0 time=779 us cost=176 size=7992 card=148) 4 4 4 MERGE JOIN CARTESIAN (cr=13 pr=0 pw=0 time=173 us cost=173 size=41 card=1) 1 1 1 VIEW (cr=5 pr=0 pw=0 time=90 us cost=169 size=36 card=1) 1 1 1 COUNT STOPKEY (cr=5 pr=0 pw=0 time=58 us) 1 1 1 TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=5 pr=0 pw=0 time=44 us cost=169 size=25 card=1) 1 1 1 INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=4 pr=0 pw=0 time=33 us cost=4 size=0 card=268)(object id 407596) 4 4 4 BUFFER SORT (cr=8 pr=0 pw=0 time=95 us cost=173 size=5 card=1) 4 4 4 TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=103 us cost=3 size=5 card=1) 4 4 4 INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=81 us cost=2 size=0 card=1)(object id 160855) 708 708 708 TABLE ACCESS BY INDEX ROWID SYS_CALENDAR_D (cr=46 pr=0 pw=0 time=685 us cost=3 size=1560 card=120) 708 708 708 INDEX RANGE SCAN SYS_CALENDAR_DIM_N01 (cr=10 pr=0 pw=0 time=348 us cost=0 size=0 card=215)(object id 15315) 11056 11056 11056 TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=11402 pr=0 pw=0 time=43785 us cost=4 size=48 card=1) 11060 11060 11060 INDEX RANGE SCAN PAY_REGISTER_F_N05 (cr=395 pr=0 pw=0 time=5395 us cost=3 size=0 card=1)(object id 447234) 11056 11056 11056 INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=510 pr=0 pw=0 time=20171 us cost=0 size=0 card=1)(object id 15537) 11056 11056 11056 TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=2457 pr=0 pw=0 time=9108 us cost=0 size=16 card=1) 887 887 887 VIEW (cr=14136 pr=0 pw=0 time=61125 us cost=231 size=28717 card=47) 887 887 887 WINDOW NOSORT (cr=14136 pr=0 pw=0 time=60993 us cost=231 size=9635 card=47) 887 887 887 SORT GROUP BY (cr=14136 pr=0 pw=0 time=58658 us cost=231 size=9635 card=47) 887 887 887 NESTED LOOPS (cr=14136 pr=0 pw=0 time=59006 us) 887 887 887 NESTED LOOPS (cr=13249 pr=0 pw=0 time=55684 us cost=230 size=9635 card=47) 887 887 887 NESTED LOOPS (cr=10706 pr=0 pw=0 time=48300 us cost=220 size=8554 card=47) 887 887 887 NESTED LOOPS (cr=8985 pr=0 pw=0 time=39919 us cost=209 size=6862 card=47) 887 887 887 HASH JOIN (cr=8976 pr=0 pw=0 time=35819 us cost=199 size=5546 card=47) 887 887 887 NESTED LOOPS (cr=8952 pr=0 pw=0 time=30299 us) 887 887 887 NESTED LOOPS (cr=8951 pr=0 pw=0 time=29278 us cost=192 size=5311 card=47) 887 887 887 NESTED LOOPS (cr=8938 pr=0 pw=0 time=26095 us cost=171 size=3901 card=47) 4 4 4 TABLE ACCESS BY INDEX ROWID PAY_DEDUCTION_CLASS_D (cr=8 pr=0 pw=0 time=72 us cost=3 size=5 card=1) 4 4 4 INDEX RANGE SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=7 pr=0 pw=0 time=56 us cost=2 size=0 card=1)(object id 160855) 887 887 887 TABLE ACCESS BY INDEX ROWID PAY_REGISTER_F (cr=8930 pr=0 pw=0 time=36757 us cost=168 size=2964 card=38) 22396 22396 22396 INDEX RANGE SCAN PAY_REGISTER_F_N04 (cr=90 pr=0 pw=0 time=5215 us cost=3 size=0 card=268)(object id 407596) 887 887 887 INDEX RANGE SCAN HCM_COMPANY_D_PK (cr=13 pr=0 pw=0 time=1306 us cost=0 size=0 card=1)(object id 15313) 887 887 887 TABLE ACCESS BY INDEX ROWID HCM_COMPANY_D (cr=1 pr=0 pw=0 time=499 us cost=0 size=30 card=1) 7 7 7 VIEW index$_join$_023 (cr=24 pr=0 pw=0 time=2842 us cost=7 size=35 card=7) 7 7 7 HASH JOIN (cr=24 pr=0 pw=0 time=2834 us) 7 7 7 INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_N01 (cr=17 pr=0 pw=0 time=92 us cost=5 size=35 card=7)(object id 160855) 7 7 7 INDEX FAST FULL SCAN PAY_DEDUCTION_CLASS_D_PK (cr=7 pr=0 pw=0 time=20 us cost=3 size=35 card=7)(object id 160857) 887 887 887 TABLE ACCESS BY INDEX ROWID PAY_PAYGROUP_D (cr=9 pr=0 pw=0 time=2830 us cost=0 size=28 card=1) 887 887 887 INDEX RANGE SCAN PAY_PAYGROUP_D_PK (cr=8 pr=0 pw=0 time=1833 us cost=0 size=0 card=1)(object id 15499) 887 887 887 TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=1721 pr=0 pw=0 time=5828 us cost=0 size=36 card=1) 887 887 887 INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_PK (cr=887 pr=0 pw=0 time=3297 us cost=0 size=0 card=1)(object id 15537) 887 887 887 PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=2543 pr=0 pw=0 time=5675 us cost=0 size=0 card=1) 887 887 887 INDEX UNIQUE SCAN HCM_PERSON_D_PK PARTITION: KEY KEY (cr=2543 pr=0 pw=0 time=4104 us cost=0 size=0 card=1)(object id 4391000) 887 887 887 TABLE ACCESS BY GLOBAL INDEX ROWID HCM_PERSON_D PARTITION: ROW LOCATION ROW LOCATION (cr=887 pr=0 pw=0 time=2795 us cost=0 size=23 card=1) 887 887 887 INLIST ITERATOR (cr=7097 pr=0 pw=0 time=23735 us) 887 887 887 PX PARTITION HASH ITERATOR PARTITION: KEY(INLIST) KEY(INLIST) (cr=7097 pr=0 pw=0 time=21955 us cost=1 size=28 card=1) 887 887 887 INDEX RANGE SCAN T2_DW_ADMIN_STDRPT_ACCESS_N1 PARTITION: KEY(INLIST) KEY(INLIST) (cr=7097 pr=0 pw=0 time=17375 us cost=1 size=28 card=1)(object id 7065133) 887 887 887 TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_REGISTER_D (cr=4110 pr=0 pw=0 time=13250 us cost=3 size=41 card=1) 2215 2215 2215 INDEX RANGE SCAN PAY_PAYROLL_REGISTER_D_N04 (cr=1778 pr=0 pw=0 time=5866 us cost=2 size=0 card=1)(object id 407517) 28928 28928 28928 PARTITION HASH ITERATOR PARTITION: KEY KEY (cr=3999 pr=0 pw=0 time=126716 us cost=0 size=0 card=1) 28928 28928 28928 INDEX RANGE SCAN HCM_EMPLOYEE_D_N03 PARTITION: KEY KEY (cr=3999 pr=0 pw=0 time=119709 us cost=0 size=0 card=1)(object id 4391673) 887 887 887 TABLE ACCESS BY GLOBAL INDEX ROWID HCM_EMPLOYEE_D PARTITION: ROW LOCATION ROW LOCATION (cr=3793 pr=0 pw=0 time=151330 us cost=0 size=58 card=1) ********************************************************************************