Why my delete is not running in parallel?

Sometimes is really faster of the delete statement is running in parallel; not only to identify the rows to be deleted, but the delete itself.

 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
/