To insert data into the target table, the selected table, TEST01.web_clicks, has to be on the same AMP as the target table TEST01.Web_clicks_CharPI. In our case, as the PI of both the tables is different, the optimizer will prepare a plan to redistribute or duplicate the source table so that rows can be on the same AMP before the final insert.
If this extra step query runtime and other factors increase, this will drag the performance of the query.
This becomes FAST PATH INSERT SELECT when the target table is empty; having the same PI is an advantage as there's no redistribution necessary.
FASTPATH INSERT.SELECT:
- The target table must be empty
- All INSERT statements in the multistatement request must have the same target ...
