The extended MultiLoad protocol is an alternative way to load data into a target table when the traditional MultiLoad protocol cannot be used because of a restriction on the target table.
The Teradata Database decides if a job uses the traditional or extended MultiLoad protocol. If the job can use the traditional MultiLoad protocol, the job uses it. If the job cannot use the traditional MultiLoad protocol, the job uses the extended MultiLoad protocol. The user does not select which protocol the job uses.
Below are the jobs that use the extended MultiLoad protocol:
- The target table has a join index, hash index, unique secondary index, referential integrity, or trigger.
- The target table has no primary index.
- The target table has a Large Object (LOB) column(s). A LOB column can be a Binary Large Object (BLOB) or a Character Large Object (CLOB).
- The target table has XML or JSON column(s).
The following table shows differences between the traditional and extended MultiLoad protocols from the Update operator's perspective:
|For...||Traditional MultiLoad Protocol . . .||Extended MultiLoad Protocol . . .|
|Sessions||uses MultiLoad sessions.||uses SQL sessions.|
|DML statements||supports multiple DML statement per DML group.||supports only DML statement or an Upsert statement per DML group.|
|Buffer size (in KB)||supports up to 16384 KB.||N/A|
|Pack factor||N/A||supports up to 16383 data records per an Array Insert statement.|
|Work table||can use 1 or more work tables.||uses only the first work table.|
|Delete task||supports delete task.||does not support delete task.|
|Acquisition phase||uses data parcels to populate the staging work table.||uses the Array Insert statement to populate the staging work table.|
|Application phase||uses the EXEC MLOAD request to apply the rows in the staging work table to the target table.||uses the Merge Into request to apply the rows in the staging work table to the target table.|
|Performance||is faster.||is slower.|
The following are restrictions on the extended MultiLoad protocol. The target table cannot be a:
- Column partitioned table
- Replication group
- Table with an identity column defined as a primary index