2 observations related to this:
1) Deleting from a table partition, it seems that the alias is not being translated properly, so instead I added a where clause, to access the required partition.
2) Adding 2 where clauses didn't help, but adding a between clause, did the trick.
Example of a query which will NOT do the delete in parallel:
delete /*+ parallel(a,16) full(a) */
from MYTAB partition (PR001) a
where expiration_date < add_months( trunc(sysdate), -85 );
Example of a fixed query, which will run in parallel:
delete /*+ parallel(a,16) full(a) */
from MYTAB a
where expiration_date < add_months( trunc(sysdate), -85 )
and ID between 2500 and 37500;
Notes:
1. ALTER SESSION ENABLE PARALLEL DML; is still required.
2. To check that the delete is running in parallel, the below query should return more than 1 row:
select a.SID,a.USERNAME,b.xidusn,b.used_urec,b.used_ublk
from v$session a,v$transaction b
where a.SADDR=b.ses_addr
/
1. ALTER SESSION ENABLE PARALLEL DML; is still required.
2. To check that the delete is running in parallel, the below query should return more than 1 row:
select a.SID,a.USERNAME,b.xidusn,b.used_urec,b.used_ublk
from v$session a,v$transaction b
where a.SADDR=b.ses_addr
/
