ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимальный способ очистки таблицы по критерию
21 сообщений из 21, страница 1 из 1
Оптимальный способ очистки таблицы по критерию
    #39291298
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
Есть не секционированная таблица в несколько десятков GB.
Ежесуточно пополняется на несколько десятков млн. строк.
Ежесуточно нужно удалять несколько десятков млн. строк (не обязательно тех, что были добавлены за эти сутки).
Удаление происходит ночью в рамках одной транзакции по условию WHERE NUMBER_COLUMN = :A AND DATE_COLUMN < :B (индексы есть, если что).
При этом происходит переполнение UNDO (очевидно, параллельно происходит более другая работа).
Решено разбить одну транзакцию удаления на несколько. (секционирование пока не рассматривается в качестве решения)

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

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

Подскажите, пожалуйста.
Спасибо.
...
Рейтинг: 0 / 0
12.08.2016, 19:03:32
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291308
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
--Eugene--оптимальное количество записей10000
...
Рейтинг: 0 / 0
12.08.2016, 19:39:28
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291311
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
--Eugene--,

Оптимальное 12345. Я даже скрипт тебе подарю.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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;
/
...
Рейтинг: 0 / 0
12.08.2016, 19:45:44
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291355
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
-2-,

Почему именно 10000?
...
Рейтинг: 0 / 0
12.08.2016, 22:59:21
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291361
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
--Eugene--,

попробуй ещё так и сравни быстродействие c другими способами
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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;
...
Рейтинг: 0 / 0
12.08.2016, 23:06:07
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291365
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
--Eugene--,

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

А так ты получишь разве что идиотские решения в духе "попробуй ещё так".
...
Рейтинг: 0 / 0
12.08.2016, 23:30:49
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291367
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
--Eugene--,
в любом случае вместо loop для своих циклов delete используй forall
только логику выборки под этот перечень продумай
примеры можно найти здесь .
...
Рейтинг: 0 / 0
12.08.2016, 23:47:21
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291369
нерезиновый ундец
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
нерезиновый ундец
Гость
Fogel--Eugene--,
в любом случае вместо loop для своих циклов delete используй forallты если сам не в состоянии прочиьать приводимые ссылки, то хотя бы обрати внимание с какой проблемой автор завел тему.
...
Рейтинг: 0 / 0
13.08.2016, 00:21:47
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291374
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
dbms_photoshopОдно дело если б ты хоть мерил в блоках, поскольку 10000 строк это может быть 10000 блоков а может 50.
И то безотносительно нагрузки и размера анду это сферическая оптимизация в вакууме.Разве не понятно что размер UNDO известен, как и размер блока.
Допустим, все строки таблицы примерно одной длины (в байтах). Таким образом, можно получить среднее отношение (кол-во строк / кол-во блоков).
Прочая нагрузка на базу во время удаления неизвестна, это верно. Но возможно получить размер используемого/свободного пространства UNDO через системные вьюхи.
...
Рейтинг: 0 / 0
13.08.2016, 03:16:27
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291375
Фотография Rinka777
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Rinka777
Участник
объем транзакции удаления определяется опытным путем. делаем цикл на 1000 записей, смотрим на реакцию. делаем цикл на 10 000 записей - сморим на реакцию. на 5000. сравниваем. думаем.
имхо. такое удаление ведет к разреживанию индексов и замедлению поиска по таблице. и место на диске ест. настоятельно рекомендую подумать в сторону партиционирования.
...
Рейтинг: 0 / 0
13.08.2016, 03:27:50
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291379
Вячеслав Любомудров
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вячеслав Любомудров
Участник
Fogel--Eugene--,

попробуй ещё так и сравни быстродействие c другими способами
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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?
...
Рейтинг: 0 / 0
13.08.2016, 06:21:11
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291385
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Fogelв любом случае вместо loop для своих циклов delete используй forallУ тебя плохо получается выглядеть умным, каждый раз давая такие бредовые советы.
...
Рейтинг: 0 / 0
13.08.2016, 07:36:25
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291412
казинак
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
казинак
Участник
--Eugene--Есть не секционированная таблица в несколько десятков GB.
,...
При этом происходит переполнение UNDO (очевидно, параллельно происходит более другая работа)......
по нынешним временам сотня гигов - это тьфу
просто попроси админов добавить неcколько десятков гигов в undo
и не парься с оптимизацией

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

это форум оракл, детка!!!
...
Рейтинг: 0 / 0
13.08.2016, 11:11:24
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291417
Nobody1111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nobody1111
Гость
казинакпо нынешним временам сотня гигов - это тьфу
просто попроси админов добавить неcколько десятков гигов в undo

простец
...
Рейтинг: 0 / 0
13.08.2016, 11:36:32
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291441
Nobody1111
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nobody1111
Гость
--Eugene--Задача состоит в том чтобы понять оптимальное количество записей, которые следует удалять при таком подходе.

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

То, что между этими крайностями, - оптимально.
...
Рейтинг: 0 / 0
13.08.2016, 12:54:52
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291474
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
Nobody1111Оптимальное количество - это, думаю, между 2 огней:
1) чтобы undo не переполнялся?
2) чтобы сервер слишком частыми коммитами не изнасиловать.

То, что между этими крайностями, - оптимально.в том-то и дело
Этот диапазон не так уж мал
...
Рейтинг: 0 / 0
13.08.2016, 16:33:17
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291475
Мисс Компро
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мисс Компро
Гость
--Eugene--,

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

Если бы послушался первого ответа, то решение было бы на сутки оптимальнее.
...
Рейтинг: 0 / 0
13.08.2016, 16:47:40
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291570
Фотография orawish
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
orawish
Участник
--Eugene--,

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


ну а вокруг этого можно накрутить например и (пещерный;)параллелизм раздачей значений для критериев хвоста сабстра по сессиям
...
Рейтинг: 0 / 0
14.08.2016, 12:53:13
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39291632
Guest я Guest
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Guest я Guest
Гость
--Eugene--,

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

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

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

> Возможно, есть более лучший способ?
Оптимальный способ - не делать таких массовых удалений.
...
Рейтинг: 0 / 0
14.08.2016, 18:01:03
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39294573
Фотография Fogel
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Fogel
Участник
Вячеслав ЛюбомудровFogel--Eugene--,

попробуй ещё так и сравни быстродействие c другими способами
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
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 млн строк
Код: plsql
1.
2.
select count(*)  from table
 where regexp_like(file_name, 'блаблабла|блаблабла1|блаблабла2|блаблабла3|блаблабла4|блаблабла5|и ещё столько же');


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


Кол-во больше (примерно на 0,5 млн), чем по условию имени файлов за то же время, это неправильно, кто-то за собой не чистит. Решил совместить приятное с полезным и вначале пройтись по индексу
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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 млн
Код: plsql
1.
2.
select count(*)  from table
 where regexp_like(file_name, 'блаблабла|блаблабла1|блаблабла2|блаблабла3|блаблабла4|блаблабла5|и ещё столько же'); --- 1339863


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

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

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

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

ElicУ тебя плохо получается выглядеть умным, каждый раз давая такие бредовые советы.
зато хорошо получается троллить снобов, для которых набор знаний синоним ума.
умные не троллятся, а делают выводы себе на пользу.
ну или не делают, если им не интересно.
...
Рейтинг: 0 / 0
19.08.2016, 13:37:23
| Ответить | Цитировать | Написать  
Оптимальный способ очистки таблицы по критерию
    #39294630
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Fogel
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
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;

Эх, это уё$ище эквивалентно следующему коду:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
begin
  loop
    delete … rownum < :n;
    exit when sql%rowcount = 0;
  end loop;
end;
/

А с учётом отсутствия фиксации - следующему:
Код: plsql
1.
delete …;

Естественно, более короткая форма эффективнее (вплоть до гораздо) более длинной.

Ты поменьше выпячивай свою альтернативную одарённость. Это выглядит как-то нездорово
...
Рейтинг: 0 / 0
19.08.2016, 14:43:15
| Ответить | Цитировать | Написать  
21 сообщений из 21, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Оптимальный способ очистки таблицы по критерию
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39291632&tid=1887636]:
 0ms
get settings:
 7ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 56ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 240ms
total:  383ms
созд. / загр.: 383ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]