Until 12c, running parallel DML on tables with LOB columns required that table to be partitioned. Starting with 12c, we allow parallel INSERTs into non-partitioned tables with LOB columns provided that those columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE are still not supported. The documentation on this has some errors leading some customers to expect all parallel DML to be supported, so I wanted to point out the current behavior.
INSERTs into non-partitioned table with SecureFiles LOB column
Here is an example showing that the INSERT will be executed in parallel.
SQL> create table t (a number,b varchar2(200),c clob) lob(c) store as securefile; Table created. SQL> explain plan for insert /*+ parallel enable_parallel_dml */ 2 into t select /*+ parallel */ * from t; Explained. SQL> select * from table(dbms_xplan.display(format=>'basic +note'));
Other DML on non-partitioned table with SecureFiles LOB column
A DELETE statement on the other hand is executed serially as indicated in the note section of the plan. The note also shows the reason which is that the table is non-partitioned. This behavior applies for UPDATE and MERGE statements too.
SQL> explain plan for delete /*+ parallel enable_parallel_dml */ from t; Explained. SQL> select * from table(dbms_xplan.display(format=>'basic +note'));
DML on partitioned table with SecureFiles LOB column
For partitioned tables with LOB columns, all parallel DML operations are supported as before. Remember that the effective DOP will be the number of partitions, this behavior is also the same as before. This is because we use the partitions as PX granules in this case.
This example shows that parallel DML is allowed on a partitioned table with LOB columns. V$PQ_SESSTAT shows we get a DOP of 4 even though the plan shows a DOP of 8, that is because the table has 4 partitions.
SQL> create table t (a number,b varchar2(200),c clob) 2 lob(c) store as securefile partition by hash(a) partitions 4; Table created. SQL> explain plan for delete /*+ parallel(8) enable_parallel_dml */ from t; Explained. SQL> select * from table(dbms_xplan.display(format=>'basic +note'));SQL> delete /*+ parallel(8) enable_parallel_dml */ from t; 0 rows deleted. SQL> select * from V$PQ_SESSTAT; STATISTIC LAST_QUERY SESSION_TOTAL CON_ID ------------------------------ ---------- ------------- ---------- Queries Parallelized 0 0 0 DML Parallelized 1 1 0 DDL Parallelized 0 0 0 DFO Trees 1 1 0 Server Threads 4 0 0 Allocation Height 4 0 0 Allocation Width 1 0 0 Local Msgs Sent 8 0 0 Distr Msgs Sent 0 0 0 Local Msgs Recv'd 8 0 0 Distr Msgs Recv'd 0 0 0 DOP 4 0 0 Slave Sets 1 0 0 13 rows selected.
As always, we are fixing the documentation about this.