Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
Научите уму-разуму, как надо было делать и что плохого в моей реализации.

Постановка задачи:
есть табл tablitchka с количеством записей 1 000 000 000. В таблице нет индексов кроме первичного ключа
Требуется проапдейтить таблицу по условию COND (условие на неиндексированные поля) установив поле kolonka в 1, прогнозируемое количество попадающих под апдейт - 10 000 000.

Мое решение:
declare
 
   type tp is record (rid rowid, id number);
   type tAr is table of tp;
   ar tar;
   cursor c is
   select t.rowid, t.id from
   tablitchka t
   where /*условие COND*/
    .. and kolonka = 0;               
begin
  open c;
  loop
     fetch c bulk collect  into ar limit 10000; 
     forall i in 1..ar.count save exceptions
     
     update
     tablitchka  --
     SET kolonka = 1 where rowid = ar(i).rid and id = ar(i).id;
     cnt := cnt + sql%rowcount;
     commit;
     exit when c%notfound;
  end loop;
 
  close c; 
 
exception when others then
   null;    
end;


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

Сообщение было отредактировано: 24 ноя 21, 11:50
24 ноя 21, 11:48    [22400113]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
AmKad
Member

Откуда:
Сообщений: 5344
ctas [+partitioning] + rename + grants.
24 ноя 21, 11:54    [22400115]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3276
legg,

если
напр kolonka = 2 проапдейтить надо?


ps
имхо
если есть условие по ровид, то по ид лишнее

.....
stax

Сообщение было отредактировано: 24 ноя 21, 12:04
24 ноя 21, 12:02    [22400117]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
AmKad
ctas
- отказался от такого варианта, тк решил что на работающей базе (таблица активно используется другими сессиями) так не прокатит. важное уточнение - забыл сразу дописать
24 ноя 21, 12:05    [22400120]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
Stax
legg,

если
напр kolonka = 2 проапдейтить надо?
stax

нет
Stax

ps
имхо
если есть условие по ровид, то по ид лишнее

.....
stax

имхо тоже, коллега вцепился зубами в ногу, боится что у записи rowid поменяется в одном случае из стотыщмиллионов. Надеюсь это успокоит
24 ноя 21, 12:16    [22400127]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3276
legg

имхо тоже, коллега вцепился зубами в ногу, боится что у записи rowid поменяется в одном случае из стотыщмиллионов. Надеюсь это успокоит

допустим поменяется ровид и ....

тогда условие rowid = ar(i).rid and id = ar(i).id; не отработает (0 на 1 не поменяется)

ps
есть save exceptions, а обработка за циклом

....
stax
24 ноя 21, 12:52    [22400148]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
AmKad
Member

Откуда:
Сообщений: 5344
legg
отказался от такого варианта, тк решил что на работающей базе (таблица активно используется другими сессиями) так не прокатит. важное уточнение - забыл сразу дописать
Есть вариант похардовее.

1) С учетом того, таблица, как и любой сегмент, состоит из экстентов, в цикле с помощью dbms_rowid получаешь первый и последний rowid для каждого экстента и делаешь update по условию
where your_fucking_condition and rowid between r_begin and r_end
например, с фиксацией транзакции после каждого или N-го экстента.
2) Делаешь финальный update-скан по всей таблице только для тех записей, что были добавлены или мигрированы с момента старта этапа 1 и для которых выполняется условие
where your_fucking_condition and lnnvl(kolonka = 1)

Какой в этом профит в сравнении с твоим вариантом - не нужно вычитывать таблицу перед обновлением.
24 ноя 21, 13:16    [22400159]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
Stax
legg

имхо тоже, коллега вцепился зубами в ногу, боится что у записи rowid поменяется в одном случае из стотыщмиллионов. Надеюсь это успокоит

допустим поменяется ровид и ....

тогда условие rowid = ar(i).rid and id = ar(i).id; не отработает (0 на 1 не поменяется)

ps
есть save exceptions, а обработка за циклом

....
stax

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

Сообщение было отредактировано: 24 ноя 21, 13:35
24 ноя 21, 13:30    [22400164]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
AmKad
legg
отказался от такого варианта, тк решил что на работающей базе (таблица активно используется другими сессиями) так не прокатит. важное уточнение - забыл сразу дописать
Есть вариант похардовее.

1) С учетом того, таблица, как и любой сегмент, состоит из экстентов, в цикле с помощью dbms_rowid получаешь первый и последний rowid для каждого экстента и делаешь update по условию
where your_fucking_condition and rowid between r_begin and r_end

например, с фиксацией транзакции после каждого или N-го экстента.
2) Делаешь финальный update-скан по всей таблице только для тех записей, что были добавлены или мигрированы с момента старта этапа 1 и для которых выполняется условие
where your_fucking_condition and lnnvl(kolonka = 1)


Какой в этом профит в сравнении с твоим вариантом - не нужно вычитывать таблицу перед обновлением.


крутокрутокруто! попробую. сейчас оставлю так, а в след релизе глядишь и эту версию выпущу. на сам деле я чего то подобного и хотел, не знал как реализовать.
24 ноя 21, 13:32    [22400166]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3276
legg

вместо null в обработчике логирование варнинга идет


я не про null

я про место обработки, следующие 10000 не обработаются если была "ошибка"


зы
я так понимаю ето разовая операция,
"оптимизация" займет больше времени чем проапдейтить 10млн
stax

Сообщение было отредактировано: 24 ноя 21, 14:08
24 ноя 21, 14:06    [22400192]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
[quot Stax#22400192]
legg

вместо null в обработчике логирование варнинга идет


я не про null

я про место обработки, следующие 10000 не обработаются если была "ошибка"


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

попытался связанно объяснить, но не уверен что вышло).

Stax

"зы
я так понимаю ето разовая операция,
"оптимизация" займет больше времени чем проапдейтить 10млн
stax " stax
- нет. ежедневно по паре десятков таблиц. с перспективой увеличения количества как таблиц так и их объемов.

Сообщение было отредактировано: 24 ноя 21, 14:23
24 ноя 21, 14:19    [22400195]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19912
legg
по полю kolonka идет партиционирование. конечная цель - перенос из исходной таблицы в целевую неактуальных данных (архивировать то есть) на лету, без техперерывов всяких.

Через row movement?
Мсье знает толк в извращениях.

https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/manage-data-db-ilm.html#GUID-C53B27F1-7E71-4683-A2A0-3DE194A59C2E

Сообщение было отредактировано: 24 ноя 21, 14:36
24 ноя 21, 14:34    [22400208]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
andrey_anonymous
legg
по полю kolonka идет партиционирование. конечная цель - перенос из исходной таблицы в целевую неактуальных данных (архивировать то есть) на лету, без техперерывов всяких.

Через row movement?
Мсье знает толк в извращениях.

согласен. через row movement естественно. не я это придумал. но реализовываю -я). а тут грабля на грабле.
24 ноя 21, 14:40    [22400210]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
andrey_anonymous


https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/manage-data-db-ilm.html#GUID-C53B27F1-7E71-4683-A2A0-3DE194A59C2E


первый раз вижу о такой возможности. вечером почитаю. спасибо огромное!
24 ноя 21, 14:46    [22400214]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
legg
andrey_anonymous


https://docs.oracle.com/en/database/oracle/oracle-database/19/vldbg/manage-data-db-ilm.html#GUID-C53B27F1-7E71-4683-A2A0-3DE194A59C2E


первый раз вижу о такой возможности. вечером почитаю. спасибо огромное!

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

Сообщение было отредактировано: 24 ноя 21, 14:55
24 ноя 21, 14:52    [22400218]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 3276
legg

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


имхо
тогда и мало смысла в save exceptions

.....
stax
24 ноя 21, 14:57    [22400219]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
Stax
legg

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


имхо
тогда и мало смысла в save exceptions

.....
stax

я там ругаюсь)
24 ноя 21, 14:58    [22400220]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 750
А зачем вообше такая странная логика:
1. Скопировать втаблицу в память
2. Сканировать таблицу в памяти
3. Апдейтить оригинальную таблицу
Вроде-как по всем книжкам гораздо быстрее работать напрямую
24 ноя 21, 16:14    [22400260]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19912
kapelan
Вроде-как по всем книжкам гораздо быстрее работать напрямую

Прямая прямой рознь.
Если тупо натравить update на большую меняющуюся таблицу, то можете нарваться на statement restart, что в случае большой таблицы будет печально само по себе.
Кроме того, update будет длительным и имеет шансы заблокировать основные операции по таблице.

Если натравить update на большую меняющуюся таблицу с ограничителем по числу строк (...and rownum < :BatchSize), то FTS будет бегать каждый запуск. При этом конкуренция и statement restart тоже никуда не денутся.

Примечение: В случае ТС если "самопалить", то надо гонять не update, а delete...returning с последующим выходом на forall insert, это будет дешевле update+row_movement.
Впрочем, означенные выше проблемы delete-у тоже свойственны.


Есть вариант распараллелить dml несколько снижая проблемы с конкуренцией, но не уверен что ТС это надо.

На круг предложенный вариант с разбиением работы на части посредством предварительного анализа таблицы экстентов вполне себе неплох.
24 ноя 21, 16:30    [22400278]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 750
andrey_anonymous
kapelan
Вроде-как по всем книжкам гораздо быстрее работать напрямую

Прямая прямой рознь....

это все понятно, тока переложить большую таблицу в другое место тоже не быстрая операция ... потом сканировать ее в памяти ...
Грубо говоря получаем двойной фулл скан большой таблицы.
Я бы посмотрел в в сторону мат. вью: навесить на него нужные индексы и делать поиск по индексам.
Таким образом получаем поиск по индексу в мат. вью и UPDATE таблицы по PRIMARY KEY
24 ноя 21, 16:40    [22400284]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19912
kapelan
переложить большую таблицу в другое место тоже не быстрая операция ... потом сканировать ее в памяти ...

Это о чем?
24 ноя 21, 16:47    [22400289]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
kapelan
Member

Откуда: хутор БольшойБугор
Сообщений: 750
andrey_anonymous
kapelan
переложить большую таблицу в другое место тоже не быстрая операция ... потом сканировать ее в памяти ...

Это о чем?


     fetch c bulk collect  into ar limit 10000; 
     forall i in 1..ar.count save exceptions
24 ноя 21, 17:05    [22400298]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
kapelan
andrey_anonymous
пропущено...

Это о чем?


     fetch c bulk collect  into ar limit 10000; 
     forall i in 1..ar.count save exceptions

или я чего то не понимаю или выглядит так, что вы сравниваете скорость чтения с диска данных таблицы и скорость перебора массива в оперативной памяти.
24 ноя 21, 17:15    [22400303]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
legg
Member

Откуда: Москва
Сообщений: 6689
kapelan
andrey_anonymous
пропущено...

Прямая прямой рознь....

это все понятно, тока переложить большую таблицу в другое место тоже не быстрая операция ... потом сканировать ее в памяти ...
Грубо говоря получаем двойной фулл скан большой таблицы.
Я бы посмотрел в в сторону мат. вью: навесить на него нужные индексы и делать поиск по индексам.
Таким образом получаем поиск по индексу в мат. вью и UPDATE таблицы по PRIMARY KEY

вопрос в том как одним махом проапдейтить 10 000 000 строк. база не в монопольном доступе, очередные 10000000 записей как раз в таблицу инсертятся в это время. а чуть позже - апдейтятся. даже если конкуренций не случится -снапшот ту олд практически гарантирован ведь при прямом апдейте всего сразу?
24 ноя 21, 17:21    [22400307]     Ответить | Цитировать Сообщить модератору
 Re: аск фор кодривью :)  [new]
booby
Member

Откуда:
Сообщений: 2655
legg
...
или я чего то не понимаю или выглядит так, что вы сравниваете скорость чтения с диска данных таблицы и скорость перебора массива в оперативной памяти.

Вы делаете тройную работу - два раза читаете блоки данных в буфере данных, и еще перебираете массив.
При этом второе чтение у вас всегда сопровождается проверкой - не надо ли соответствующий блок еще раз прочитать в память.

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

Сообщение было отредактировано: 24 ноя 21, 17:31
24 ноя 21, 17:30    [22400314]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Oracle Ответить