1) First, we lock a distinct DB_EDW."pseudo table" for write on aRowHash to prevent global deadlock for DB_EDW.PRTY_SCR.2) Next, we lock DB_EDW.PRTY_SCR for write.3) We do an all-AMPs STAT FUNCTION step from DB_EDW.PRTY_SCR by wayof an all-rows scan with no residual conditions into Spool 5 (LastUse), which is built locally on the AMPs. The result rows are putinto Spool 1 (all_amps), which is redistributed by hash code toall AMPs. Then we do a SORT to order Spool 1 by the sort key inspool field1 eliminating duplicate rows. The size is estimatedwith low confidence to be 4,392 rows (1,370,304 bytes). Theestimated time for this step is 0.02 seconds.4) We do an all-AMPs RETRIEVE step from DB_EDW.PRTY_SCR by way of anall-rows scan with a condition of ("((CAST((DB_EDW.PRTY_SCR.EDW_END_DTTM) AS DATE))=(TD_SYSFNLIB.TO_DATE ('12/31/9999 23:59:59.999999', 'MM/DD/YYYYHH24:MI:SS.US'))) AND ((NOT (DB_EDW.PRTY_SCR.LVL_NUM IS NULL ))AND (NOT (DB_EDW.PRTY_SCR.PRTY_SCR_VAL IS NULL )))") into Spool 9(all_amps), which is duplicated on all AMPs. The size of Spool 9is estimated with no confidence to be 31,680 rows (3,326,400bytes). The estimated time for this step is 0.02 seconds.5) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of anall-rows scan, which is joined to Spool 1 (Last Use) by way of anall-rows scan with a condition of ("(NOT (a.LEAD IS NULL )) AND(NOT (a.LVL_NUM IS NULL ))"). Spool 9 and Spool 1 are joinedusing a dynamic hash join, with a join condition of ("(MODL_ID =MODL_ID) AND ((MODL_RUN_ID = MODL_RUN_ID) AND ((PRTY_ID = PRTY_ID)AND ((EDW_STRT_DTTM = EDW_STRT_DTTM) AND ((LVL_NUM = LVL_NUM) AND(PRTY_SCR_VAL = PRTY_SCR_VAL )))))"). The result goes into Spool8 (all_amps), which is redistributed by hash code to all AMPs.Then we do a SORT to order Spool 8 by the sort key in spool field1.The size of Spool 8 is estimated with no confidence to be 587 rows(16,436 bytes). The estimated time for this step is 0.02 seconds.6) We do a MERGE Update to DB_EDW.PRTY_SCR from Spool 8 (Last Use)via ROWID. The size is estimated with no confidence to be 587rows (81,006 bytes). The estimated time for this step is 0.01seconds.7) Finally, we send out an END TRANSACTION step to all AMPs involvedin processing the request.-> No rows are returned to the user as the result of statement 1.The total estimated time is 0.07 seconds.

Server 2: NO error

1) First, we lock a distinct DB_EDW."pseudo table" for writeon a RowHash to prevent global deadlock for DB_EDW.PRTY_SCR.2) Next, we lock DB_EDW.PRTY_SCR for write.3) We do an all-AMPs STAT FUNCTION step from DB_EDW.PRTY_SCRby way of an all-rows scan with no residual conditions into Spool5 (Last Use), which is built locally on the AMPs. The result rowsare put into Spool 1 (all_amps), which is redistributed by hashcode to all AMPs. Then we do a SORT to order Spool 1 by the sortkey in spool field1 eliminating duplicate rows. The size isestimated with high confidence to be 3,908 rows (1,219,296 bytes).The estimated time for this step is 0.03 seconds.4) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way ofan all-rows scan with a condition of ("(NOT (a.LEAD IS NULL )) AND((NOT (a.PRTY_SCR_VAL IS NULL )) AND (NOT (a.LVL_NUM IS NULL )))")into Spool 9 (all_amps), which is redistributed by hash code toall AMPs. Then we do a SORT to order Spool 9 by row hash. Thesize of Spool 9 is estimated with high confidence to be 3,908 rows(429,880 bytes). The estimated time for this step is 0.02 seconds.5) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of aRowHash match scan, which is joined to DB_EDW.PRTY_SCR byway of a RowHash match scan with a condition of ("((CAST((DB_EDW.PRTY_SCR.EDW_END_DTTM) AS DATE))=(TD_SYSFNLIB.TO_DATE ('12/31/9999 23:59:59.999999', 'MM/DD/YYYYHH24:MI:SS.US'))) AND (NOT (DB_EDW.PRTY_SCR.PRTY_SCR_VAL ISNULL ))"). Spool 9 and DB_EDW.PRTY_SCR are joined using amerge join, with a join condition of ("(DB_EDW.PRTY_SCR.MODL_ID = MODL_ID) AND((DB_EDW.PRTY_SCR.MODL_RUN_ID = MODL_RUN_ID) AND((DB_EDW.PRTY_SCR.PRTY_ID = PRTY_ID) AND((DB_EDW.PRTY_SCR.EDW_STRT_DTTM = EDW_STRT_DTTM) AND((DB_EDW.PRTY_SCR.LVL_NUM = LVL_NUM) AND(DB_EDW.PRTY_SCR.PRTY_SCR_VAL = PRTY_SCR_VAL )))))"). Theresult goes into Spool 8 (all_amps), which is built locally on theAMPs. Then we do a SORT to order Spool 8 by the sort key in spoolfield1. The size of Spool 8 is estimated with no confidence to be391 rows (10,948 bytes). The estimated time for this step is 0.03seconds.6) We do a MERGE Update to DB_EDW.PRTY_SCR from Spool 8 (LastUse) via ROWID. The size is estimated with no confidence to be391 rows (53,958 bytes). The estimated time for this step is 0.00seconds.7) Finally, we send out an END TRANSACTION step to all AMPs involvedin processing the request.-> No rows are returned to the user as the result of statement 1.The total estimated time is 0.08 seconds.

I believe the error is because of TO_DATE('12/31/9999 23:59:59.999999','MM/DD/YYYY HH24:MI:SS.US')

I checked the columnstats, indexstats, multicolumnstats of PRTY_SCR table in both the server and the stats are same in both the servers.

But still why is the explain plan different? I think because of the sequence of filters shown in the explain plan the query fails in one of the servers? Can someone explain the resaon for this?

Re: explain plan

The TO_DATE function does not support fractional seconds (i.e. the "US" format element). But in this case, why not just use a TIMESTAMP constant?

timestamp'9999-12-31 23:59:59.999999'

The optimizer is free to choose the order of evaluation for filter criteria in the WHERE clause. In the case where it does not fail, the execution plan apparently eliminates all the rows without ever having to call TO_DATE.