ALL Amp MERGE step high estimated time.

Database
Enthusiast

ALL Amp MERGE step high estimated time.

Hi experts,

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

select cust_id,

                Pd_dt

                ,'01. All Up'                                                                                                              as Tst_Name

                , case

                                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

                                as feed_cd             

                , CT_5                                                                                    

                , Desc    

                , 'NA'                                                                                                                                                        as Tsted_Variable

                ,count(*)                                                                                                                  as Record_cnt

                ,sum(Outs_amt) as Outstandings

 

from VT_STG

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

     seconds.

  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.

1 REPLY
Supporter

Re: ALL Amp MERGE step high estimated time.

What is the PI of the two tables?

Do you need this PI for later SQLs? Did you try NoPI for the target table?