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 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.
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.