CODEWITHSUNDEEP
sqldatabaseoracle-databaseparallel-processing
Vij
Vij

Reputation: 95

Oracle: Can a Parallel DML operation occur on a table without parallel attribute?

I wanted to know if we can run parallel update/delete/insert on tables which were not created using parallel query.

ex:

Create table Discount( sales_rep_id number, discount number, performance_indicator number ) tablespace commt;

(notice the absence of parallel keyword in the above ddl query)

now can I update the above table with the below parallel hint?

update /*+ PARALLEL(discount) */ Discount set performance_indicator = 50 where discount<30 ;

Upvotes: 0

Views: 422

Answers (1)

Jon Heller
Jon Heller

Reputation: 36807

Yes, parallel DML can run against a table that was created with NOPARALLEL (the default parallel setting).

The table-level setting is only for convenience, for tables that should usually be processed with parallelism. That setting can be over-ridden by a SQL hint.

But the session will need to run this first:

alter session enable parallel dml;

And I recommend you use statement-level parallelism instead of object-level parallelism. This makes everything in the statement run with the same DOP. In general, if one part of the statement uses parallelism, they all should. Simply remove the object name from the query and Oracle will set the DOP for the whole statement:

update /*+ parallel */ discount ...

Upvotes: 2

Related Questions