INSERT . . . SELECT with Join Index
The fastest way of processing inserts into a table with a join index is:
1 Use FastLoad to load the rows into a staging table with no indexes or join indexes defined.
2 Do an INSERT . . . SELECT from the staging table into the target table with the join index.
If the target table has multiple join indexes defined, the Optimizer may choose to use reusable spool during join index maintenance, if applicable.
Teradata Database supports bulk SQL error handling for MERGE and INSERT . . . SELECT statements. This permits bulk SQL inserts and updates to be done without the target table restrictions that apply to Teradata Database load utilities.
Load utilities are restricted from unique indexes, join or hash indexes, referential constraints, triggers, and LOBs on the target table.
USI and referential integrity (RI) violations cause the request to abort and rollback after all these violations and all other supported error conditions are logged.