ITPub博客

首页 > Linux操作系统 > Linux操作系统 > _unnest_subquery 参数引起执行计划改变

_unnest_subquery 参数引起执行计划改变

原创 Linux操作系统 作者:jimmy13202 时间:2011-11-30 19:12:29 0 删除 编辑
ps89tech
 
select   *                                                        
FROM   PS_Benefits_VW BV
WHERE  BV.Emplid          = '10679'
AND  BV.Plan_Type       = '21'
AND  BV.Pay_End_Dt = (SELECT  MAX(BV2.Pay_End_Dt)
FROM  PS_Benefits_VW BV2
WHERE BV2.Emplid    = BV.Emplid
AND BV2.EMPL_RCD = BV.EMPL_RCD
AND BV2.Plan_Type = BV.Plan_Type
AND BV2.Benefit_Plan = BV.Benefit_Plan
AND BV2.Pay_End_Dt <= sysdate)
AND  BV.EMPL_RCD = (SELECT  MAX(BV3.EMPL_RCD)
FROM  PS_Benefits_VW BV3
WHERE BV3.Emplid    = BV.Emplid
AND BV3.EMPL_RCD = BV.EMPL_RCD
AND BV3.Plan_Type = BV.Plan_Type
AND BV3.Benefit_Plan = BV.Benefit_Plan
AND BV3.Pay_End_Dt = BV.Pay_End_Dt);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3181770671
---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |     1 |    89 | 80491   (1)| 00:16:06 |
|   1 |  NESTED LOOPS                        |                  |       |       |            |          |
|   2 |   NESTED LOOPS                       |                  |     1 |    89 |   827   (1)| 00:00:10 |
|   3 |    TABLE ACCESS BY INDEX ROWID       | PS_PAY_CHECK     |   182 |  6916 |   184   (0)| 00:00:03 |
|*  4 |     INDEX SKIP SCAN                  | PSAPAY_CHECK     |   182 |       |     7   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | PS_PAY_DEDUCTION |     1 |       |     3   (0)| 00:00:01 |
|   6 |     SORT AGGREGATE                   |                  |     1 |    80 |            |          |
|*  7 |      FILTER                          |                  |       |       |            |          |
|   8 |       NESTED LOOPS                   |                  |     1 |    80 |   731   (1)| 00:00:09 |
|*  9 |        TABLE ACCESS BY INDEX ROWID   | PS_PAY_CHECK     |   182 |  6916 |   184   (0)| 00:00:03 |
|* 10 |         INDEX SKIP SCAN              | PSAPAY_CHECK     |   182 |       |     7   (0)| 00:00:01 |
|* 11 |        INDEX RANGE SCAN              | PS_PAY_DEDUCTION |     1 |    42 |     3   (0)| 00:00:01 |
|  12 |         SORT AGGREGATE               |                  |     1 |    78 |            |          |
|  13 |          NESTED LOOPS                |                  |    91 |  7098 |   731   (1)| 00:00:09 |
|* 14 |           TABLE ACCESS BY INDEX ROWID| PS_PAY_CHECK     |   182 |  6916 |   184   (0)| 00:00:03 |
|* 15 |            INDEX SKIP SCAN           | PSAPAY_CHECK     |   182 |       |     7   (0)| 00:00:01 |
|* 16 |           INDEX RANGE SCAN           | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|  17 |         SORT AGGREGATE               |                  |     1 |    42 |            |          |
|* 18 |          INDEX RANGE SCAN            | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
|  19 |     SORT AGGREGATE                   |                  |     1 |    78 |            |          |
|  20 |      NESTED LOOPS                    |                  |    91 |  7098 |   731   (1)| 00:00:09 |
|* 21 |       TABLE ACCESS BY INDEX ROWID    | PS_PAY_CHECK     |   182 |  6916 |   184   (0)| 00:00:03 |
|* 22 |        INDEX SKIP SCAN               | PSAPAY_CHECK     |   182 |       |     7   (0)| 00:00:01 |
|* 23 |       INDEX RANGE SCAN               | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|  24 |     SORT AGGREGATE                   |                  |     1 |    42 |            |          |
|* 25 |      INDEX RANGE SCAN                | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
|  26 |     SORT AGGREGATE                   |                  |     1 |    80 |            |          |
|* 27 |      FILTER                          |                  |       |       |            |          |
|  28 |       NESTED LOOPS                   |                  |     1 |    80 |     7   (0)| 00:00:01 |
|* 29 |        TABLE ACCESS BY INDEX ROWID   | PS_PAY_CHECK     |     1 |    38 |     5   (0)| 00:00:01 |
|* 30 |         INDEX RANGE SCAN             | PS1PAY_CHECK     |     1 |       |     4   (0)| 00:00:01 |
|* 31 |        INDEX RANGE SCAN              | PS_PAY_DEDUCTION |     1 |    42 |     3   (0)| 00:00:01 |
|  32 |         SORT AGGREGATE               |                  |     1 |    78 |            |          |
|  33 |          NESTED LOOPS                |                  |    91 |  7098 |   731   (1)| 00:00:09 |
|* 34 |           TABLE ACCESS BY INDEX ROWID| PS_PAY_CHECK     |   182 |  6916 |   184   (0)| 00:00:03 |
|* 35 |            INDEX SKIP SCAN           | PSAPAY_CHECK     |   182 |       |     7   (0)| 00:00:01 |
|* 36 |           INDEX RANGE SCAN           | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|  37 |         SORT AGGREGATE               |                  |     1 |    42 |            |          |
|* 38 |          INDEX RANGE SCAN            | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
|  39 |   TABLE ACCESS BY INDEX ROWID        | PS_PAY_DEDUCTION |     1 |    51 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."EMPLID"='10679')
       filter("A"."EMPLID"='10679')
   5 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK"
              AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "B"."PLAN_TYPE"='21')
       filter("B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"= (SELECT MAX("B"."PAY_END_DT") FROM
              SYSADM."PS_PAY_DEDUCTION" "SYS_ALIAS_14",SYSADM."PS_PAY_CHECK" "SYS_ALIAS_2" WHERE :B1<>'00' AND
              "A"."EMPL_RCD"=:B2 AND "A"."PAY_END_DT"<=SYSDATE@! AND "A"."EMPLID"=:B3 AND
              "B"."BENEFIT_PLAN"=:B4 AND "B"."PLAN_TYPE"=:B5 AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR"
              AND "A"."SEPCHK"="B"."SEPCHK" AND "A"."LINE_NUM"="B"."LINE_NUM" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."COMPANY"="B"."COMPANY" AND "B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"<=SYSDATE@! AND
              "B"."PAY_END_DT"= (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION"
              "D",SYSADM."PS_PAY_CHECK" "C" WHERE "C"."EMPL_RCD"=:B6 AND
              "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD') AND "C"."EMPLID"=:B7 AND
              "D"."BENEFIT_PLAN"=:B8 AND "D"."PLAN_TYPE"=:B9 AND "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR"
              AND "D"."SEPCHK"="C"."SEPCHK" AND "D"."LINE_NUM"="C"."LINE_NUM" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."COMPANY"="C"."COMPANY" AND "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-M
              M-DD')) AND "B"."DED_CLASS"= (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E"
              WHERE "E"."BENEFIT_PLAN"=:B10 AND "E"."PLAN_TYPE"=:B11 AND "E"."BENEFIT_RCD_NBR"=:B12 AND
              "E"."SEPCHK"=:B13 AND "E"."LINE_NUM"=:B14 AND "E"."PAGE_NUM"=:B15 AND "E"."OFF_CYCLE"=:B16 AND
              "E"."PAY_END_DT"=:B17 AND "E"."PAYGROUP"=:B18 AND "E"."COMPANY"=:B19)) AND "B"."PAY_END_DT"=
              (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION" "D",SYSADM."PS_PAY_CHECK" "C" WHERE
              "C"."EMPL_RCD"=:B20 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')
              AND "C"."EMPLID"=:B21 AND "D"."BENEFIT_PLAN"=:B22 AND "D"."PLAN_TYPE"=:B23 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND
              "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND
              "D"."PAYGROUP"="C"."PAYGROUP" AND "D"."COMPANY"="C"."COMPANY" AND
              "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "B"."DED_CLASS"=
              (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"=:B24
              AND "E"."PLAN_TYPE"=:B25 AND "E"."BENEFIT_RCD_NBR"=:B26 AND "E"."SEPCHK"=:B27 AND
              "E"."LINE_NUM"=:B28 AND "E"."PAGE_NUM"=:B29 AND "E"."OFF_CYCLE"=:B30 AND "E"."PAY_END_DT"=:B31
              AND "E"."PAYGROUP"=:B32 AND "E"."COMPANY"=:B33) AND "A"."EMPL_RCD"= (SELECT MAX("A"."EMPL_RCD")
              FROM SYSADM."PS_PAY_DEDUCTION" "SYS_ALIAS_14",SYSADM."PS_PAY_CHECK" "SYS_ALIAS_2" WHERE
              :B34<>'00' AND "A"."EMPLID"=:B35 AND "A"."EMPL_RCD"=:B36 AND "A"."PAY_END_DT"=:B37 AND
              "B"."BENEFIT_PLAN"=:B38 AND "B"."PLAN_TYPE"=:B39 AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR"
              AND "A"."SEPCHK"="B"."SEPCHK" AND "A"."LINE_NUM"="B"."LINE_NUM" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."COMPANY"="B"."COMPANY" AND "B"."PAY_END_DT"=:B40 AND "B"."PLAN_TYPE"<>'00' AND
              "B"."PAY_END_DT"= (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION"
              "D",SYSADM."PS_PAY_CHECK" "C" WHERE "C"."EMPL_RCD"=:B41 AND
              "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD') AND "C"."EMPLID"=:B42
              AND "D"."BENEFIT_PLAN"=:B43 AND "D"."PLAN_TYPE"=:B44 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND
              "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND
              "D"."PAYGROUP"="C"."PAYGROUP" AND "D"."COMPANY"="C"."COMPANY" AND
              "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "B"."DED_CLASS"=
              (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"=)
   7 - filter(:B1<>'00')
   9 - filter("A"."EMPL_RCD"=:B1 AND "A"."PAY_END_DT"<=SYSDATE@!)
  10 - access("A"."EMPLID"=:B1)
       filter("A"."EMPLID"=:B1)
  11 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK"
              AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "B"."PLAN_TYPE"=:B1 AND
              "B"."BENEFIT_PLAN"=:B2)
       filter("B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"<=SYSDATE@! AND "B"."PAY_END_DT"=
              (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION" "D",SYSADM."PS_PAY_CHECK" "C" WHERE
              "C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')
              AND "C"."EMPLID"=:B2 AND "D"."BENEFIT_PLAN"=:B3 AND "D"."PLAN_TYPE"=:B4 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND
              "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND
              "D"."PAYGROUP"="C"."PAYGROUP" AND "D"."COMPANY"="C"."COMPANY" AND
              "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "B"."DED_CLASS"=
              (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"=:B5 AND
              "E"."PLAN_TYPE"=:B6 AND "E"."BENEFIT_RCD_NBR"=:B7 AND "E"."SEPCHK"=:B8 AND "E"."LINE_NUM"=:B9
              AND "E"."PAGE_NUM"=:B10 AND "E"."OFF_CYCLE"=:B11 AND "E"."PAY_END_DT"=:B12 AND
              "E"."PAYGROUP"=:B13 AND "E"."COMPANY"=:B14))
  14 - filter("C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'Y
              YYY-MM-DD'))
  15 - access("C"."EMPLID"=:B1)
       filter("C"."EMPLID"=:B1)
  16 - access("D"."COMPANY"="C"."COMPANY" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."SEPCHK"="C"."SEPCHK"
              AND "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."PLAN_TYPE"=:B1 AND
              "D"."BENEFIT_PLAN"=:B2)
       filter("D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
  18 - access("E"."COMPANY"=:B1 AND "E"."PAYGROUP"=:B2 AND "E"."PAY_END_DT"=:B3 AND
              "E"."OFF_CYCLE"=:B4 AND "E"."PAGE_NUM"=:B5 AND "E"."LINE_NUM"=:B6 AND "E"."SEPCHK"=:B7 AND
              "E"."BENEFIT_RCD_NBR"=:B8 AND "E"."PLAN_TYPE"=:B9 AND "E"."BENEFIT_PLAN"=:B10)
  21 - filter("C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'Y
              YYY-MM-DD'))
  22 - access("C"."EMPLID"=:B1)
       filter("C"."EMPLID"=:B1)
  23 - access("D"."COMPANY"="C"."COMPANY" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."SEPCHK"="C"."SEPCHK"
              AND "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."PLAN_TYPE"=:B1 AND
              "D"."BENEFIT_PLAN"=:B2)
       filter("D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
  25 - access("E"."COMPANY"=:B1 AND "E"."PAYGROUP"=:B2 AND "E"."PAY_END_DT"=:B3 AND
              "E"."OFF_CYCLE"=:B4 AND "E"."PAGE_NUM"=:B5 AND "E"."LINE_NUM"=:B6 AND "E"."SEPCHK"=:B7 AND
              "E"."BENEFIT_RCD_NBR"=:B8 AND "E"."PLAN_TYPE"=:B9 AND "E"."BENEFIT_PLAN"=:B10)
  27 - filter(:B1<>'00')
  29 - filter("A"."EMPL_RCD"=:B1)
  30 - access("A"."EMPLID"=:B1 AND "A"."PAY_END_DT"=:B2)
       filter("A"."PAY_END_DT"=:B1)
  31 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK"
              AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "B"."PLAN_TYPE"=:B1 AND
              "B"."BENEFIT_PLAN"=:B2)
       filter("B"."PAY_END_DT"=:B1 AND "B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"= (SELECT
              MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION" "D",SYSADM."PS_PAY_CHECK" "C" WHERE
              "C"."EMPL_RCD"=:B2 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')
              AND "C"."EMPLID"=:B3 AND "D"."BENEFIT_PLAN"=:B4 AND "D"."PLAN_TYPE"=:B5 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND
              "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND
              "D"."PAYGROUP"="C"."PAYGROUP" AND "D"."COMPANY"="C"."COMPANY" AND
              "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "B"."DED_CLASS"=
              (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"=:B6 AND
              "E"."PLAN_TYPE"=:B7 AND "E"."BENEFIT_RCD_NBR"=:B8 AND "E"."SEPCHK"=:B9 AND "E"."LINE_NUM"=:B10
              AND "E"."PAGE_NUM"=:B11 AND "E"."OFF_CYCLE"=:B12 AND "E"."PAY_END_DT"=:B13 AND
              "E"."PAYGROUP"=:B14 AND "E"."COMPANY"=:B15))
  34 - filter("C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'Y
              YYY-MM-DD'))
  35 - access("C"."EMPLID"=:B1)
       filter("C"."EMPLID"=:B1)
  36 - access("D"."COMPANY"="C"."COMPANY" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."SEPCHK"="C"."SEPCHK"
              AND "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."PLAN_TYPE"=:B1 AND
              "D"."BENEFIT_PLAN"=:B2)
       filter("D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
  38 - access("E"."COMPANY"=:B1 AND "E"."PAYGROUP"=:B2 AND "E"."PAY_END_DT"=:B3 AND
              "E"."OFF_CYCLE"=:B4 AND "E"."PAGE_NUM"=:B5 AND "E"."LINE_NUM"=:B6 AND "E"."SEPCHK"=:B7 AND
              "E"."BENEFIT_RCD_NBR"=:B8 AND "E"."PLAN_TYPE"=:B9 AND "E"."BENEFIT_PLAN"=:B10)
188 rows selected.
 
 
ps89prod
 
select   *                                                        
FROM   PS_Benefits_VW BV
WHERE  BV.Emplid          = '10679'
AND  BV.Plan_Type       = '21'
AND  BV.Pay_End_Dt = (SELECT  MAX(BV2.Pay_End_Dt)
FROM  PS_Benefits_VW BV2
WHERE BV2.Emplid    = BV.Emplid
AND BV2.EMPL_RCD = BV.EMPL_RCD
AND BV2.Plan_Type = BV.Plan_Type
AND BV2.Benefit_Plan = BV.Benefit_Plan
AND BV2.Pay_End_Dt <= sysdate)
AND  BV.EMPL_RCD = (SELECT  MAX(BV3.EMPL_RCD)
FROM  PS_Benefits_VW BV3
WHERE BV3.Emplid    = BV.Emplid
AND BV3.EMPL_RCD = BV.EMPL_RCD
AND BV3.Plan_Type = BV.Plan_Type
AND BV3.Benefit_Plan = BV.Benefit_Plan
AND BV3.Pay_End_Dt = BV.Pay_End_Dt);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2069921788
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                  |     1 |   128 |  3260   (1)| 00:00:40 |
|   1 |  NESTED LOOPS                              |                  |     1 |   128 |  2319   (1)| 00:00:28 |
|   2 |   NESTED LOOPS                             |                  |     1 |   126 |  2315   (1)| 00:00:28 |
|   3 |    NESTED LOOPS                            |                  |   183 | 13725 |  1669   (1)| 00:00:21 |
|   4 |     VIEW                                   | VW_SQ_5          |     1 |    37 |  1481   (1)| 00:00:18 |
|*  5 |      FILTER                                |                  |       |       |            |          |
|   6 |       HASH GROUP BY                        |                  |     1 |   108 |  1481   (1)| 00:00:18 |
|   7 |        NESTED LOOPS                        |                  |     1 |   108 |  1481   (1)| 00:00:18 |
|   8 |         NESTED LOOPS                       |                  |     1 |   106 |  1477   (1)| 00:00:18 |
|   9 |          NESTED LOOPS                      |                  |   183 | 11712 |   927   (1)| 00:00:12 |
|  10 |           VIEW                             | VW_SQ_1          |     1 |    26 |   739   (1)| 00:00:09 |
|* 11 |            FILTER                          |                  |       |       |            |          |
|  12 |             HASH GROUP BY                  |                  |     1 |    78 |   739   (1)| 00:00:09 |
|  13 |              NESTED LOOPS                  |                  |   237 | 18486 |   738   (1)| 00:00:09 |
|* 14 |               TABLE ACCESS BY INDEX ROWID  | PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 15 |                INDEX SKIP SCAN             | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|* 16 |               INDEX RANGE SCAN             | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|* 17 |           TABLE ACCESS BY INDEX ROWID      | PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 18 |            INDEX SKIP SCAN                 | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|* 19 |          INDEX RANGE SCAN                  | PS_PAY_DEDUCTION |     1 |    42 |     3   (0)| 00:00:01 |
|* 20 |         VIEW PUSHED PREDICATE              | VW_SQ_2          |     1 |     2 |     4   (0)| 00:00:01 |
|* 21 |          FILTER                            |                  |       |       |            |          |
|  22 |           SORT AGGREGATE                   |                  |     1 |    42 |            |          |
|* 23 |            FILTER                          |                  |       |       |            |          |
|* 24 |             INDEX RANGE SCAN               | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
|* 25 |     TABLE ACCESS BY INDEX ROWID            | PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 26 |      INDEX SKIP SCAN                       | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|  27 |    TABLE ACCESS BY INDEX ROWID             | PS_PAY_DEDUCTION |     1 |    51 |     4   (0)| 00:00:01 |
|* 28 |     INDEX RANGE SCAN                       | PS_PAY_DEDUCTION |     1 |       |     3   (0)| 00:00:01 |
|  29 |      SORT AGGREGATE                        |                  |     1 |    78 |            |          |
|  30 |       NESTED LOOPS                         |                  |   157 | 12246 |   738   (1)| 00:00:09 |
|* 31 |        TABLE ACCESS BY INDEX ROWID         | PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 32 |         INDEX SKIP SCAN                    | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|* 33 |        INDEX RANGE SCAN                    | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|  34 |      SORT AGGREGATE                        |                  |     1 |   119 |            |          |
|  35 |       NESTED LOOPS                         |                  |     1 |   119 |   203   (1)| 00:00:03 |
|  36 |        NESTED LOOPS                        |                  |     1 |   117 |   199   (1)| 00:00:03 |
|  37 |         NESTED LOOPS                       |                  |     1 |    75 |   196   (1)| 00:00:03 |
|  38 |          VIEW                              | VW_SQ_3          |     1 |    37 |   192   (1)| 00:00:03 |
|* 39 |           FILTER                           |                  |       |       |            |          |
|  40 |            SORT GROUP BY                   |                  |     1 |    83 |   192   (1)| 00:00:03 |
|  41 |             NESTED LOOPS                   |                  |     1 |    83 |   192   (1)| 00:00:03 |
|  42 |              VIEW                          | VW_GBC_12        |     1 |    43 |   189   (1)| 00:00:03 |
|  43 |               SORT GROUP BY                |                  |     1 |    38 |   189   (1)| 00:00:03 |
|* 44 |                FILTER                      |                  |       |       |            |          |
|* 45 |                 TABLE ACCESS BY INDEX ROWID| PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 46 |                  INDEX SKIP SCAN           | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|* 47 |              INDEX RANGE SCAN              | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|* 48 |          TABLE ACCESS BY INDEX ROWID       | PS_PAY_CHECK     |     1 |    38 |     4   (0)| 00:00:01 |
|* 49 |           INDEX RANGE SCAN                 | PS1PAY_CHECK     |     1 |       |     3   (0)| 00:00:01 |
|* 50 |         INDEX RANGE SCAN                   | PS_PAY_DEDUCTION |     1 |    42 |     3   (0)| 00:00:01 |
|* 51 |        VIEW PUSHED PREDICATE               | VW_SQ_4          |     1 |     2 |     4   (0)| 00:00:01 |
|* 52 |         FILTER                             |                  |       |       |            |          |
|  53 |          SORT AGGREGATE                    |                  |     1 |    42 |            |          |
|* 54 |           INDEX RANGE SCAN                 | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
|* 55 |   VIEW PUSHED PREDICATE                    | VW_SQ_6          |     1 |     2 |     4   (0)| 00:00:01 |
|* 56 |    FILTER                                  |                  |       |       |            |          |
|  57 |     SORT AGGREGATE                         |                  |     1 |    42 |            |          |
|* 58 |      FILTER                                |                  |       |       |            |          |
|* 59 |       INDEX RANGE SCAN                     | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("B"."PLAN_TYPE"<>'00' AND "A"."EMPLID"='10679' AND "B"."PLAN_TYPE"='21')
  11 - filter("C"."EMPLID"='10679' AND "D"."PLAN_TYPE"='21' AND MAX("C"."PAY_END_DT")<=SYSDATE@!)
  14 - filter("C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
  15 - access("C"."EMPLID"='10679')
       filter("C"."EMPLID"='10679')
  16 - access("D"."COMPANY"="C"."COMPANY" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."PLAN_TYPE"='21')
       filter("D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD') AND
              "D"."PLAN_TYPE"<>'00')
  17 - filter("A"."PAY_END_DT"<=SYSDATE@! AND "ITEM_2"="A"."EMPL_RCD")
  18 - access("A"."EMPLID"='10679')
       filter("A"."EMPLID"='10679')
  19 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "B"."PAY_END_DT"="MAX(C.PAY_END_DT)" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK" AND
              "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "B"."PLAN_TYPE"='21' AND "ITEM_4"="B"."BENEFIT_PLAN")
       filter("B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"<=SYSDATE@! AND
              "A"."PAY_END_DT"="B"."PAY_END_DT")
  20 - filter("B"."DED_CLASS"="MIN(E.DED_CLASS)")
  21 - filter(COUNT(*)>0 AND '00'<>'21')
  23 - filter(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')>="B"."PAY_END_DT" AND
              SYSDATE@!>="B"."PAY_END_DT" AND '21'="B"."PLAN_TYPE")
  24 - access("E"."COMPANY"="B"."COMPANY" AND "E"."PAYGROUP"="B"."PAYGROUP" AND
              "E"."PAY_END_DT"="B"."PAY_END_DT" AND "E"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "E"."PAGE_NUM"="B"."PAGE_NUM" AND "E"."LINE_NUM"="B"."LINE_NUM" AND "E"."SEPCHK"="B"."SEPCHK" AND
              "E"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "E"."PLAN_TYPE"='21' AND
              "E"."BENEFIT_PLAN"="B"."BENEFIT_PLAN")
       filter("E"."PAY_END_DT"<=LEAST(SYSDATE@!,TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')))
  25 - filter("ITEM_30"="A"."EMPL_RCD")
  26 - access("A"."EMPLID"='10679')
       filter("A"."EMPLID"='10679')
  28 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK" AND
              "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "B"."PLAN_TYPE"='21' AND "ITEM_32"="B"."BENEFIT_PLAN")
       filter("B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"="MAX(BV2.PAY_END_DT)" AND "B"."PAY_END_DT"=
              (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION" "D",SYSADM."PS_PAY_CHECK" "C" WHERE
              "C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD') AND
              "C"."EMPLID"=:B2 AND "D"."BENEFIT_PLAN"=:B3 AND "D"."PLAN_TYPE"=:B4 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE"
              AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."COMPANY"="C"."COMPANY" AND "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')
              ) AND "A"."EMPL_RCD"= (SELECT MAX("A"."EMPL_RCD") FROM SYSADM."PS_PAY_DEDUCTION"
              "B",SYSADM."PS_PAY_CHECK" "A", (SELECT MAX("ITEM_9") "MAX(C.PAY_END_DT)","ITEM_10" "ITEM_15","ITEM_11"
              "ITEM_16","D"."PLAN_TYPE" "ITEM_17","D"."BENEFIT_PLAN" "ITEM_18" FROM SYSADM."PS_PAY_DEDUCTION" "D",
              (SELECT "C"."BENEFIT_RCD_NBR" "ITEM_1","C"."SEPCHK" "ITEM_2","C"."LINE_NUM" "ITEM_3","C"."PAGE_NUM"
              "ITEM_4","C"."OFF_CYCLE" "ITEM_5","C"."PAY_END_DT" "ITEM_6","C"."PAYGROUP" "ITEM_7","C"."COMPANY"
              "ITEM_8",MAX("C"."PAY_END_DT") "ITEM_9","C"."EMPLID" "ITEM_10","C"."EMPL_RCD" "ITEM_11" FROM
              SYSADM."PS_PAY_CHECK" "C" WHERE :B5<>'00' AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD')
              ,'YYYY-MM-DD') AND "C"."EMPL_RCD"=:B6 AND "C"."EMPLID"=:B7 GROUP BY
              "C"."EMPLID","C"."EMPL_RCD","C"."LINE_NUM","C"."PAGE_NUM","C"."OFF_CYCLE","C"."PAY_END_DT","C"."PAYGROU
              P","C"."COMPANY","C"."BENEFIT_RCD_NBR","C"."SEPCHK") "VW_GBC_12" WHERE "D"."BENEFIT_PLAN"=:B8 AND
              "D"."PLAN_TYPE"=:B9 AND "D"."BENEFIT_RCD_NBR"="ITEM_1" AND "D"."SEPCHK"="ITEM_2" AND
              "D"."LINE_NUM"="ITEM_3" AND "D"."PAGE_NUM"="ITEM_4" AND "D"."OFF_CYCLE"="ITEM_5" AND
              "D"."PAY_END_DT"="ITEM_6" AND "D"."PAYGROUP"="ITEM_7" AND "D"."COMPANY"="ITEM_8" AND
              "D"."PLAN_TYPE"<>'00' GROUP BY "ITEM_10","ITEM_11","D"."PLAN_TYPE","D"."BENEFIT_PLAN" HAVING
              MAX("ITEM_9")=:B10) "VW_SQ_3", (SELECT MIN("E"."DED_CLASS") "MIN(E.DED_CLASS)" FROM
              SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"="B"."BENEFIT_PLAN" AND
              "E"."PLAN_TYPE"="B"."PLAN_TYPE" AND "E"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND
              "E"."SEPCHK"="B"."SEPCHK" AND "E"."LINE_NUM"="B"."LINE_NUM" AND "E"."PAGE_NUM"="B"."PAGE_NUM" AND
              "E"."OFF_CYCLE"="B"."OFF_CYCLE" AND "E"."PAY_END_DT"="B"."PAY_END_DT" AND
              "E"."PAYGROUP"="B"."PAYGROUP" AND "E"."COMPANY"="B"."COMPANY" HAVING COUNT(*)>0 AND :B11<>'00')
              "VW_SQ_4" WHERE "B"."DED_CLASS"="MIN(E.DED_CLASS)" AND "ITEM_15"="A"."EMPLID" AND "A"."EMPL_RCD"=:B12
              AND "ITEM_16"="A"."EMPL_RCD" AND "A"."EMPLID"=:B13 AND "A"."PAY_END_DT"=:B14 AND
              "ITEM_18"="B"."BENEFIT_PLAN" AND "ITEM_17"="B"."PLAN_TYPE" AND
              "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "A"."SEPCHK"="B"."SEPCHK" AND
              "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE"
              AND "B"."PAY_END_DT"="MAX(C.PAY_END_DT)" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."COMPANY"="B"."COMPANY" AND "B"."PAY_END_DT"=:B15 AND "B"."BENEFIT_PLAN"=:B16 AND
              "B"."PLAN_TYPE"=:B17 AND "B"."PLAN_TYPE"<>'00' AND "A"."PAY_END_DT"="B"."PAY_END_DT"))
  31 - filter("C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM
              -DD'))
  32 - access("C"."EMPLID"=:B1)
       filter("C"."EMPLID"=:B1)
  33 - access("D"."COMPANY"="C"."COMPANY" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."PLAN_TYPE"=:B1 AND "D"."BENEFIT_PLAN"=:B2)
       filter("D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
  39 - filter(MAX("ITEM_9")=:B1)
  44 - filter(:B1<>'00')
  45 - filter("C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD') AND
              "C"."EMPL_RCD"=:B1)
  46 - access("C"."EMPLID"=:B1)
       filter("C"."EMPLID"=:B1)
  47 - access("D"."COMPANY"="ITEM_8" AND "D"."PAYGROUP"="ITEM_7" AND "D"."PAY_END_DT"="ITEM_6" AND
              "D"."OFF_CYCLE"="ITEM_5" AND "D"."PAGE_NUM"="ITEM_4" AND "D"."LINE_NUM"="ITEM_3" AND
              "D"."SEPCHK"="ITEM_2" AND "D"."BENEFIT_RCD_NBR"="ITEM_1" AND "D"."PLAN_TYPE"=:B1 AND
              "D"."BENEFIT_PLAN"=:B2)
       filter("D"."PLAN_TYPE"<>'00')
  48 - filter("A"."EMPL_RCD"=:B1 AND "ITEM_16"="A"."EMPL_RCD")
  49 - access("ITEM_15"="A"."EMPLID" AND "A"."PAY_END_DT"=:B1)
       filter("A"."EMPLID"=:B1 AND "A"."PAY_END_DT"=:B2)
  50 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "B"."PAY_END_DT"="MAX(C.PAY_END_DT)" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK" AND
              "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "ITEM_17"="B"."PLAN_TYPE" AND
              "ITEM_18"="B"."BENEFIT_PLAN")
       filter("B"."PAY_END_DT"=:B1 AND "B"."BENEFIT_PLAN"=:B2 AND "B"."PLAN_TYPE"=:B3 AND
              "B"."PLAN_TYPE"<>'00' AND "A"."PAY_END_DT"="B"."PAY_END_DT")
  51 - filter("B"."DED_CLASS"="MIN(E.DED_CLASS)")
  52 - filter(COUNT(*)>0 AND :B1<>'00')
  54 - access("E"."COMPANY"="B"."COMPANY" AND "E"."PAYGROUP"="B"."PAYGROUP" AND
              "E"."PAY_END_DT"="B"."PAY_END_DT" AND "E"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "E"."PAGE_NUM"="B"."PAGE_NUM" AND "E"."LINE_NUM"="B"."LINE_NUM" AND "E"."SEPCHK"="B"."SEPCHK" AND
              "E"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "E"."PLAN_TYPE"="B"."PLAN_TYPE" AND
              "E"."BENEFIT_PLAN"="B"."BENEFIT_PLAN")
  55 - filter("B"."DED_CLASS"="MIN(E.DED_CLASS)")
  56 - filter(COUNT(*)>0 AND '21'<>'00')
  58 - filter(SYSDATE@!>="B"."PAY_END_DT" AND TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')>="B
              "."PAY_END_DT" AND '21'="B"."PLAN_TYPE")
  59 - access("E"."COMPANY"="B"."COMPANY" AND "E"."PAYGROUP"="B"."PAYGROUP" AND
              "E"."PAY_END_DT"="B"."PAY_END_DT" AND "E"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "E"."PAGE_NUM"="B"."PAGE_NUM" AND "E"."LINE_NUM"="B"."LINE_NUM" AND "E"."SEPCHK"="B"."SEPCHK" AND
              "E"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "E"."PLAN_TYPE"='21' AND
              "E"."BENEFIT_PLAN"="B"."BENEFIT_PLAN")
       filter("E"."PAY_END_DT"<=LEAST(TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'),SYSDATE@!))
190 rows selected.
 
在ps89prod上执行该语句,消耗3分钟,在ps89tech上只需要不到一分钟
分析发现两库上隐藏参数_unnest_subquery不一样所致。
 

SQL> show parameter db_name

 

NAME                                 TYPE        VALUE

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

db_name                              string      PS89PROD

SQL> SELECT ksppinm, ksppstvl, ksppdesc

FROM x$ksppi x, x$ksppcv y

WHERE x.indx = y.indx

AND translate(ksppinm,'_','#') like '_unnest_subquery%';  2    3    4

 

KSPPINM              KSPPSTVL   KSPPDESC

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

_unnest_subquery     TRUE       enables unnesting of complex subqueries

 

SQL> alter session set current_schema=sysadm;

 

Session altered.

 

SQL> set timing on;

SQL> select   *

FROM   PS_Benefits_VW BV

WHERE  BV.Emplid = '10679'

  2    3    4  AND  BV.Plan_Type= '21'

  5  AND  BV.Pay_End_Dt = (SELECT  MAX(BV2.Pay_End_Dt)

  6  FROM  PS_Benefits_VW BV2

  7  WHERE BV2.Emplid    = BV.Emplid

  8  AND BV2.EMPL_RCD = BV.EMPL_RCD

  9  AND BV2.Plan_Type = BV.Plan_Type

 10  AND BV2.Benefit_Plan = BV.Benefit_Plan

 11  AND BV2.Pay_End_Dt <= sysdate)

 12  AND  BV.EMPL_RCD = (SELECT   MAX(BV3.EMPL_RCD)

 13  FROM  PS_Benefits_VW BV3

 14  WHERE BV3.Emplid    = BV.Emplid

 15  AND BV3.EMPL_RCD = BV.EMPL_RCD

 16  AND BV3.Plan_Type = BV.Plan_Type

 17  AND BV3.Benefit_Plan = BV.Benefit_Plan

 18  AND BV3.Pay_End_Dt = BV.Pay_End_Dt);

 

EMPLID        EMPL_RCD PL BENEFI D PAY_END_DT     DED_CUR CALCULATED_BASE D

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

10679                0 21 3XLIFE B 14-FEB-2003          0          135000 B

 

Elapsed: 00:00:03.20

SQL> alter session set "_unnest_subquery"=false;

 

Session altered.

 

Elapsed: 00:00:00.00

SQL> select   *

  2  FROM   PS_Benefits_VW BV

WHERE  BV.Emplid = '10679'

  3    4  AND  BV.Plan_Type= '21'

  5  AND  BV.Pay_End_Dt = (SELECT  MAX(BV2.Pay_End_Dt)

  6  FROM  PS_Benefits_VW BV2

  7  WHERE BV2.Emplid    = BV.Emplid

AND BV2.EMPL_RCD = BV.EMPL_RCD

  8    9  AND BV2.Plan_Type = BV.Plan_Type

AND BV2.Benefit_Plan = BV.Benefit_Plan

 10   11  AND BV2.Pay_End_Dt <= sysdate)

 12  AND  BV.EMPL_RCD = (SELECT   MAX(BV3.EMPL_RCD)

 13  FROM  PS_Benefits_VW BV3

 14  WHERE BV3.Emplid    = BV.Emplid

 15  AND BV3.EMPL_RCD = BV.EMPL_RCD

 16  AND BV3.Plan_Type = BV.Plan_Type

 17  AND BV3.Benefit_Plan = BV.Benefit_Plan

 18  AND BV3.Pay_End_Dt = BV.Pay_End_Dt);

 

EMPLID        EMPL_RCD PL BENEFI D PAY_END_DT     DED_CUR CALCULATED_BASE D

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

10679                0 21 3XLIFE B 14-FEB-2003          0          135000 B

 

Elapsed: 00:00:00.01

 

 

SQL> show parameter db_name

 

NAME                                 TYPE        VALUE

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

db_name                              string      PS89TECH

SQL> SELECT ksppinm, ksppstvl, ksppdesc

FROM x$ksppi x, x$ksppcv y

WHERE x.indx = y.indx

AND translate(ksppinm,'_','#') like '_unnest_subquery%';  2    3    4

 

KSPPINM              KSPPSTVL   KSPPDESC

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

_unnest_subquery     FALSE      enables unnesting of complex subqueries

 
 
在ps89prod上更改其值,查询执行计划跟ps89tech上一致,时间也相应缩短
 
SQL> alter session set current_schema=sysadm;
Session altered.
SQL> alter session set "_unnest_subquery"=false;
Session altered.
SQL> set autotrace trace exp
SQL> set timing on
SQL> show parameter db_name
SQL> select   *
FROM   PS_Benefits_VW BV
WHERE  BV.Emplid = '10679'
  2    3    4  AND  BV.Plan_Type= '21'
  5  AND  BV.Pay_End_Dt = (SELECT  MAX(BV2.Pay_End_Dt)
  6  FROM  PS_Benefits_VW BV2
  7  WHERE BV2.Emplid    = BV.Emplid
  8  AND BV2.EMPL_RCD = BV.EMPL_RCD
  9  AND BV2.Plan_Type = BV.Plan_Type
 10  AND BV2.Benefit_Plan = BV.Benefit_Plan
 11  AND BV2.Pay_End_Dt <= sysdate)
 12  AND  BV.EMPL_RCD = (SELECT   MAX(BV3.EMPL_RCD)
 13  FROM  PS_Benefits_VW BV3
WHERE BV3.Emplid    = BV.Emplid
 14   15  AND BV3.EMPL_RCD = BV.EMPL_RCD
 16  AND BV3.Plan_Type = BV.Plan_Type
AND BV3.Benefit_Plan = BV.Benefit_Plan
 17   18  AND BV3.Pay_End_Dt = BV.Pay_End_Dt);
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3181770671
---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |     1 |    89 |   160K  (1)| 00:32:08 |
|   1 |  NESTED LOOPS                        |                  |       |       |            |          |
|   2 |   NESTED LOOPS                       |                  |     1 |    89 |   834   (1)| 00:00:11 |
|   3 |    TABLE ACCESS BY INDEX ROWID       | PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|*  4 |     INDEX SKIP SCAN                  | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | PS_PAY_DEDUCTION |     1 |       |     3   (0)| 00:00:01 |
|   6 |     SORT AGGREGATE                   |                  |     1 |    80 |            |          |
|*  7 |      FILTER                          |                  |       |       |            |          |
|   8 |       NESTED LOOPS                   |                  |     1 |    80 |   738   (1)| 00:00:09 |
|*  9 |        TABLE ACCESS BY INDEX ROWID   | PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 10 |         INDEX SKIP SCAN              | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|* 11 |        INDEX RANGE SCAN              | PS_PAY_DEDUCTION |     1 |    42 |     3   (0)| 00:00:01 |
|  12 |         SORT AGGREGATE               |                  |     1 |    78 |            |          |
|  13 |          NESTED LOOPS                |                  |   157 | 12246 |   738   (1)| 00:00:09 |
|* 14 |           TABLE ACCESS BY INDEX ROWID| PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 15 |            INDEX SKIP SCAN           | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|* 16 |           INDEX RANGE SCAN           | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|  17 |         SORT AGGREGATE               |                  |     1 |    42 |            |          |
|* 18 |          INDEX RANGE SCAN            | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
|  19 |     SORT AGGREGATE                   |                  |     1 |    78 |            |          |
|  20 |      NESTED LOOPS                    |                  |   157 | 12246 |   738   (1)| 00:00:09 |
|* 21 |       TABLE ACCESS BY INDEX ROWID    | PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 22 |        INDEX SKIP SCAN               | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|* 23 |       INDEX RANGE SCAN               | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|  24 |     SORT AGGREGATE                   |                  |     1 |    42 |            |          |
|* 25 |      INDEX RANGE SCAN                | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
|  26 |     SORT AGGREGATE                   |                  |     1 |    80 |            |          |
|* 27 |      FILTER                          |                  |       |       |            |          |
|  28 |       NESTED LOOPS                   |                  |     1 |    80 |     7   (0)| 00:00:01 |
|* 29 |        TABLE ACCESS BY INDEX ROWID   | PS_PAY_CHECK     |     1 |    38 |     5   (0)| 00:00:01 |
|* 30 |         INDEX RANGE SCAN             | PS1PAY_CHECK     |     1 |       |     4   (0)| 00:00:01 |
|* 31 |        INDEX RANGE SCAN              | PS_PAY_DEDUCTION |     1 |    42 |     3   (0)| 00:00:01 |
|  32 |         SORT AGGREGATE               |                  |     1 |    78 |            |          |
|  33 |          NESTED LOOPS                |                  |   157 | 12246 |   738   (1)| 00:00:09 |
|* 34 |           TABLE ACCESS BY INDEX ROWID| PS_PAY_CHECK     |   183 |  6954 |   188   (0)| 00:00:03 |
|* 35 |            INDEX SKIP SCAN           | PSAPAY_CHECK     |   183 |       |     7   (0)| 00:00:01 |
|* 36 |           INDEX RANGE SCAN           | PS_PAY_DEDUCTION |     1 |    40 |     3   (0)| 00:00:01 |
|  37 |         SORT AGGREGATE               |                  |     1 |    42 |            |          |
|* 38 |          INDEX RANGE SCAN            | PS_PAY_DEDUCTION |     1 |    42 |     4   (0)| 00:00:01 |
|  39 |   TABLE ACCESS BY INDEX ROWID        | PS_PAY_DEDUCTION |     1 |    51 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."EMPLID"='10679')
       filter("A"."EMPLID"='10679')
   5 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK"
              AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "B"."PLAN_TYPE"='21')
       filter("B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"= (SELECT MAX("B"."PAY_END_DT") FROM
              SYSADM."PS_PAY_DEDUCTION" "SYS_ALIAS_14",SYSADM."PS_PAY_CHECK" "SYS_ALIAS_2" WHERE :B1<>'00' AND
              "A"."EMPL_RCD"=:B2 AND "A"."PAY_END_DT"<=SYSDATE@! AND "A"."EMPLID"=:B3 AND
              "B"."BENEFIT_PLAN"=:B4 AND "B"."PLAN_TYPE"=:B5 AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR"
              AND "A"."SEPCHK"="B"."SEPCHK" AND "A"."LINE_NUM"="B"."LINE_NUM" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."COMPANY"="B"."COMPANY" AND "B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"<=SYSDATE@! AND
              "B"."PAY_END_DT"= (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION"
              "D",SYSADM."PS_PAY_CHECK" "C" WHERE "C"."EMPL_RCD"=:B6 AND
              "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD') AND "C"."EMPLID"=:B7 AND
              "D"."BENEFIT_PLAN"=:B8 AND "D"."PLAN_TYPE"=:B9 AND "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR"
              AND "D"."SEPCHK"="C"."SEPCHK" AND "D"."LINE_NUM"="C"."LINE_NUM" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."COMPANY"="C"."COMPANY" AND "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-M
              M-DD')) AND "B"."DED_CLASS"= (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E"
              WHERE "E"."BENEFIT_PLAN"=:B10 AND "E"."PLAN_TYPE"=:B11 AND "E"."BENEFIT_RCD_NBR"=:B12 AND
              "E"."SEPCHK"=:B13 AND "E"."LINE_NUM"=:B14 AND "E"."PAGE_NUM"=:B15 AND "E"."OFF_CYCLE"=:B16 AND
              "E"."PAY_END_DT"=:B17 AND "E"."PAYGROUP"=:B18 AND "E"."COMPANY"=:B19)) AND "B"."PAY_END_DT"=
              (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION" "D",SYSADM."PS_PAY_CHECK" "C" WHERE
              "C"."EMPL_RCD"=:B20 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')
              AND "C"."EMPLID"=:B21 AND "D"."BENEFIT_PLAN"=:B22 AND "D"."PLAN_TYPE"=:B23 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND
              "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND
              "D"."PAYGROUP"="C"."PAYGROUP" AND "D"."COMPANY"="C"."COMPANY" AND
              "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "B"."DED_CLASS"=
              (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"=:B24
              AND "E"."PLAN_TYPE"=:B25 AND "E"."BENEFIT_RCD_NBR"=:B26 AND "E"."SEPCHK"=:B27 AND
              "E"."LINE_NUM"=:B28 AND "E"."PAGE_NUM"=:B29 AND "E"."OFF_CYCLE"=:B30 AND "E"."PAY_END_DT"=:B31
              AND "E"."PAYGROUP"=:B32 AND "E"."COMPANY"=:B33) AND "A"."EMPL_RCD"= (SELECT MAX("A"."EMPL_RCD")
              FROM SYSADM."PS_PAY_DEDUCTION" "SYS_ALIAS_14",SYSADM."PS_PAY_CHECK" "SYS_ALIAS_2" WHERE
              :B34<>'00' AND "A"."EMPLID"=:B35 AND "A"."EMPL_RCD"=:B36 AND "A"."PAY_END_DT"=:B37 AND
              "B"."BENEFIT_PLAN"=:B38 AND "B"."PLAN_TYPE"=:B39 AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR"
              AND "A"."SEPCHK"="B"."SEPCHK" AND "A"."LINE_NUM"="B"."LINE_NUM" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."COMPANY"="B"."COMPANY" AND "B"."PAY_END_DT"=:B40 AND "B"."PLAN_TYPE"<>'00' AND
              "B"."PAY_END_DT"= (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION"
              "D",SYSADM."PS_PAY_CHECK" "C" WHERE "C"."EMPL_RCD"=:B41 AND
              "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD') AND "C"."EMPLID"=:B42
              AND "D"."BENEFIT_PLAN"=:B43 AND "D"."PLAN_TYPE"=:B44 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND
              "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND
              "D"."PAYGROUP"="C"."PAYGROUP" AND "D"."COMPANY"="C"."COMPANY" AND
              "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "B"."DED_CLASS"=
              (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"=)
   7 - filter(:B1<>'00')
   9 - filter("A"."EMPL_RCD"=:B1 AND "A"."PAY_END_DT"<=SYSDATE@!)
  10 - access("A"."EMPLID"=:B1)
       filter("A"."EMPLID"=:B1)
  11 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK"
              AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "B"."PLAN_TYPE"=:B1 AND
              "B"."BENEFIT_PLAN"=:B2)
       filter("B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"<=SYSDATE@! AND "B"."PAY_END_DT"=
              (SELECT MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION" "D",SYSADM."PS_PAY_CHECK" "C" WHERE
              "C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')
              AND "C"."EMPLID"=:B2 AND "D"."BENEFIT_PLAN"=:B3 AND "D"."PLAN_TYPE"=:B4 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND
              "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND
              "D"."PAYGROUP"="C"."PAYGROUP" AND "D"."COMPANY"="C"."COMPANY" AND
              "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "B"."DED_CLASS"=
              (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"=:B5 AND
              "E"."PLAN_TYPE"=:B6 AND "E"."BENEFIT_RCD_NBR"=:B7 AND "E"."SEPCHK"=:B8 AND "E"."LINE_NUM"=:B9
              AND "E"."PAGE_NUM"=:B10 AND "E"."OFF_CYCLE"=:B11 AND "E"."PAY_END_DT"=:B12 AND
              "E"."PAYGROUP"=:B13 AND "E"."COMPANY"=:B14))
  14 - filter("C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'Y
              YYY-MM-DD'))
  15 - access("C"."EMPLID"=:B1)
       filter("C"."EMPLID"=:B1)
  16 - access("D"."COMPANY"="C"."COMPANY" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."SEPCHK"="C"."SEPCHK"
              AND "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."PLAN_TYPE"=:B1 AND
              "D"."BENEFIT_PLAN"=:B2)
       filter("D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
  18 - access("E"."COMPANY"=:B1 AND "E"."PAYGROUP"=:B2 AND "E"."PAY_END_DT"=:B3 AND
              "E"."OFF_CYCLE"=:B4 AND "E"."PAGE_NUM"=:B5 AND "E"."LINE_NUM"=:B6 AND "E"."SEPCHK"=:B7 AND
              "E"."BENEFIT_RCD_NBR"=:B8 AND "E"."PLAN_TYPE"=:B9 AND "E"."BENEFIT_PLAN"=:B10)
  21 - filter("C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'Y
              YYY-MM-DD'))
  22 - access("C"."EMPLID"=:B1)
       filter("C"."EMPLID"=:B1)
  23 - access("D"."COMPANY"="C"."COMPANY" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."SEPCHK"="C"."SEPCHK"
              AND "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."PLAN_TYPE"=:B1 AND
              "D"."BENEFIT_PLAN"=:B2)
       filter("D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
  25 - access("E"."COMPANY"=:B1 AND "E"."PAYGROUP"=:B2 AND "E"."PAY_END_DT"=:B3 AND
              "E"."OFF_CYCLE"=:B4 AND "E"."PAGE_NUM"=:B5 AND "E"."LINE_NUM"=:B6 AND "E"."SEPCHK"=:B7 AND
              "E"."BENEFIT_RCD_NBR"=:B8 AND "E"."PLAN_TYPE"=:B9 AND "E"."BENEFIT_PLAN"=:B10)
  27 - filter(:B1<>'00')
  29 - filter("A"."EMPL_RCD"=:B1)
  30 - access("A"."EMPLID"=:B1 AND "A"."PAY_END_DT"=:B2)
       filter("A"."PAY_END_DT"=:B1)
  31 - access("A"."COMPANY"="B"."COMPANY" AND "A"."PAYGROUP"="B"."PAYGROUP" AND
              "A"."PAY_END_DT"="B"."PAY_END_DT" AND "A"."OFF_CYCLE"="B"."OFF_CYCLE" AND
              "A"."PAGE_NUM"="B"."PAGE_NUM" AND "A"."LINE_NUM"="B"."LINE_NUM" AND "A"."SEPCHK"="B"."SEPCHK"
              AND "A"."BENEFIT_RCD_NBR"="B"."BENEFIT_RCD_NBR" AND "B"."PLAN_TYPE"=:B1 AND
              "B"."BENEFIT_PLAN"=:B2)
       filter("B"."PAY_END_DT"=:B1 AND "B"."PLAN_TYPE"<>'00' AND "B"."PAY_END_DT"= (SELECT
              MAX("C"."PAY_END_DT") FROM SYSADM."PS_PAY_DEDUCTION" "D",SYSADM."PS_PAY_CHECK" "C" WHERE
              "C"."EMPL_RCD"=:B2 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')
              AND "C"."EMPLID"=:B3 AND "D"."BENEFIT_PLAN"=:B4 AND "D"."PLAN_TYPE"=:B5 AND
              "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."SEPCHK"="C"."SEPCHK" AND
              "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."PAGE_NUM"="C"."PAGE_NUM" AND
              "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND "D"."PAY_END_DT"="C"."PAY_END_DT" AND
              "D"."PAYGROUP"="C"."PAYGROUP" AND "D"."COMPANY"="C"."COMPANY" AND
              "D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')) AND "B"."DED_CLASS"=
              (SELECT MIN("E"."DED_CLASS") FROM SYSADM."PS_PAY_DEDUCTION" "E" WHERE "E"."BENEFIT_PLAN"=:B6 AND
              "E"."PLAN_TYPE"=:B7 AND "E"."BENEFIT_RCD_NBR"=:B8 AND "E"."SEPCHK"=:B9 AND "E"."LINE_NUM"=:B10
              AND "E"."PAGE_NUM"=:B11 AND "E"."OFF_CYCLE"=:B12 AND "E"."PAY_END_DT"=:B13 AND
              "E"."PAYGROUP"=:B14 AND "E"."COMPANY"=:B15))
  34 - filter("C"."EMPL_RCD"=:B1 AND "C"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'Y
              YYY-MM-DD'))
  35 - access("C"."EMPLID"=:B1)
       filter("C"."EMPLID"=:B1)
  36 - access("D"."COMPANY"="C"."COMPANY" AND "D"."PAYGROUP"="C"."PAYGROUP" AND
              "D"."PAY_END_DT"="C"."PAY_END_DT" AND "D"."OFF_CYCLE"="C"."OFF_CYCLE" AND
              "D"."PAGE_NUM"="C"."PAGE_NUM" AND "D"."LINE_NUM"="C"."LINE_NUM" AND "D"."SEPCHK"="C"."SEPCHK"
              AND "D"."BENEFIT_RCD_NBR"="C"."BENEFIT_RCD_NBR" AND "D"."PLAN_TYPE"=:B1 AND
              "D"."BENEFIT_PLAN"=:B2)
       filter("D"."PAY_END_DT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD'))
  38 - access("E"."COMPANY"=:B1 AND "E"."PAYGROUP"=:B2 AND "E"."PAY_END_DT"=:B3 AND
              "E"."OFF_CYCLE"=:B4 AND "E"."PAGE_NUM"=:B5 AND "E"."LINE_NUM"=:B6 AND "E"."SEPCHK"=:B7 AND
              "E"."BENEFIT_RCD_NBR"=:B8 AND "E"."PLAN_TYPE"=:B9 AND "E"."BENEFIT_PLAN"=:B10)
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24152604/viewspace-712472/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-06-26

  • 博文量
    28
  • 访问量
    33936