Thans everyone for help me to solve this problem . I will try it.
The following message is the explaination of sql statement for Dieter.
Explanation
-------------------------------------------------- | |
| | 1) | First, we lock a distinct PMART."pseudo table" for write on a RowHash to prevent global deadlock for PMART.TEMP_TXN_SHOPPING_TRAN.
| |
| | 2) | Next, we lock a distinct PMART."pseudo table" for read on a RowHash to prevent global deadlock for
PMART.TXN_SHOPPING_TRANSACTION_2003.
| |
| | 3) | We lock a distinct PDATA."pseudo table" for read on a RowHash to prevent global deadlock for PDATA.TXN_SHOPPING_TRAN_C.
| |
| | 4) | We lock PMART.TEMP_TXN_SHOPPING_TRAN for write, we lock PMART.TXN_SHOPPING_TRANSACTION_2003 for read, and we lock
PDATA.TXN_SHOPPING_TRAN_C for read.
| |
| | 5) | We execute the following steps in parallel.
| |
| | |
| | 1) | We do an all-AMPs RETRIEVE step from PMART.TXN_SHOPPING_TRANSACTION_2003 by way of an all-rows scan with a condition of
("(EXTRACT(YEAR FROM (PMART.TXN_SHOPPING_TRANSACTION_2003.TransactionProcessDT)))= 2003") into Spool 2, which is redistributed by hash code
to all AMPs. Then we do a SORT to order Spool 2 by row hash and the sort key in spool field1 eliminating duplicate rows. The input table
will not be cached in memory, but it is eligible for synchronized scanning. The result spool file will not be cached in memory. The size
of Spool 2 is estimated with no confidence to be 3,401,551 rows. The estimated time for this step is 59 minutes and 4 seconds.
| | |
| | |
| | 2) | We do an all-AMPs RETRIEVE step from PDATA.TXN_SHOPPING_TRAN_C by way of an all-rows scan with a condition of ("(EXTRACT(YEAR FROM
(PDATA.TXN_SHOPPING_TRAN_C.TransactionProcessDT )))= 2003") into Spool 3, which is redistributed by hash code to all AMPs. Then we do a
SORT to order Spool 3 by row hash and the sort key in spool field1 eliminating duplicate rows. The input table will not be cached in
memory, but it is eligible for synchronized scanning. The size of Spool 3 is estimated with no confidence to be 138,172 rows. The
estimated time for this step is 2 minutes and 24 seconds.
| | |
| | 6) | We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an all-rows scan, which is joined to Spool 3 (Last Use). Spool 2 and
Spool 3 are joined using an exclusion merge join, with a join condition of ("Field_1 = Field_1"). The result goes into Spool 1, which is
built locally on the AMPs. The size of Spool 1 is estimated to be 3,332,465 rows. The estimated time for this step is 2 minutes and 46
seconds.
| |
| | 7) | We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of an all-rows scan into Spool 4, which is redistributed by hash code
to all AMPs. Then we do a SORT to order Spool 4 by row hash. The result spool file will not be cached in memory. The size of Spool 4 is
estimated with no confidence to be 3,332,465 rows. The estimated time for this step is 4 hours and 16 minutes.
| |
| | 8) | We do a MERGE into PMART.TEMP_TXN_SHOPPING_TRAN from Spool 4 (Last Use).
| |
| | 9) | We spoil the parser's dictionary cache for the table.
| |
| | 10) | 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.
| |
