all-AMPs MERGE into <table> from Spool N (Last Use) - Huge Estimation Difference

Database

all-AMPs MERGE into <table> from Spool N (Last Use) - Huge Estimation Difference

Apologies if  might have already been answered in this forum, but my 5 minute search did not get me anywhere. Hence a new thread.

Running SQLA 14.01 on TD14

Issue:

I'm running a query, the SELECT part estimates a completion time "The total estimated time is 0.71 seconds". The query results come back withing a second. 4 tables are LOJ'ed and all the tables are on the same PI. No issue here.

But when I'm inserting this information into a physical table there's a couple of additional step at the end of the  Explain plan, below.

Then we do a SORT to order Spool 1 by the hash code of (<dbname>.<table>.<pi_column>).  The size

of Spool 1 is estimated with low confidence to be 6,334,626 rows (2,090,426,580 bytes).  The estimated time for this step is 0.26 seconds.
 We do an all-AMPs MERGE into <table> from Spool 1 (Last Use).  The size is estimated with low confidence to

 be 6,334,626 rows.  The estimated time for this step is 4 minutes and 3 seconds. 

This make the query go into a long queue and takes 20 minutes to complete. 

Question:

How can an INSERT step to the same SELECT query make such a huge difference when the SELECT part gives me the results so fast?

Appretiate any help..!! Promise a pint of bitter to anyone who could help :-)

2 REPLIES
N/A

Re: all-AMPs MERGE into <table> from Spool N (Last Use) - Huge Estimation Difference

This is usually caused by a bad Primary Index on a SET table resulting in a huge amount of duplicate row checks.

Check the number of rows per PI value of the target table.

Re: all-AMPs MERGE into <table> from Spool N (Last Use) - Huge Estimation Difference

Thanks for the quick responce Dieter.

I'm using a MS table and the PI is the same as the four tables in the join.

I split down the query and I was using a BETWEEN in one of the joins. Materialiezed the split query into sub-tables and then joined and all worked well.

Thanks.