This is the first method to optimize the insert.select. There are some due considerations that need to be acknowledged while changing the PI:
- Business needs
- Uniqueness of the PI column
- Usage of PI column in select
Following are some tasks that you need to do if you find issues with insert.select statements:
- Problem: Query stuck on MERGE step of a INSERT.SELECT statement. We do a MERGE into table [TBId=0x1767] from Spool 9.
- Issue: Bad primary index defined on target table (SET in nature). PI has multiple duplicate/skew values which are causing duplicate row check.
- Solution: Change the PI to the least skewed distribution values. Also, try creating a MULTISET table instead of a SET table.
- Problem: Secondary index on the target ...
