Зафиксируйте каждые 1000 строк

Question

Я пытаюсь сделать коммит каждые 1000 строк, пока все записи не будут удалены, мы должны удалить более миллиона записей.

Первоначально:

private static final String DELETE_OLD_REPORTS_FROM_REPORTING =
-            "DELETE FROM A_REPORTING\n" +
-            "WHERE ID IN(" +
-            "SELECT ID FROM A_REPORTING\n" +
-            "WHERE STATUS = 'LOADED'\n" +
-            "AND CREATE_DT < TO_DATE(:createdDate, 'dd-mon-yyyy'))";

Я думал сделать что-то вроде этого:

"BEGIN\n" +
                "LOOP\n" +
                    "DELETE FROM A_REPORTING\n" +
                    "WHERE ID IN(" +
                    "SELECT ID FROM A_REPORTING\n" +
                    "WHERE STATUS = 'LOADED'\n" +
                    "AND CREATE_DT < TO_DATE(:createdDate, 'dd-mon-yyyy')\n+" +
                    "AND ROWNUM <= 10000);\n" +
                    "EXIT WHEN SQL%rowcount < 9999;\n" +
                    "COMMIT;\n" +
                "END LOOP;\n"+
            "COMMIT;\n" +
            "END";

Однако есть ли лучший подход к этому? Причина этого в том, что мы получили ошибку ORA-01555:

ORA-01555: снимок слишком старый: сегмент отката с номером%n с именем "%segname" слишком мал.

Answer 1

Это звучит идеально для массового сбора!

declare

  d_created_date   date   := to_date(:createddate, 'dd-mon-yyyy'); -- define :createddate here

  -- get all rows you want to delete
  cursor cur_delete_records is
  select r.rowid 
    from a_reporting r
   where r.status = 'LOADED'
     and r.create_dt < to_date(d_created_date, 'dd-mon-yyyy');

  -- collection to store rows
  type t_delete_records   is table of cur_delete_records%rowtype;
  rec_delete_records      t_delete_records;

begin

  open cur_delete_records;
    loop
      fetch cur_delete_records
      bulk collect into rec_delete_records
      limit 1000; -- here's the 1,000 record max per loop
      exit when nvl(rec_delete_records.count, 0) = 0;

      forall x in rec_delete_records.first .. rec_delete_records.last

        delete from a_reporting r
         where r.rowid = rec_delete_records(x).rowid;

        commit;

    end loop;
  close cur_delete_records;

end;
/

Answer 2

Вы можете сделать ваше табличное пространство отмены достаточно большим, чтобы все изменения данных в исходном операторе удаления могли быть сохранены в отмене. Затем вы можете увеличить параметр undo_retention, чтобы он длился дольше, чем требуется для выполнения вашего исходного оператора удаления. Таким образом, если для удаления требуется 1 час, установите для сохранения отмены 4 часа и продолжайте увеличивать размер табличного пространства отмены, пока вы не получите ORA-01555 при запуске удаления. 1 миллион строк не так много, чтобы удалить, если строки не очень большие. Вы должны быть в состоянии предотвратить ORA-01555 с помощью отмены.

Бобби

Answer 3

Спасибо всем. Мы решили пойти по этому пути после разговора с администратором базы данных>