I am selecting data from 2 tables using a union statement into a third table. One of the source tables is a permanent table, the other is a volatile temp table. The target table is a permanent table.
All the tables have the same NUPPI and paritioning.
One Source table (the permanent one), and the target table also have a USI.
The explain (I took this from PMON) show multiple redistributions. Since the PIs for al the tables involved are identical, I would expect everything to be amp-local.
Can anyone explain why this is happening. I am trying to insert about 10M rows into a table that has about 7M rows in it. My intention was to build this result set, then rename the tables to avoid transient logging. However, all the redistribution is taking a long time.
Here's the explain. Spool 60615 is the temp source table.
---- 1 First, lock staging."pseudo table" for write on a row hash. 2 Next, we lock development."pseudo table" for read on a row hash. 3 We lock staging.page_view_wrk for write and we lock development.page_view for read. 4 We do an All-AMPs RETRIEVE step from development.page_view by way of an all-rows scan into Spool 18330, which is redistributed by hash code to all AMPs. 5 We do an All-AMPs RETRIEVE step from Spool 60615 by way of an all-rows scan into Spool 18330, which is redistributed by hash code to all AMPs. 6 We do an All-AMPs RETRIEVE step from Spool 18330 (Last Use) by way of an all-rows scan into Spool 18331, which is redistributed by hash code to all AMPs. 7 We do a MERGE into table page_view_wrk from Spool 18331. 8 We Spoil the p****r's dictionary cache for the table. 9 We send out an END TRANSACTION step to all AMPs involved in processing the request.