If the Serialize option is set to ON, operations on a row occur serially in the order submitted.
The sequencing key of SERIALIZE ON is specified as one or more column names occurring in the input data SCHEMA definition. These SCHEMA columns are collectively referred to as the key. Usually the key is the primary index of the table being updated, but it can be a different column or set of columns. For example:
APPLY ('UPDATE emp SET dept_name = :dept_name WHERE empno = :empno;') SERIALIZE ON (empno) TO TARGET_TABLE
This APPLY statement guarantees that all data rows with the same key (empno) are applied to the database in the same order received they are received from the producer operator. In this case, the column empno is the primary index of the Emp table.
Note that SERIALIZE ON is local to a specific DML statement. In the following example, a group DML is specified, but only the first statement uses the Serialize option:
APPLY ('UPDATE emp SET dept_num = :dept_num WHERE empno = :empno; ') SERIALIZE ON (empno) ('UPDATE dept SET dept_name = :dept_name WHERE deptno = :deptno; ') TO TARGET_TABLE
Following are some of the advantages to using the Serialize option, and might improve performance:
- SERIALIZE ON can eliminate the lock delays or potential deadlocks caused by primary index collisions coming from multiple sessions.
- SERIALIZE ON can also reduce deadlocks when rows with non-unique primary index values are processed.