delete few records from multiple partitions in oracle

1. I have a table t_orders containing 40mn records .
2. The table is partitioned daily on trans_dt .
3. I want to delete around 25 mn records based on p_code='OTC'
4. I want to go with the below approach which seems to be faster. Is there any way I could delete based on multiple partitions like below? like using 2 partition(SYS_P20209,SYS_P20345) . with using one partition it is working fine.

ALTER SESSION ENABLE PARALLEL DML;
DELETE t_orders PARTITION(SYS_P20209,SYS_P20345) WHERE p_code = 'OTC';
COMMIT;

Thanks in advance,
Kashi

You want to delete 25 million out of 40 million, or 62% of your entire table? You do not want to use DELETE, as the amount of undo and redo will be enormous, and if it fails or you abort it you could end up with a very long rollback time. The correct way to handle this is to recreate the segments. You can do this several ways:

1. Use a CREATE TABLE AS SELECT (CTAS) to create a new table with only the data you want to keep. CTAS allows partitioning syntax so you can partition it as is created rather than pre-creating the table, or altering it later. Then rename the old table, rename the new table to the old, apply any grants, indexes, etc.. on it that the old table had, and you're done. This is the most efficient method without scripting.

2. Use a CREATE TABLE AS SELECT to create a temporary table with only the data you want to keep. Truncate the main table, enable parallel dml and insert append from the temp table back into the main table. This has the downside of moving the data twice, but avoids having to redefine dependent objects/grants, etc.

3. Since it's partitioned, you can write a PL/SQL loop through the partitions (query all_tab_partitions to get the partition names) and one by one CTAS each partition using extended partition naming syntax to a temp table, moving only the data you want to keep. You can only work on one partition at a time when using extended partition syntax. Then use an ALTER TABLE EXCHANGE PARTITION to swap the old partition with the temp table. Rebuild indexes when you're done. This is as efficient as #1 plus avoids having to reapply grants and redefine indexes, etc.. but of course only works on partitioned tables.

4. Lastly, if you absolutely, absolutely have to do a real DELETE (again, not recommended), here are some options:

   4a. Disable indexes and triggers. Enable parallel dml and hint (without the hint, enabling parallel dml may not do anything): DELETE /*+ parallel(16) */ FROM t_orders.... When done, rebuild indexes and reenable triggers.

   4b. Write a PL/SQL loop through the partition names and using extended partition naming syntax do the hinted, parallel dml delete in #4a but against only one partition. Commit each loop iteration. This ensures that all parallel threads distribute the workload evenly by block range rather than Oracle possibly opting for partition-wise distribution which can skew and result in less effective parallelization. It also keeps rollback times and space requirement for undo down to a more reasonable level since you are only doing one partition at a time. But if you fail you'll need to know where you left off as this is doing the operation in committed chunks rather than in one transaction. Example:

    BEGIN
      EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
   
      FOR rec_part IN (SELECT partition_name
                         FROM user_tab_partitions
                        WHERE table_name = 'T_ORDERS'
                       ORDER BY partition_position)
      LOOP
        dbms_output.put_line('Working on '||rec_part.partition_name);
   
        EXECUTE IMMEDIATE 'DELETE /*+ parallel(16) */ FROM t_orders PARTITION ('||rec_part.partition_name||') WHERE p_code = ''OTC''';
        COMMIT;
      END LOOP;
    END;