Оптимизация запроса для обновления миллионов данных в Oracle?

У меня есть 3 таблицы:

  • Таблица A: 170 миллионов данных
  • Таблица B: 170 миллионов данных
  • Таблица C: 130 миллионов данных
  • Журнал таблицы

Я хочу обновить столбец colA1 со значением colC4, если colA2 == colB2 и colB3< /em> == colC3. Я создал таблицу:

  • Таблица D: 80 миллионов данных для хранения colB2 и colC3 для ускорения выполнения запроса.

Я также добавляю индекс для colB3 в таблицу D.

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

В настоящее время мой сценарий обновления выглядит следующим образом:

v_str_log := 'Begin updating';
p_write_to_log(v_str_log);
commit;

for data_list in
(
    select distinct colC4
    from tableC
)
loop
    update tableA
    set colA1 = data_list.colC4
    where colA2 in
    (
        select colB2
        from tableD
        where colC3 = data_list.colC4
    )
    var_total := var_total + sql%rowcount;
    v_str_log := 'Updated ' || sql%rowcount || ' for ' || card.sim_type || ' with total ' || var_total || ' rows.';
    commit;
end loop;

Я запустил процедуру, и она закончилась примерно через 6 часов. Но я обнаружил из журнала, что для первого цикла 3 миллиона данных выполняются в течение 3 минут, но через несколько итераций 5 миллионов данных выполняются примерно за 20 минут. Запрос выполняется не так быстро, как первые итерации

Почему это могло произойти? Можно ли оптимизировать скрипт?


sql oracle join sql-optimization
person Hary Kamaro    schedule 16.01.2019    source источник
comment
Посмотрите инструкцию MERGE INTO   -  person q4za4    schedule 16.01.2019
comment
Почему бы не использовать BULK COLLECT или FORALL, если нужно обновить миллионы записей? Взгляните на это   -  person Jacob    schedule 16.01.2019
comment
И рассмотрите возможность удаления инструкции фиксации из внутреннего цикла. Это также сэкономит вам много времени. Если что-то рухнет во время выполнения - скажем, после 30 000 000 записей, эти 30 000 000 записей в исключении все еще будут ждать фиксации.   -  person q4za4    schedule 16.01.2019
comment
@ q4za4 большое спасибо за ваше предложение об удалении фиксации из цикла. Это сокращает время выполнения примерно с 4 часов до 2 часов. :)   -  person Hary Kamaro    schedule 17.01.2019
comment
@ q4za4 q4za4 команда MERGE INTO значительно улучшит приведенный выше запрос или только для удобства чтения?   -  person Hary Kamaro    schedule 17.01.2019
comment
Добро пожаловать :) Для таких больших наборов данных - это должно улучшить вашу производительность :)   -  person q4za4    schedule 24.01.2019

Ответы (1)


arrow_upward
1
arrow_downward

Вы должны посмотреть на обновляемый вид присоединения особенность Oracle - это как раз ваш вариант использования.

Сначала соедините все таблицы в подзапросе (это будет эффективно сделано с помощью хэш-соединения без медленных циклов построчно).

Чем UPDATE подзапрос

Пример

create table a
(col1 number,
col2 number);

create table b
(col2 number,
col3 number);

create table c
(col3 number,
col4 number);

insert into a values(null,1);
insert into b values(1,2);
insert into c values(2,1);


update (
select a.col1, c.col4
from a 
join b on a.col2 = b.col2
join c on b.col3 = c.col3
)
set col1 = col4
;

Обратите внимание, что у UJV есть предварительное условие. При пропуске это приводит к

ORA-01779: невозможно изменить столбец, который сопоставляется с таблицей без сохранения ключа

В вашем случае вы должны поддерживать таблицу B и C с уникальным индексом, чтобы гарантировать представление с сохранением ключа

create unique index c_idx on c(col3);
create unique index b_idx on b(col2);

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

person Marmite Bomber    schedule 16.01.2019