2.5 Performing Parallel DDL, Parallel DML (PDML), and Parallel Query (PQ) Operations on LOBs

Oracle supports parallel execution of the following operations when performed on partitioned tables with SecureFiles LOBs or BasicFiles LOBs.

• CREATE TABLE AS SELECT
• INSERT AS SELECT
• Multitable INSERT
• SELECT
• DELETE
• UPDATE
• MERGE (conditional UPDATE and INSERT)
• ALTER TABLE MOVE
• SQL Loader
• Import/Export

Additionally, Oracle supports parallel execution of the following operations when performed on non-partitioned tables with only SecureFile LOBs:

• CREATE TABLE AS SELECT
• INSERT AS SELECT
• Multitable INSERT
• SELECT
• DELETE
• UPDATE
• MERGE (conditional UPDATE and INSERT)
• ALTER TABLE MOVE
• SQL Loader

Restrictions on parallel operations with LOBs

• Parallel insert direct load (PIDL) is disabled if a table also has a BasicFiles LOB column, in addition to a SecureFiles LOB column.
• PDML is disabled if LOB column is part of a constraint.
• PDML does not work when there are any domain indexes defined on the LOB column.
• Parallelism must be specified only for top-level non-partitioned tables.
• Use the ALTER TABLE MOVE statement with LOB storage clause, to change the storage properties of LOB columns instead of the ALTER TABLE MODIFY statement. The ALTER TABLE MOVE statement is more efficient because it executes in parallel and does not generate undo logs.

See Also:

Oracle Database Administrator's Guide section "Managing Processes for Parallel SQL Execution"

Oracle Database SQL Language Reference section "ALTER TABLE"