Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Оптимальный способ очистки таблицы по критерию  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Есть не секционированная таблица в несколько десятков GB.
Ежесуточно пополняется на несколько десятков млн. строк.
Ежесуточно нужно удалять несколько десятков млн. строк (не обязательно тех, что были добавлены за эти сутки).
Удаление происходит ночью в рамках одной транзакции по условию WHERE NUMBER_COLUMN = :A AND DATE_COLUMN < :B (индексы есть, если что).
При этом происходит переполнение UNDO (очевидно, параллельно происходит более другая работа).
Решено разбить одну транзакцию удаления на несколько. (секционирование пока не рассматривается в качестве решения)

Задача состоит в том чтобы понять оптимальное количество записей, которые следует удалять при таком подходе.

Возможно, есть более лучший способ?

Подскажите, пожалуйста.
Спасибо.
12 авг 16, 19:03    [19535699]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
-2-
Member

Откуда:
Сообщений: 15330
--Eugene--
оптимальное количество записей
10000
12 авг 16, 19:39    [19535805]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
--Eugene--,

Оптимальное 12345. Я даже скрипт тебе подарю.
declare
   limit int := 12345;
begin

   loop
      delete from <your_table> where <your condition> and rownum <= limit;
      exit when sql%rowcount < limit;
      commit;      
   end loop;
   commit;

end;
/
12 авг 16, 19:45    [19535820]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
-2-,

Почему именно 10000?
12 авг 16, 22:59    [19536346]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Fogel
Member

Откуда:
Сообщений: 378
--Eugene--,

попробуй ещё так и сравни быстродействие c другими способами
declare
  row4del single_integer;
begin
  select count(1)
    into row4del
    from table
   WHERE NUMBER_COLUMN = :A AND DATE_COLUMN < :B;
  forall j in 1 .. (row4del / 100000 + 1)
    delete table
     where rowid in
           (select rowid
              from table
             WHERE NUMBER_COLUMN = :A AND DATE_COLUMN < :B
       and rownum < 100000);
    commit;
end;
12 авг 16, 23:06    [19536363]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
--Eugene--,

Тебе пытались намекнуть про бредовость постановки.
Одно дело если б ты хоть мерил в блоках, поскольку 10000 строк это может быть 10000 блоков а может 50.
И то безотносительно нагрузки и размера анду это сферическая оптимизация в вакууме.

А так ты получишь разве что идиотские решения в духе "попробуй ещё так".
12 авг 16, 23:30    [19536405]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Fogel
Member

Откуда:
Сообщений: 378
--Eugene--,
в любом случае вместо loop для своих циклов delete используй forall
только логику выборки под этот перечень продумай
примеры можно найти здесь.
12 авг 16, 23:47    [19536431]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
нерезиновый ундец
Guest
Fogel
--Eugene--,
в любом случае вместо loop для своих циклов delete используй forall
ты если сам не в состоянии прочиьать приводимые ссылки, то хотя бы обрати внимание с какой проблемой автор завел тему.
13 авг 16, 00:21    [19536499]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
dbms_photoshop
Одно дело если б ты хоть мерил в блоках, поскольку 10000 строк это может быть 10000 блоков а может 50.
И то безотносительно нагрузки и размера анду это сферическая оптимизация в вакууме.
Разве не понятно что размер UNDO известен, как и размер блока.
Допустим, все строки таблицы примерно одной длины (в байтах). Таким образом, можно получить среднее отношение (кол-во строк / кол-во блоков).
Прочая нагрузка на базу во время удаления неизвестна, это верно. Но возможно получить размер используемого/свободного пространства UNDO через системные вьюхи.
13 авг 16, 03:16    [19536607]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Rinka777
Member

Откуда:
Сообщений: 668
объем транзакции удаления определяется опытным путем. делаем цикл на 1000 записей, смотрим на реакцию. делаем цикл на 10 000 записей - сморим на реакцию. на 5000. сравниваем. думаем.
имхо. такое удаление ведет к разреживанию индексов и замедлению поиска по таблице. и место на диске ест. настоятельно рекомендую подумать в сторону партиционирования.
13 авг 16, 03:27    [19536610]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Вячеслав Любомудров
Member

Откуда: Владивосток
Сообщений: 18483
Fogel
--Eugene--,

попробуй ещё так и сравни быстродействие c другими способами
declare
  row4del single_integer;
begin
  select count(1)
    into row4del
    from table
   WHERE NUMBER_COLUMN = :A AND DATE_COLUMN < :B;
  forall j in 1 .. (row4del / 100000 + 1)
    delete table
     where rowid in
           (select rowid
              from table
             WHERE NUMBER_COLUMN = :A AND DATE_COLUMN < :B
       and rownum < 100000);
    commit;
end;
Я не понял, это юмор такой несмешной?

Или ты можешь объяснить какую роль здесь выполняет FORALL?
13 авг 16, 06:21    [19536636]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Elic
Member

Откуда:
Сообщений: 29976
Fogel
в любом случае вместо loop для своих циклов delete используй forall
У тебя плохо получается выглядеть умным, каждый раз давая такие бредовые советы.
13 авг 16, 07:36    [19536657]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
казинак
Member

Откуда:
Сообщений: 1273
--Eugene--
Есть не секционированная таблица в несколько десятков GB.
,...
При этом происходит переполнение UNDO (очевидно, параллельно происходит более другая работа)......

по нынешним временам сотня гигов - это тьфу
просто попроси админов добавить неcколько десятков гигов в undo
и не парься с оптимизацией

а то здешние недогуру тебя с дерьмом смешают
ниче полезного не скажут, но ты узнаешь много нового о своем уровне развития, о благонадежности и здравомыслии своих родственников до седьмого колена,
ну и т.д.

это форум оракл, детка!!!
13 авг 16, 11:11    [19536803]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Nobody1111
Guest
казинак
по нынешним временам сотня гигов - это тьфу
просто попроси админов добавить неcколько десятков гигов в undo

простец
13 авг 16, 11:36    [19536833]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Nobody1111
Guest
--Eugene--
Задача состоит в том чтобы понять оптимальное количество записей, которые следует удалять при таком подходе.


Оптимальное количество - это, думаю, между 2 огней:
1) чтобы undo не переполнялся?
2) чтобы сервер слишком частыми коммитами не изнасиловать.

То, что между этими крайностями, - оптимально.
13 авг 16, 12:54    [19536948]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Nobody1111
Оптимальное количество - это, думаю, между 2 огней:
1) чтобы undo не переполнялся?
2) чтобы сервер слишком частыми коммитами не изнасиловать.

То, что между этими крайностями, - оптимально.
в том-то и дело
Этот диапазон не так уж мал
13 авг 16, 16:33    [19537193]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Мисс Компро
Guest
--Eugene--,

Оптимальность определяется:
- уложиться в ресурсы (undo);
- время выполнения;
- время на раздумья.

Если бы послушался первого ответа, то решение было бы на сутки оптимальнее.
13 авг 16, 16:47    [19537210]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
--Eugene--,

совершенно согласен с много раз вышеозвученным мнением насчет ресурсов.
ресурсы надо иметь,
но ежели, например, работа-на-раз (быстро сделать и убежать ),
то имейте ввиду метод деления поляны на известное количество статистически-равных частей
например, substr например rowid, например, -1
substr(rowid,-1)

ну а вокруг этого можно накрутить например и (пещерный;)параллелизм раздачей значений для критериев хвоста сабстра по сессиям
14 авг 16, 12:53    [19538800]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Guest я Guest
Guest
--Eugene--,

> Задача состоит в том чтобы понять оптимальное количество записей, которые следует удалять при таком подходе.

Оптимальное количество = <Сколько UNDO можно использовать> / <UNDO, требуемое на удаление одной записи>

UNDO на удаление записи (в среднем) можно узнать из V$TRANSACTION.USED_UBLK

> Возможно, есть более лучший способ?
Оптимальный способ - не делать таких массовых удалений.
14 авг 16, 18:01    [19539297]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Fogel
Member

Откуда:
Сообщений: 378
Вячеслав Любомудров
Fogel
--Eugene--,

попробуй ещё так и сравни быстродействие c другими способами
declare
  row4del single_integer;
begin
  select count(1)
    into row4del
    from table
   WHERE NUMBER_COLUMN = :A AND DATE_COLUMN < :B;
  forall j in 1 .. (row4del / 100000 + 1)
    delete table
     where rowid in
           (select rowid
              from table
             WHERE NUMBER_COLUMN = :A AND DATE_COLUMN < :B
       and rownum < 100000);
    commit;
end;
Я не понял, это юмор такой несмешной?

Или ты можешь объяснить какую роль здесь выполняет FORALL?


это ум, бегущий впереди рук
а что я имел ввиду, сейчас продемонстрирую.
сегодня как раз потребовалось решить нечто подобное:

+
дано: таблица без партиций, два индекса.
Нужно удалить все записи с определённых файлов - данное поле без индекса
Замер показал около 7,5 млн строк
select count(*)  from table
 where regexp_like(file_name, 'блаблабла|блаблабла1|блаблабла2|блаблабла3|блаблабла4|блаблабла5|и ещё столько же');

База - пром, в таблицу что-то льётся не каждую секунду, но раз в 5 минут или чаще.
Оценил вообще размер бедствия по индексированному полю
select count(*) from table
  where insert_date < sysdate-20; --- 6621339

Кол-во больше (примерно на 0,5 млн), чем по условию имени файлов за то же время, это неправильно, кто-то за собой не чистит. Решил совместить приятное с полезным и вначале пройтись по индексу
declare
  type t_cu_rou is table of rowid index by pls_integer;
  col_cu_rou t_cu_rou;
  row4del    pls_integer;
begin
  select count(*)
    into row4del
    from table
   where insert_date < sysdate-20;
  for j in 1 .. (row4del / 100000 + 1) loop
    select rowid bulk collect
      into col_cu_rou
      from table
     where insert_date < sysdate-20
       and rownum < 100000;
    forall k_idx in col_cu_rou.first .. col_cu_rou.last
      delete from table where rowid = col_cu_rou(k_idx);
  end loop;
end;

/
время очистки 498 с
После этого запрос вернул 1,3 млн
select count(*)  from table
 where regexp_like(file_name, 'блаблабла|блаблабла1|блаблабла2|блаблабла3|блаблабла4|блаблабла5|и ещё столько же'); --- 1339863

В скрипте исправил условие на неиндексируемое.
Удаление заняло 148 с

около года назад тоже нечто подобное делал на этой же таблице
"опытные" товарищи тогда как раз предложили loop c пачками по 10 тыс и коммитом
было около 15 млн строк - удалялось несколько часов, благо в джоб запихнул.

Ах, да, забыл. Размер табличного пространства undo 2 gb

После этого вспомнил про данную тему и решил посмотреть, что тут ещё насоветовали.
Прочитал комментарии и решил поделиться.

Elic
У тебя плохо получается выглядеть умным, каждый раз давая такие бредовые советы.

зато хорошо получается троллить снобов, для которых набор знаний синоним ума.
умные не троллятся, а делают выводы себе на пользу.
ну или не делают, если им не интересно.
19 авг 16, 13:37    [19563486]     Ответить | Цитировать Сообщить модератору
 Re: Оптимальный способ очистки таблицы по критерию  [new]
Elic
Member

Откуда:
Сообщений: 29976
Fogel
declare
  type t_cu_rou is table of rowid index by pls_integer;
  col_cu_rou t_cu_rou;
  row4del    pls_integer;
begin
  select count(*)
    into row4del
    from table
   where insert_date < sysdate-20;
  for j in 1 .. (row4del / 100000 + 1) loop
    select rowid bulk collect
      into col_cu_rou
      from table
     where insert_date < sysdate-20
       and rownum < 100000;
    forall k_idx in col_cu_rou.first .. col_cu_rou.last
      delete from table where rowid = col_cu_rou(k_idx);
  end loop;
end;
Эх, это уё$ище эквивалентно следующему коду:
begin
  loop
    delete … rownum < :n;
    exit when sql%rowcount = 0;
  end loop;
end;
/
А с учётом отсутствия фиксации - следующему:
delete …;
Естественно, более короткая форма эффективнее (вплоть до гораздо) более длинной.

Ты поменьше выпячивай свою альтернативную одарённость. Это выглядит как-то нездорово
19 авг 16, 14:43    [19563937]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить