_unnest_subquery 参数引起执行计划改变

最新推荐文章于 2022-03-28 17:07:52 发布
最新推荐文章于 2022-03-28 17:07:52 发布
阅读量210 收藏
点赞数
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24152604/viewspace-712472/

cuijiao8006
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
alter session set _unnest_subquery=false；
zhaoyangjian724的专栏
07-04 409
alter session set _unnest_subquery=false; 不让它进行子查询改写 select count(*) from test o where object_id>(select avg(object_id) from test i where i.object_type=o.object_type); SQL> set linesize 200...
oracle三组难缠的hint no_unnest/unnest,push_subq,push_pred
Colin Lau Oracle
11-30 6022
最近在看一个哥们优化sql里面,使用到了几个特殊的hint，但我总是对此不解，所以针对此问题自己做了一些测试(参考了一些资料)  1. no_unnest, unnest  unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套(nest)在里面。  所以un_unnest双重否定代表肯定，即让子查询不展开，让它嵌套(nest)在里面。  如果一个查询中的where 中出现
控制子查询嵌套的参数_unnest_subquery
ciliting2867的博客
12-11 278
sys@EBANK>set linesize 132sys@EBANK>column name format a30sys@EBANK>column value format a25sys@EBANK>s...
mysql优化浅析
youjie10231的博客
05-07 752
1.查询优化 2.数据库结构优化 3.插入数据的优化                                                  插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。                         LOAD DATA INFILE                         禁用外键检查、禁用唯一性检查
Oracle 子查询展开(subquery unnesting)
07-10 4999
Oracle 子查询展开(subquery unnesting)
firebase_block_v3
04-20
每个event_name都有嵌套的key-value对，例如event_params.key和event_params.value，这意味着每个查询都将需要一些复杂的UNNEST操作与user_properties键值对相同​​，都高度嵌套该积木为您做什么？ 创建一种选择...
unnest:非常快速地将嵌套数据结构扩展和堆叠到 R data.frames 中
05-29
描述是一个 0 依赖 R 包，用于非常快速、单次复制和单遍、分层数据结构的取消嵌套。 unnest(..)函数将分层列表作为输入...安装install.packages( " unnest " )# ordevtools :: install_github( " vspinu/unnest " )
unique-random:生成连续唯一的随机数
04-30
唯一随机 生成连续唯一的随机数 对于不需要连续两次显示同一张幻灯片的幻灯片显示很有用。 安装 $ npm install unique-random 用法 import uniqueRandom from...-此模块具有可选的depth参数 生成加密强度高的随机字符串
pgsql-postal:PostgreSQL绑定libpostal
05-25
PostgreSQL邮政地址规范化器 动机 是一个C库，用于解析/规范世界各地的街道地址。 直接在PostgreSQL中拥有该功能可能会很有用。 此扩展用于此。 使用说明 由于支持JSONB，因此需要PostgreSQL 9.4及更高版本... unnest
tidyfast:快速有效的替代方法，可用于构建基于data.table #rdatatable #rstats的Tidyr函数
04-16
tidyfast v0.3.1 tidyfast的目标是在dplyr使用data.table为某些tidyr （和一些dplyr ）功能提供快速有效的替代方法。... 嵌套和dplyr::group_nest()嵌套（类似于dplyr::group_nest()和tidyr::unnest() ）： dt
oracle 子查询解嵌套和谓词推入（sqlsubquery unnest and pre_push）
FoKing的专栏
08-27 1968
Subquery Unnesting  In subquery unnesting, the optimizer transforms a nested query into an equivalent join statement, and then optimizes the join. This transformation enables the optimizer to ta
mysql unique_subquery_mysql 数据优化
weixin_42560067的博客
01-21 1092
在工作中我们经常要与数据库打交道，数据优化已经成为一个无法避免的问题，在这里先不考虑 mysql 服务器优化、连接数、主从同步等问题，只优化数据，以及更好的业务处理。PS：这里我们假设数据是百万级，超过百万级可能就有一些纸上谈兵了，因为我还没有在 mysql 上处理过千万级以上的数据。数据类型选择最合适的类型，来存储数据，当然这是废话，就只说几个点吧。1、小数类型为 decimal，禁止使用 fl...
MySQL入门 (九) : 子查询 Subquery
Just Code
08-30 2695
1 一个叙述中的查询叙述 子查询(subquery)是一种很常见的应用，不论是查询、新增、修改或删除都有可能出现。 子查询是一个放在左右刮号中的「SELECT」叙述，而这个查询叙述会放在另一个SQL叙述中。 在执行一些工作的时候，使用子查询可以简化SQL叙述。 以查询「人口比美国多的国家」来说，你要先执行下列查询美国人口数量的叙述： 知道美国人口数量以后，再执行下列的叙述就可以传回人口...
subquery 子查询
weixin_30536513的博客
09-28 233
#encoding: utf-8 from sqlalchemy import create_engine,Column,Integer,String,Float,func,and_,or_,Enum from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionma...
hive 中 unnest的使用
weixin_43995212的博客
09-02 3742
unnest 类似 hive中的侧视图 如数据： id infos 1 01,02,03 2 04,05,06 select id,info from test cross join unnest(split(info,',')) as info 输出如下 1 01 1 02 1 03 2 04 2 05 2 06
Doris ODBC外表使用
枪枪枪的博客
03-28 2354
文章目录PostgreSQL参考资料开始：配置ODBC驱动安装Postgresql ODBC驱动postgresql实现mysql中的show create table XXX PostgreSQL Doris Version:Baidu Palo 0.15.1 参考资料 Apache Doris ODBC外表之Postgresql使用指南 https://zhuanlan.zhihu.com/p/452442524 Linux/Unix ODBC的原理、安装、配置和编程 http://blog.china
***unnest语法
u014765410的博客
07-17 1077
参考博文： unnest语法
配置模拟外网环境开发网站的方法
emtit2008的专栏
12-16 1560
           相信很多网站开发者都会碰到这样的情况，网站在本地开发完成后，上传达到虚拟主机的空间，但出现的问题是路径不正确。        因为很多程序员在开发时都会在Apache指定的网站目录下建一个文件夹放网站，当在本机测试时就是输入http://127.0.0.1/web1/这样访问网站，但上传到网站后是用域名的，这样就会造成路径不正确。 解决方法：      在
20 查询变换(subquery unnesting) --优化主题系列
leo0805的博客
10-13 621
子查询非嵌套(subquery unnesting) 在学习subquery unnesting之前，先深入理解 执行计划中的FILTER，可以这样说，绝大部分复杂的子查询，性能问题基本上都是出现在FILTER上面。现在举个例子解释一下什么是FILTER。(你们要模拟就自己创建一个test表，数据从dba_objects复制)
unnest与string_to_array结合，无法处理null数据
最新发布
06-03
确实，`unnest` 函数无法处理包含 `NULL` 值的数组。如果需要处理包含 `NULL` 值的数组，可以将字符串先使用 `string_to_array` 函数转换为数组，然后使用 `unnest` 函数展开数组元素，并使用 `COALESCE` 函数将 `...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值