Why is this resulting in redistribution

UDA
Enthusiast

Why is this resulting in redistribution

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.
1 REPLY
Enthusiast

Re: Why is this resulting in redistribution

Problem solved.

The problem was that I was using UNION instead of UNION ALL, which was causing a duplicate row check.