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

Ответ 1

Резюме

Я думаю, что обновление до нуля медленнее, потому что Oracle (неправильно) пытается воспользоваться тем, как он хранит значения NULL, заставляя его часто реорганизовывать строки в блоке ( "сжатие кучи" ), создавая много дополнительные UNDO и REDO.

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

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

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

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

Test

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

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

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

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, я думаю, он фактически ничего не делает. Повтор, отмена и прошедшее время между двумя запусками почти идентичны с включенным сжатием таблицы.

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

Ответ 2

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

EDIT:

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

Ответ 3

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

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

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

UPDATE:

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

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

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

Ответ 4

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

В основном создавайте индексированную таблицу 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 [email protected] t,
           [email protected] 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;

Ответ 5

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