В таблице много записей. Добавлена новая колонка со значением NULL.
Нужно присвоить новое значение этой колонке в каждой строке. Для этого много раз выполняется следующий код:
UPDATE TOP(100000) table
SET col = value
WHERE col IS NULL
Как это ускорить?
Правильно ли я понимаю, что при каждом выполнении такого кода будет выполнять скан по всей таблице, пока не найдутся NOT NULL значения. Т.е. по мере обновления таблицы скорость поиска NULL будет замедляться. Грубо говоря сложность обновления всех записей таблицы O(N^2)
Что произойдет, если добавить индекс по колонке col — индекс для поиска NULL? Ускорится выполнение обновления за счет быстрого поиска NULL, или замедлится за счет постоянного обновления индекса при обновлении таблицы?
Напишите миграцию на c#/java. В таблице наверняка есть PK.
Выберите все PK в одной транзакции.
Потом в цикле делайте транзакцию для пакета и обновляйте колонку по PK.
Обработанные PK убираете из очереди.
Здравствуйте, B7_Ruslan, Вы писали:
B_R>Напишите миграцию на c#/java. В таблице наверняка есть PK. B_R>Выберите все PK в одной транзакции. B_R>Потом в цикле делайте транзакцию для пакета и обновляйте колонку по PK. B_R>Обработанные PK убираете из очереди.
Почему не на SQL? Если правильно понял:
Создаем новую временную таблицу для хранения обновляемых значений:
— id (PK) — автоинкремент
— updating_table_pk — первичный ключ обновляемой таблицы
-- непонятно как на лету создать autoincrement-поле, это возможно?CREATE TABLE temp
AS
SELECT id (AS AUTOINCREMENT), table.pk AS updating_table_pk
FROM table
WHERE col IS NULL-- далееUPDATE table
SET col = value
WHERE table.pk IN (
SELECT updating_table_pk
FROM temp
WHERE id BETWEEN start AND start+batch_size
)
-- следующий батч
start := start + batch_size
Здравствуйте, Буравчик, Вы писали:
Б>Правильно ли я понимаю, что при каждом выполнении такого кода будет выполнять скан по всей таблице, пока не найдутся NOT NULL значения. Т.е. по мере обновления таблицы скорость поиска NULL будет замедляться. Грубо говоря сложность обновления всех записей таблицы O(N^2)
Не понятно откуда тут N^2, тут же вроде просто один раз пройти всю таблицу, это вроде N
Не проще сделать значение по умолчанию (default) на это поле?
Кстати это можно было сделать прямо при добавлении колонки, тогда даже 1 раз не надо было бы вызвать.
Здравствуйте, Буравчик, Вы писали:
Б>Как это ускорить?
1. Обновить все сразу, если возможно.
2. Использовать подходящий индекс, например по PK, если есть. Разбить множество значений ключа на диапазоны, и обновлять их в цикле.
Б>Правильно ли я понимаю, что при каждом выполнении такого кода будет выполнять скан по всей таблице, пока не найдутся NOT NULL значения. Т.е. по мере обновления таблицы скорость поиска NULL будет замедляться. Грубо говоря сложность обновления всех записей таблицы O(N^2)
Могут быть нюансы, но в общем да.
Б>Что произойдет, если добавить индекс по колонке col — индекс для поиска NULL? Ускорится выполнение обновления за счет быстрого поиска NULL, или замедлится за счет постоянного обновления индекса при обновлении таблицы?
Такой индекс при апдейте выше использоваться не будет. В обычном индексе по одной колонке null значения не сохраняются. Такая особенность реализации в Oracle.
Здравствуйте, bnk, Вы писали:
bnk>Не понятно откуда тут N^2, тут же вроде просто один раз пройти всю таблицу, это вроде N
Этот update повторяется сотни/тысячи раз
bnk>Не проще сделать значение по умолчанию (default) на это поле? bnk>Кстати это можно было сделать прямо при добавлении колонки, тогда даже 1 раз не надо было бы вызвать.
Здравствуйте, Буравчик, Вы писали:
Б>Этот update повторяется сотни/тысячи раз
Понятно
bnk>>Кстати это можно было сделать прямо при добавлении колонки, тогда даже 1 раз не надо было бы вызвать.
Б>Так проще, но это надолго блокирует всю таблицу
Но добавление колонки, даже без дефолта, ее же все равно блокирует, или?
Здравствуйте, wildwind, Вы писали:
W>Такой индекс при апдейте выше использоваться не будет. В обычном индексе по одной колонке null значения не сохраняются. Такая особенность реализации в Oracle.
Спасибо. Этот вопрос больше все интересовал.
А если обхитрить, сделать индекс из несколькиз колонок, например, (NULLABLE + PK)?
Здравствуйте, Буравчик, Вы писали: Б>Как это ускорить?
Это не ускорить.
Б>Правильно ли я понимаю, что при каждом выполнении такого кода будет выполнять скан по всей таблице, пока не найдутся NOT NULL значения. Т.е. по мере обновления таблицы скорость поиска NULL будет замедляться. Грубо говоря сложность обновления всех записей таблицы O(N^2)
Неа. Там время поиска будет слишком малым по сравнению со временем записи новых данных.
Б>Что произойдет, если добавить индекс по колонке col — индекс для поиска NULL? Ускорится выполнение обновления за счет быстрого поиска NULL, или замедлится за счет постоянного обновления индекса при обновлении таблицы?
Ускорится, за счет ускорения чтения блоков. Индекс компактнее таблицы, поэтому контроллер его тупо быстрее почитает в память.
Но самое главное — блокировка будет накладываться на индекс, а не на таблицу. Я подозреваю, что проблема как раз в блокировке, а не во времени обновления. Поэтому:
1. Добавляем новый столбец.
2. Создаем на него индекс.
3. Делаем update table set newcol = value без where или top N.
Ты рад что можно пойти выпить кофе, пользователи рады что все работает, процессор радостно двигает цифирки, ссд в полной тишине перелопачивают блоки. Ну идиллия жеж...
Всё, что нас не убивает, ещё горько об этом пожалеет.
Здравствуйте, Ромашка, Вы писали:
Р>1. Добавляем новый столбец. Р>2. Создаем на него индекс. Р>3. Делаем update table set newcol = value без where или top N.
Разве в этом случае не будет заблокирована каждая строка таблицы до завершения обновления? Что-то слабо верится.
Здравствуйте, Буравчик, Вы писали:
Б>А если обхитрить, сделать индекс из несколькиз колонок, например, (NULLABLE + PK)?
Заставить Oracle использовать индекс можно, но выигрыша, скорее всего не будет. Вероятно будет проигрыш.
Если регламент позволяет даунтайм и нет прочих ограничений (типа FK, которые нельзя трогать по другим причинам), наиболее быстрый способ все провернуть это CTAS, create table as select в новую таблицу, с нумерацией из сиквенса, с order by в нужном порядке.
Здравствуйте, wildwind, Вы писали: Р>>Но самое главное — блокировка будет накладываться на индекс, а не на таблицу. W>С чего это? И на таблицу, и на индекс.
А на таблицу-то зачем? Я не большой специалист по блокировкам в оракле, но чет мне быстрый гуглинг подсказывает, что блокировки на таблицу не будет. Не вижу причин для нее.
Р>>3. Делаем update table set newcol = value без where или top N. W>Если бы value было константой, и update не нужен был бы. Ему же нужно пронумеровать строки.
Да пофиг. Если может с топ, значит сможет и без топ.
Всё, что нас не убивает, ещё горько об этом пожалеет.
Здравствуйте, Ромашка, Вы писали:
Р>А на таблицу-то зачем? Я не большой специалист по блокировкам в оракле, но чет мне быстрый гуглинг подсказывает, что блокировки на таблицу не будет. Не вижу причин для нее.
Можно ссылку из гугла?
Я думаю, что перед обновлением таблицы СУБД наложит эксклюзивную блокировку на каждую запись таблицы.
И скорее всего, СУБД, видя, что заблокированы все записи, наложит блокировку на всю таблицу целиком, т.е. таблица будет полностью заблокирована и на чтение тоже.
Вроде, по-умолчанию так и в Oracle, и в MSSQL (инфа из интернета). Интересно, а как в Postgres будет?
Здравствуйте, wildwind, Вы писали:
W>Могут быть нюансы, но в общем да.
Сделал замеры, "квадратичность" проявляется. И уже после 20 млн записей выгоднее создать индекс (см. ниже).
W>Такой индекс при апдейте выше использоваться не будет. В обычном индексе по одной колонке null значения не сохраняются. Такая особенность реализации в Oracle.
Помог BITMAP индекс по NULL колонке, квадратичность ушла.
Вернее, почти ушла, чуток осталось (судя по замерам). Если правильно понял, то из-за того, что BITMAP можно создать только локальным, то при работе с таким индексом просматриваются повторно индекс в уже обработанных партициях.
Здравствуйте, Буравчик, Вы писали:
Б>Я думаю, что перед обновлением таблицы СУБД наложит эксклюзивную блокировку на каждую запись таблицы.
Не на каждую, а на каждую изменяемую. И не всегда "перед", иногда "в процессе".
Б>И скорее всего, СУБД, видя, что заблокированы все записи, наложит блокировку на всю таблицу целиком, т.е. таблица будет полностью заблокирована и на чтение тоже.
Нет. Заблокировать таблицу на чтение в Oracle практически невозможно.
Здравствуйте, Ромашка, Вы писали:
Р>А на таблицу-то зачем? Я не большой специалист по блокировкам в оракле, но чет мне быстрый гуглинг подсказывает, что блокировки на таблицу не будет. Не вижу причин для нее.
Я, разумеется, имел в виду блокировку строк таблицы (и индекса). На всю таблицу блокировки тоже накладываются, но разделяемые.
Здравствуйте, wildwind, Вы писали:
W>Не на каждую, а на каждую изменяемую. И не всегда "перед", иногда "в процессе".
У меня отрывистые знания про блокировки.
Не понял, если мы делаем update table set col = seq.nextval для всех строк одновременно, то какие строки будут заблокированы и когда?
Будет ли доступно в других транзакция чтение строк, изменение, удаление и добавление?
Как из из этих операций будут доступны? Желательно про oracle, но можно и в общем
P.S. Посоветуйте литературу про блокировки и лучшие практики
Слишком общие вопросы, чтобы на них ответить однозначно.
Б>Не понял, если мы делаем update table set col = seq.nextval для всех строк одновременно, то какие строки будут заблокированы и когда?
Сначала будут блокироваться строки по мере доступа к ним, а потом будет выполнена эскалация блокировки на всю таблицу, когда количество заблокированных строк превысит некую величину.
(В перечеркнутом ошибка вышла, это не про oracle, а про sql server).
Б>Будет ли доступно в других транзакция чтение строк, изменение, удаление и добавление? Б>Как из из этих операций будут доступны? Желательно про oracle, но можно и в общем
Применительно к oracle? Выборка без указаний блокировок будет доступна, остальное будет зависеть от того , будет ли выполнена эскалация, будут ли затронуты одинаковые строки.
Б>P.S. Посоветуйте литературу про блокировки и лучшие практики
Здравствуйте, vladislav_somov, Вы писали:
_>а потом будет выполнена эскалация блокировки на всю таблицу, когда количество заблокированных строк превысит некую величину.
Здравствуйте, Буравчик, Вы писали:
Б>Не понял, если мы делаем update table set col = seq.nextval для всех строк одновременно, то какие строки будут заблокированы и когда?
Если меняются все строки, значит все и будут заблокированы. Насчет "когда именно" могут быть нюансы реализации, но обычно они блокируются по мере выборки и изменения.
Б>Будет ли доступно в других транзакция чтение строк, изменение, удаление и добавление? Б>Как из из этих операций будут доступны? Желательно про oracle, но можно и в общем
Все будут доступны, с учетом требований согласованности, которые определяются уровнем изоляции всех участвующих транзакций, наложенными ими блокировками, а также моментом их начала. Деталей слишком много, чтобы изложить их в двух словах.
Б>P.S. Посоветуйте литературу про блокировки и лучшие практики
