📋Steps in this guide1/2

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

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

oracle 23configurationintermediate

by OracleDba

18 views

The Problem : Touch-Once Restriction

In releases prior to Oracle database 23ai/26ai 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 . We enable parallel DML and perform a parallel insert into the table. Without issuing a commit or rollback, we attempt to select from the table and we get an error caused by the touch-once restriction. We have to commit or rollback before referencing the table modified by the parallel DML.

Code/Command (click line numbers to comment):

drop table t1 purge;

create table t1 as
select *
from   all_objects;

Table created.

SQL>

alter session enable parallel dml;

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

118,673 rows inserted.

SQL>

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>

commit;

select count(*) from t1;

   COUNT(*)
-----------
     237346

SQL>

The Solution: The Removal of the Touch-Once Restriction

Oracle database 23ai/26ai 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 23ai/26ai. We create a test table as a copy of . We enable parallel DML and perform a parallel insert into the table. Without issuing a commit or rollback, we attempt to select from the table and we no longer get an error. We can also perform more parallel or direct loads without a commit. We can think of this as a quality of life improvement. For more information see: - About Parallel DML Operations - Restrictions on Parallel DML Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

drop table if exists t1 purge;

create table t1 as
select *
from   all_objects;

Table created.

SQL>

alter session enable parallel dml;

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

79144 rows inserted.

SQL>

select count(*) from t1;

  COUNT(*)
----------
    158288

SQL>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!