Strange problem.

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Tue, 06 Dec 2005 17:13:23 +0000
Message-ID: <1133889203.ba727e1adc34c4abde94a39bd7453edb@news.nntpserver.com>

I have an update that I'm trying to optimize.

UPDATE PS_TL_PAYABLE_TIME
   SET PAYABLE_STATUS = 'RP'
WHERE PAY_SYSTEM = 'NA'
   AND PAYABLE_STATUS NOT IN ('PD', 'DL')    AND PAYROLL_REQ_NUM <> 1
   AND EXISTS (

          SELECT 'X'
            FROM PS_TL_XREF_TBL A, PS_PY_XREF_WRK B
           WHERE A.PAY_SYSTEM = 'NA'
             AND A.SEQ_NBR = PS_TL_PAYABLE_TIME.SEQ_NBR
             AND B.XREF_NUM = A.XREF_NUM
             AND B.PROCESS_INSTANCE = :1)

The performance problem seems to be coming from the way the optimizer is adding in the EXISTS subquery. If I rewrite it to use an IN I get a 10x performance increase.

UPDATE PS_TL_PAYABLE_TIME
   SET PAYABLE_STATUS = 'RP'
WHERE PAY_SYSTEM = 'NA'
   AND PAYABLE_STATUS NOT IN ('PD', 'DL')    AND PAYROLL_REQ_NUM <> 1
   AND PS_TL_PAYABLE_TIME.SEQ_NBR IN (

          SELECT a.seq_nbr
            FROM PS_TL_XREF_TBL A, PS_PY_XREF_WRK B
           WHERE A.PAY_SYSTEM = 'NA'
             AND B.XREF_NUM = A.XREF_NUM
             AND B.PROCESS_INSTANCE = :1)

If I just add an UNNEST hint to the subquery in the original SQL though, I get the exact same execution plan as the IN version gets, but here's the strange part. Executing the hinted version runs 5x longer and does 5x the number of consistent gets as the IN version. How can this be when the execution plans are identical? The bind variable used is the same for both.

Here are the plans for the IN version, and the UNNEST version.

IN version

| 0 | UPDATE STATEMENT | | 1796 | 50288 | 659 (0)|
| 1 | UPDATE | PS_TL_PAYABLE_TIME | | | |

|*  2 |   TABLE ACCESS BY INDEX ROWID| PS_TL_PAYABLE_TIME  |     1 |    15 |     2  (50)|


|   3 |    NESTED LOOPS              |                     |  1796 | 50288 |   659   (0)|

|   4 |     VIEW                     | VW_NSO_1            |  1796 | 23348 |            |

|   5 |      SORT UNIQUE             |                     |  1796 | 57472 |            |

|   6 |       NESTED LOOPS           |                     |  1796 | 57472 |   112   (1)|


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
|*  7 |        TABLE ACCESS FULL     | PS_TL_XREF_TBL      |   259K|  4817K|   110   (0)|
|*  8 |        INDEX UNIQUE SCAN     | PS_PY_XREF_WRK      |     1 |    13 |            |
|*  9 |     INDEX RANGE SCAN         | PSATL_PAYABLE_TIME  |     1 |       |            |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   2 - filter("PS_TL_PAYABLE_TIME"."PAY_SYSTEM"='NA' AND
              "PS_TL_PAYABLE_TIME"."PAYABLE_STATUS"<>'PD' AND
              "PS_TL_PAYABLE_TIME"."PAYABLE_STATUS"<>'DL' AND

PLAN_TABLE_OUTPUT

              "PS_TL_PAYABLE_TIME"."PAYROLL_REQ_NUM"<>1)
   7 - filter("A"."PAY_SYSTEM"='NA')
   8 - access("B"."PROCESS_INSTANCE"=TO_NUMBER(:Z) AND "B"."XREF_NUM"="A"."XREF_NUM")
   9 - access("PS_TL_PAYABLE_TIME"."SEQ_NBR"="VW_NSO_1"."$nso_col_1")

==========================================================================================

UNNEST version

| 0 | UPDATE STATEMENT | | 1796 | 50288 | 659 (0)|
| 1 | UPDATE | PS_TL_PAYABLE_TIME | | | |

|*  2 |   TABLE ACCESS BY INDEX ROWID| PS_TL_PAYABLE_TIME  |     1 |    15 |     2  (50)|


|   3 |    NESTED LOOPS              |                     |  1796 | 50288 |   659   (0)|

|   4 |     VIEW                     | VW_SQ_1             |  1796 | 23348 |            |

|   5 |      SORT UNIQUE             |                     |  1796 | 57472 |            |

|   6 |       NESTED LOOPS           |                     |  1796 | 57472 |   112   (1)|


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
|*  7 |        TABLE ACCESS FULL     | PS_TL_XREF_TBL      |   259K|  4817K|   110   (0)|
|*  8 |        INDEX UNIQUE SCAN     | PS_PY_XREF_WRK      |     1 |    13 |            |
|*  9 |     INDEX RANGE SCAN         | PSATL_PAYABLE_TIME  |     1 |       |            |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   2 - filter("PS_TL_PAYABLE_TIME"."PAY_SYSTEM"='NA' AND
              "PS_TL_PAYABLE_TIME"."PAYABLE_STATUS"<>'PD' AND
              "PS_TL_PAYABLE_TIME"."PAYABLE_STATUS"<>'DL' AND

PLAN_TABLE_OUTPUT

              "PS_TL_PAYABLE_TIME"."PAYROLL_REQ_NUM"<>1)
   7 - filter("A"."PAY_SYSTEM"='NA')
   8 - access("B"."PROCESS_INSTANCE"=TO_NUMBER(:Z) AND "B"."XREF_NUM"="A"."XREF_NUM")
   9 - access("VW_SQ_1"."SEQ_NBR"="PS_TL_PAYABLE_TIME"."SEQ_NBR")

. Received on Tue Dec 06 2005 - 11:13:23 CST