Removal of Touch-Once Restriction after Parallel DML (Unrestricted Direct Loads) in Oracle Database 23c

Oracle database 23c makes parallel DML more flexible by removing the touch-once restriction after parallel DML. This is also know as unrestricted direct loads.

◉ The Problem : Touch-Once Restriction

In releases prior to Oracle database 23c we can't select or modify an object after it has been modified using parallel DML in the same transaction. We have to issue either a commit or rollback to end the transaction before it can be referenced. This is called the touch-once restriction. Here is an example of it in 19c.

We create a test table as a copy of ALL_OBJECTS.

drop table t1 purge;

create table t1 as

select *

from   all_objects;

Table created.

SQL>

We enable parallel DML and perform a parallel insert into the T1 table.

alter session enable parallel dml;

insert /*+ parallel(t1 4) */ into t1

select /*+ parallel(t1 4)*/ * from t1;

118,673 rows inserted.

SQL>

Without issuing a commit or rollback, we attempt to select from the T1 table and we get an error caused by the touch-once restriction.

select count(*) from t1;

Error starting at line : 1 in command -

select count(*) from t1

Error at Command Line : 1 Column : 22

Error report -

SQL Error: ORA-12838: cannot read/modify an object after modifying it in parallel

12838. 00000 -  "cannot read/modify an object after modifying it in parallel"

*Cause:    Within the same transaction, an attempt was made to add read or

           modification statements on a table after it had been modified in parallel

           or with direct load. This is not permitted.

*Action:   Rewrite the transaction, or break it up into two transactions

           one containing the initial modification and the second containing the

           parallel modification operation.

SQL>

We have to commit or rollback before referencing the table modified by the parallel DML.

commit;

select count(*) from t1;

   COUNT(*)

-----------

     237346

SQL>

◉ The Solution: The Removal of the Touch-Once Restriction

Oracle database 23c has removed the touch-once restriction, so we no longer have to issue a commit or rollback before referencing the object modified by a parallel DML operation. Here is a repeat of the previous example in Oracle database 23c.

We create a test table as a copy of ALL_OBJECTS.

drop table if exists t1 purge;

create table t1 as

select *

from   all_objects;

Table created.

SQL>

We enable parallel DML and perform a parallel insert into the T1 table.

alter session enable parallel dml;

insert /*+ parallel(t1 4) */ into t1

select /*+ parallel(t1 4)*/ * from t1;

79144 rows inserted.

SQL>

Without issuing a commit or rollback, we attempt to select from the T1 table and we no longer get an error.

select count(*) from t1;

  COUNT(*)

----------

    158288

SQL>

We can also perform more parallel or direct loads without a commit.

insert /*+ parallel(t1 4) */ into t1

select /*+ parallel(t1 4)*/ * from t1;

158288 rows created.

SQL>

insert /*+ append */ into t1

select * from t1;

316576 rows created.

SQL>

We can think of this as a quality of life improvement.

Source: oracle-base.com