2

I've tried the following statement. But it doesn't proceed parallel. Why? How can I speed up the operation?

ALTER SESSION ENABLE PARALLEL DML;

DELETE /*+ parallel(20) */
      FROM  table
      WHERE flag != 'N';

enter image description here

Revious
  • 7,816
  • 31
  • 98
  • 147
  • 1
    #1I've found this article http://asktom.oracle.com/pls/asktom/f?p=100:11:0%3a%3a%3a%3aP11_QUESTION_ID:2345591157689 #2Can I disable ROLLBACK? – Revious Jul 17 '13 at 10:08
  • 1
    There's some advice on using hints with parallel delete at https://community.oracle.com/message/10371302#10371302, but it didn't help me either. – Vadzim Sep 29 '15 at 09:59
  • `DELETE` parallel will only work if the table **is partitioned** and if there are multiple partitions involved in the operation i.e. the `DELETE` must be deleting from multiple partitions instead just a single partition. For non partitioned table you will have to set the table to `PARALLEL` using `ALTER TABLE` and then use `ALTER SESSION FORCE PARALLEL DML` with parallel hint in the `DELETE` – Anjan Biswas Jul 02 '16 at 20:35
  • @Annjawn seems a very detailed description. Where in the documentation can we find this? – vegatripy Feb 08 '17 at 08:55
  • @vegatripy http://docs.oracle.com/cd/B19306_01/server.102/b14223/usingpe.htm see the section called "Rules for UPDATE, MERGE, and DELETE" second line "Update, merge, and delete parallelism are not possible within a partition, nor on a nonpartitioned table." – Anjan Biswas Feb 08 '17 at 09:11
  • 1
    Another reason for `PARALLEL` hint not working is the presence of triggers in the table. A `PARALLEL` hint will almost certainly not work if there are triggers on the tabled which are enabled, so disabling triggers is necessary. But if disabling the trigger breaks the functionality then I guess it's a moot point. – Anjan Biswas Feb 08 '17 at 09:14
  • 1
    @Annjawn The partition limitation does not apply anymore, at least since 11g. – Jon Heller Sep 15 '17 at 02:03

1 Answers1

1

try 

ALTER SESSION ENABLE PARALLEL DML;
DELETE /*+ parallel(table, 20) */
  FROM  table
  WHERE flag!= 'N';

you can also try another option to delete data, using CTAS, reference from asktom Deleting many rows from a big table

create table new_table unrecoverable as select * from old_table where ....;

drop table old_table;

rename new_table to old_table;

create index old_table_idx1 on old_table(c1,c2) unrecoverable parallel 5;

wanana
  • 361
  • 1
  • 9
  • 1
    #1Doesn't work. I've put an image. The block change happen just by one thread and the operation is thousand times slower than a select. #2I cannot drop the original table.. – Revious Jul 17 '13 at 09:46
  • Is your table using bitmap index? – wanana Jul 17 '13 at 10:03
  • Or you can just try to Manual Parallelism with different key value ranges or rowid ranges. – wanana Jul 17 '13 at 10:08
  • No, don't use bitmap index. I fear the problem is the table is not partitioned. So it create redo segments (unuseful for me), it updates indexes, ecc... all unuseful... can I disable? – Revious Jul 17 '13 at 10:18