All Forums Database
nomadic_being 3 posts Joined 09/14
19 Sep 2014
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
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. 




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 :-)


dnoeth 4628 posts Joined 11/04
19 Sep 2014

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.


nomadic_being 3 posts Joined 09/14
21 Sep 2014

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.

You must sign in to leave a comment.