INSERT . . . SELECT with FastLoad
Use the optimized INSERT . . . SELECT to manipulate FastLoad data:
1 FastLoad into a staging table.
2 INSERT . . . SELECT into the final table, manipulating the data as required.
FastLoad and INSERT . . . SELECT are faster than using an INMOD to manage data on the host. The host is a single bottleneck as opposed to parallel AMPs that populate temporary tables for reports or intermediate results.
Multiple source tables may populate the same target table. If the target table is empty before a request begins, all INSERT . . . SELECT statements in that request run in the optimized mode.
The staging table can be a No Primary Index (NoPI) table, which has rows that are not hash distributed, as the staging table. FastLoad runs faster inserting into a NoPI table because there is no sort and no row redistribution involved; rows are simply appended to the table evenly across all AMPs.
Use of NoPI reduces skew in intermediate tables that have no natural primary index, and is particularly useful for staging tables. However, because the rows of a NoPI table are not hashed-based, an INSERT/SELECT from a NoPI table to a PI table may be slower than INSERT/SELECT where the tables share the same PI definition. Consider NoPI tables for use with BI tools and applications that generate many intermediate tables
An INSERT . . . SELECT from a NoPI table to a PI table can be slower than an INSERT . . . SELECT from a PI table to a PI table with the same PI.