There's more...

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 table.
• Solution: Due to secondary index on target table present insert into table becomes slow, drop and re create index after the insert is completed.