Intereting Posts
MySQL Trigger после обновления только в том случае, если строка изменилась Обходной путь для ORA-00997: незаконное использование LONG-типа данных ORACLE Группирование DDL-заявлений в рамках Execute Immediate Возможно ли запросить имя столбца, указанное пользователем, без использования динамического SQL? SQL SELECT ORDER BY несколько столбцов в зависимости от значения другого столбца Как получить определенные значения записи из базы данных SQL, хранящейся на сервере, и обновить эти значения в базе данных SQLite для Android? Когда следует использовать хранимые процедуры? Оптимизация запроса Oracle Вопрос о синтаксисе sql: * = Как объединить несколько строк в одну строку, оракул Вычисление возраста с рождения с помощью триггера placq oracle и вставка возраста в таблицу Преобразовать временную метку / дату с UTC на EST Oracle SQL сложные строки дерева sql TransactionScope и транзакции Геолокационное расстояние SQL из таблицы городов

update x set y = null занимает много времени

На работе у меня большой стол (около 3 миллионов строк, например 40-50 столбцов). Иногда мне нужно очистить некоторые столбцы и заполнить их новыми данными. Я не ожидал, что

UPDATE table1 SET y = null 

занимает гораздо больше времени, чем заполнение столбца данными, которые генерируются, например, в sql-запросе из других столбцов одной таблицы или запрашиваются из других таблиц в подзапросе. Не имеет значения, прохожу ли я сразу через все строки таблицы (например, в запросе на обновление выше), или если я использую курсор для перехода по таблице по строкам (используя pk). Не имеет значения, использую ли я большую таблицу на работе или создаю небольшую тестовую таблицу и заполняю ее несколькими сотнями тысяч тестовых строк. Установка столбца в значение null всегда занимает больше времени (во всех тестах я обнаружил факторы от 2 до 10), чем обновление столбца с некоторыми динамическими данными (что отличается для каждой строки).

В чем причина этого? Что делает Oracle при установке столбца в null? Или – в чем моя ошибка в рассуждении?

Спасибо за вашу помощь!

PS: Я использую oracle 11g2 и нашел эти результаты, используя разработчик plsql и разработчика oracle sql.

Резюме

Я думаю, что обновление до нуля медленнее, потому что Oracle (неправильно) пытается воспользоваться тем, как он хранит нули, заставляя его часто реорганизовывать строки в блоке («сжатие кучи блоков»), создавая много дополнительных UNDO и ПОВТОРИТЬ.

Что такого особенного в null?

Из концепций базы данных Oracle :

«Нули хранятся в базе данных, если они попадают между столбцами со значениями данных. В этих случаях для хранения длины столбца (ноль) требуется 1 байт.

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

Контрольная работа

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

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

Я использовал утилиту RunStats с http://www.oracle-developer.net/utilities.php, чтобы сравнить потребление ресурсов от обновления к значению с обновлением до нуля.

 create table test1(col1 number); BEGIN dbms_output.enable(1000000); runstats_pkg.rs_start; for i in 1 .. 10 loop execute immediate 'drop table test1 purge'; execute immediate 'create table test1 (col1 number)'; execute immediate 'insert /*+ append */ into test1 select 1 col1 from dual connect by level <= 100000'; commit; execute immediate 'update test1 set col1 = 1'; commit; end loop; runstats_pkg.rs_pause; runstats_pkg.rs_resume; for i in 1 .. 10 loop execute immediate 'drop table test1 purge'; execute immediate 'create table test1 (col1 number)'; execute immediate 'insert /*+ append */ into test1 select 1 col1 from dual connect by level <= 100000'; commit; execute immediate 'update test1 set col1 = null'; commit; end loop; runstats_pkg.rs_stop(); END; / 

результат

Есть десятки различий, это четыре, которые я считаю наиболее актуальными:

 Type Name Run1 Run2 Diff ----- ---------------------------- ------------ ------------ ------------ TIMER elapsed time (hsecs) 1,269 4,738 3,469 STAT heap block compress 1 2,028 2,027 STAT undo change vector size 55,855,008 181,387,456 125,532,448 STAT redo size 133,260,596 581,641,084 448,380,488 

Решения?

Единственное возможное решение, о котором я могу думать, это включить сжатие таблицы. Трюк с конечным нулевым типом не выполняется для сжатых таблиц. Таким образом, даже несмотря на то, что число «компрессионный блок кучи» становится еще выше для Run2, с 2028 по 23208, я думаю, он фактически ничего не делает. Повтор, отмена и истекшее время между двумя прогонами почти идентичны с включенным сжатием таблицы.

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

Является ли индекс Y индексированным? Может быть, установка столбца в значение null означает, что Oracle должен удалить из индекса, а не просто обновить его. Если это так, вы можете сбросить и перестроить его после обновления данных.

РЕДАКТИРОВАТЬ:

Это только столбец Y, который показывает проблему, или он не зависит от обновляемого столбца? Можете ли вы опубликовать определение таблицы, включая ограничения?

Это потому, что он удаляет из блоков данные.

И delete – самая сложная операция. Если вы можете избежать delete , сделайте это.

Я рекомендую вам создать другую таблицу с нулевым столбцом ( Create table as select например, insert select или insert select ), и заполните ее (столбец) своей процедурой. Отбросьте старую таблицу, а затем переименуйте новую таблицу с текущим именем.

ОБНОВИТЬ:

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

 udpate table1 set y = (select new_value from source where source.key = table1.key) 

и будет устанавливать нулевые строки, которые не существуют в источнике.

Я бы попробовал то, что предложил Том Ките на больших обновлениях. Когда дело доходит до огромных таблиц, лучше всего идти так: возьмите несколько строк, обновите их, возьмите еще что-нибудь, обновите их и т. Д. Не пытайтесь опубликовать обновление во всей таблице. Это убийственное движение с самого начала.

В основном создайте индексированную таблицу binary_integer, забирайте по 10 строк за раз и обновляйте их.

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

 DECLARE TYPE BookingRecord IS RECORD ( bprice number, bevent_id number, book_id number ); TYPE array is TABLE of BookingRecord index by binary_integer; l_data array; CURSOR c1 is SELECT LVC_USD_PRICE_V2(ev.activity_version_id,ev.course_start_date,t.local_update_date,ev.currency,nvl(t.delegate_country,ev.sponsor_org_country),ev.price,ev.currency,t.ota_status,ev.location_type) x, ev.title, t.ota_booking_id FROM ota_gsi_delegate_bookings_t@diseulprod t, inted_parted_events_t@diseulprod ev WHERE t.event_id = ev.event_id and t.ota_booking_id = BEGIN open c1; loop fetch c1 bulk collect into l_data limit 20; for i in 1..l_data.count loop update ou_inc_int_t_01 set price = l_data(i).bprice, updated = 'Y' where booking_id = l_data(i).book_id; end loop; exit when c1%notfound; end loop; close c1; END; 

что также может помочь ускорить обновление, так это использовать alter table table1 nologging чтобы обновление не генерировало повторные журналы. Другая возможность – удалить столбец и снова добавить его. поскольку это DDL-операция, она не будет генерировать ни повтора, ни отмены.