explain plan

Database
Enthusiast

explain plan

I am running the below update query in two different servers

 


UPDATE PRTY_SCR FROM
(SELECT distinct MODL_ID,MODL_RUN_ID,PRTY_ID,LVL_NUM,EDW_STRT_DTTM, PRTY_SCR_VAL,

max(EDW_STRT_DTTM) over (partition by MODL_ID,MODL_RUN_ID,PRTY_ID ORDER BY EDW_STRT_DTTM ASC rows between 1 following and 1 following) - INTERVAL '1' SECOND
as lead
FROM PRTY_SCR
) a
set
EDW_END_DTTM=A.lead
WHERE PRTY_SCR.MODL_ID=A.MODL_ID
and PRTY_SCR.MODL_RUN_ID=A.MODL_RUN_ID
AND PRTY_SCR.PRTY_ID=A.PRTY_ID
AND PRTY_SCR.EDW_STRT_DTTM = A.EDW_STRT_DTTM
AND PRTY_SCR.EDW_END_DTTM=TO_DATE('12/31/9999 23:59:59.999999','MM/DD/YYYY HH24:MI:SS.US')
AND PRTY_SCR.LVL_NUM = A.LVL_NUM
AND PRTY_SCR.PRTY_SCR_VAL = A.PRTY_SCR_VAL
and lead is not null;

 

FYI - "lead" in the above query always returns null. (This is how the data is in the table)

 

One server gives an error " UPDATE Failed. 9134:  Unknown character in format string "

and the other server doesnt give any error (however no rows are updated by the query and this is expected)

 

Here are the explain plans from both the servers

server 1 - Error " UPDATE Failed. 9134:  Unknown character in format string "

 

1) First, we lock a distinct DB_EDW."pseudo table" for write on 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_SCR by way
of an all-rows scan with no residual conditions into Spool 5 (Last
Use), which is built locally on the AMPs. The result rows are put
into Spool 1 (all_amps), which is redistributed by hash code to
all AMPs. Then we do a SORT to order Spool 1 by the sort key in
spool field1 eliminating duplicate rows. The size is estimated
with low confidence to be 4,392 rows (1,370,304 bytes). The
estimated time for this step is 0.02 seconds.
4) We do an all-AMPs RETRIEVE step from DB_EDW.PRTY_SCR by way of an
all-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/YYYY
HH24: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 9
is estimated with no confidence to be 31,680 rows (3,326,400
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 an
all-rows scan, which is joined to Spool 1 (Last Use) by way of an
all-rows scan with a condition of ("(NOT (a.LEAD IS NULL )) AND
(NOT (a.LVL_NUM IS NULL ))"). Spool 9 and Spool 1 are joined
using 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 Spool
8 (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 587
rows (81,006 bytes). The estimated time for this step is 0.01
seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in 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 write
on 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_SCR
by way of an all-rows scan with no residual conditions into Spool
5 (Last Use), which is built locally on the AMPs. The result rows
are put into Spool 1 (all_amps), which is redistributed by hash
code to all AMPs. Then we do a SORT to order Spool 1 by the sort
key in spool field1 eliminating duplicate rows. The size is
estimated 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 of
an 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 to
all AMPs. Then we do a SORT to order Spool 9 by row hash. The
size 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 a
RowHash match scan, which is joined to DB_EDW.PRTY_SCR by
way 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/YYYY
HH24:MI:SS.US'))) AND (NOT (DB_EDW.PRTY_SCR.PRTY_SCR_VAL IS
NULL ))"). Spool 9 and DB_EDW.PRTY_SCR are joined using a
merge 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 )))))"). The
result goes into Spool 8 (all_amps), which is built locally on the
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
391 rows (10,948 bytes). The estimated time for this step is 0.03
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
391 rows (53,958 bytes). The estimated time for this step is 0.00
seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in 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?

 

1 REPLY
Highlighted
Teradata Employee

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.