Oracle support parallel query for DML statements that include INSERT, UPDATE and DELETE statements. This is a great way to speed up these DML statements when you are trying to process those millions or records.
Not a big deal! you already knew about it. Infact, you have used parallel query on your DML statements. Have you ever been in a situation when your parallel DML that used to complete quickly enough is not completing today?????
To your surprise, it is entirely possible that your Parallel DML is not using parallel slaves because there are some restrictions for using parallel query on DML. Two of the most important ones are below
1) Triggers on the underlying tables. If the table used in your DML statement has triggers on it, it cannot use Parallel query
2) Referential integrity constraint: The underlying table, if it has referential integrity constraint, will not be able to use Parallel query.
This is true even if there is sufficient parallel slaves available for your query.
So, next time you run into this situation, you know what to check.
Not a big deal! you already knew about it. Infact, you have used parallel query on your DML statements. Have you ever been in a situation when your parallel DML that used to complete quickly enough is not completing today?????
To your surprise, it is entirely possible that your Parallel DML is not using parallel slaves because there are some restrictions for using parallel query on DML. Two of the most important ones are below
1) Triggers on the underlying tables. If the table used in your DML statement has triggers on it, it cannot use Parallel query
2) Referential integrity constraint: The underlying table, if it has referential integrity constraint, will not be able to use Parallel query.
This is true even if there is sufficient parallel slaves available for your query.
So, next time you run into this situation, you know what to check.
