VT_tst and VT_STG are multiset and have the same PI , which is unique. The select part shows an estimated time of around 70 secs while the 'insert' shows more than 4 mins cauing it to go into a delay queue. I have tried removing the count(*) and the sum but it still shows the same 4 mins for teh last step. How can we decrease the estimated time so as not go into delay queue? And why is there so much of difference between Insert and Select when it is based on PI?
Explain insert into VT_tst
,'01. All Up' as Tst_Name
when gn_idin ('XXXX', 'YYYY-ZZZ-AAA') then 'XXX_ZZZAAA'
when H_Ind = 'Y' and feed_cd = 'YYYY' then 'YYYY_HFS'
else feed_cd end
, 'NA' as Tsted_Variable
,count(*) as Record_cnt
,sum(Outs_amt) as Outstandings
group by 1,2,3,4,5,6,7;
1) First, we do an all-AMPs RETRIEVE step from
.VT_STG by way of an all-rows scan with no
residual conditions into Spool 1 (all_amps), which is built
locally on the AMPs. Then we do a SORT to order Spool 1 by the
hash code of (.VT_STG.CUST_ID,
.VT_STG.PD_DT). The size of Spool 1 is
estimated with high confidence to be 33,156,122 rows (
7,228,034,596 bytes). The estimated time for this step is 1.06
2) Next, we do an all-AMPs MERGE into .VT_tst
from Spool 1 (Last Use). The size is estimated with high
confidence to be 33,156,122 rows. The estimated time for this
step is 4 minutes and 31 seconds.
3) We spoil the parser's dictionary cache for the table.
4) 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.