Unrestricted Direct Loads Features in Oracle 23c Database Architecture .

 

Hi All,

In this post i am sharing the details about Unrestricted Direct Loads features in Oracle 23C database Architecture .

Unrestricted Direct Loads

Prior to this feature, after a direct load and prior to a commit, queries and additional DMLs were not allowed on the same table for the same session or for other database sessions. This enhancement allows the loading session to query and perform DML on the same table that was loaded. Other sessions are also able to concurrently perform direct loads and DML. Rollback to a savepoint is also supported.

This feature removes the restrictions that you may have encountered when loading and querying data. Potentially improving the performance of your applications in areas such as Data Warehousing and complex batch processing. 

Restrictions on Parallel DML

There are several restrictions that apply to parallel DML.

The following restrictions apply to parallel DML (including direct-path INSERT):

• Intra-partition parallelism for UPDATE, MERGE, and DELETE operations require that the COMPATIBLE initialization parameter be set to 9.2 or greater.

• The INSERT VALUES statement is never executed in parallel.

• Parallel DML operations cannot be done on tables with triggers.

• Replication functionality is not supported for parallel DML.

• Parallel DML cannot occur in the presence of certain constraints: self-referential integrity, delete cascade, and deferred integrity. In addition, for direct-path INSERT, there is no support for any referential integrity.

• Parallel DML can be done on tables with object columns provided the object columns are not accessed.

• Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

  For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

• A DML operation cannot be executed in parallel if it is in a distributed transaction or if the DML or the query operation is on a remote object.

• Clustered tables are not supported.

• Parallel UPDATE, DELETE, and MERGE operations are not supported for temporary tables.

• Parallel DML is not supported on a table with bitmap indexes if the table is not partitioned.

Note:

As of Oracle Database 23c, some previous restrictions on DML and PDML after direct-path inserts have been lifted. You can now do the following immediately after a direct-path insert:

• Query the same table multiple times within the same session.
• Perform conventional DML (serial and parallel) on the same table within the same session.
• Multiple direct loads on the same table in the same session.

However, the restrictions on the above operations still apply when:

• The tables are IOT or clustered tables. (The table must be a heap table.)
• The tablespace is not under Automatic Segment Space Management (ASSM). Temporary tables are not under ASSM, so this includes them as well.
• The tablespace is of uniform extent.

The restrictions on multiple queries and DML/PDML operations as well as the restriction on multiple direct-path inserts in the same session can be reinstated when needed by including the NO_MULTI_STATEMENT hint in SQL statements.

See Also:

 Partitioning Key Restriction

You can only update the partitioning key of a partitioned table to a new value if the update does not cause the row to move to a new partition.

The update is possible if the table is defined with the row movement clause enabled.

 Function Restrictions

The function restrictions for parallel DML are the same as those for parallel DDL and parallel query.

See About Parallel Execution of Functions for more information.

 Data Integrity Restrictions

The interactions of integrity constraints and parallel DML statements are introduced in the topic.

This section contains following topics:

• NOT NULL and CHECK

• UNIQUE and PRIMARY KEY

• FOREIGN KEY (Referential Integrity)

• Delete Cascade

• Self-Referential Integrity

• Deferrable Integrity Constraints

 NOT NULL and CHECK

The integrity constraints for NOT NULL and CHECK are discussed in this topic.

NOT NULL and CHECK integrity constraints are allowed. They are not a problem for parallel DML because they are enforced on the column and row level, respectively.

UNIQUE and PRIMARY KEY

The integrity constraints for UNIQUE and PRIMARY KEY are discussed in this topic.

UNIQUE and PRIMARY KEY integrity constraints are allowed.

 FOREIGN KEY (Referential Integrity)

Restrictions for referential integrity occur whenever a DML operation on one table could cause a recursive DML operation on another table.

These restrictions also apply when, to perform an integrity check, it is necessary to see simultaneously all changes made to the object being modified.

below table lists all of the operations that are possible on tables that are involved in referential integrity constraints.

Table  Referential Integrity Restrictions

DML Statement	Issued on Parent	Issued on Child	Self-Referential
INSERT	(Not applicable)	Not parallelized	Not parallelized
MERGE	(Not applicable)	Not parallelized	Not parallelized
UPDATE No Action	Supported	Supported	Not parallelized
DELETE No Action	Supported	Supported	Not parallelized
DELETE Cascade	Not parallelized	(Not applicable)	Not parallelized

 Delete Cascade

The delete cascade data integrity restrictions are discussed in this topic.

Deletion on tables having a foreign key with delete cascade is not parallelized because parallel execution servers attempt to delete rows from multiple partitions (parent and child tables).

 Self-Referential Integrity

DML on tables with self-referential integrity constraints is not parallelized if the referenced keys (primary keys) are involved.

For DML on all other columns, parallelism is possible.

 Deferrable Integrity Constraints

The deferrable integrity constraints are discussed in this topic.

If any deferrable constraints apply to the table being operated on, the DML operation is not executed in parallel.

 Trigger Restrictions

A DML operation is not executed in parallel if the affected tables contain enabled triggers that may get invoked as a result of the statement.

This implies that DML statements on tables that are being replicated are not parallelized.

Relevant triggers must be disabled to parallelize DML on the table. If you enable or disable triggers, then the dependent shared cursors are invalidated.

 Distributed Transaction Restrictions

The distributed transaction restrictions are discussed in this topic.

A DML operation cannot be executed in parallel if it is in a distributed transaction or if the DML or the query operation is on a remote object.

 Examples of Distributed Transaction Parallelization

Several examples of distributed transaction processing are shown in this topic.

In the first example, the DML statement queries a remote object. The DML operation is executed serially without notification because it references a remote object.

INSERT /*+ APPEND PARALLEL (t3,2) */ INTO t3 SELECT * FROM t4@dblink;

In the next example, the DML operation is applied to a remote object. The DELETE operation is not parallelized because it references a remote object.

DELETE /*+ PARALLEL (t1, 2) */ FROM t1@dblink;

In the last example, the DML operation is in a distributed transaction. The DELETE operation is not executed in parallel because it occurs in a distributed transaction (which is started by the SELECT statement).

SELECT * FROM t1@dblink; 

 DELETE /*+ PARALLEL (t2,2) */ FROM t2;

 COMMIT; 

Thanks,

Srini