Как оптимизировать обновление SQL, который работает на таблице Oracle с 700M строки
UPDATE [TABLE] SET [FIELD]=0 WHERE [FIELD] IS NULL
[ТАБЛИЦА] является таблицей базы данных Oracle больше чем с 700 миллионами строк. Я отменил выполнение SQL после того, как оно работало в течение 6 часов.
Есть ли какая-либо подсказка SQL, которая могла улучшить производительность? Или какое-либо другое решение ускорить это?
Править: Этот запрос будет выполнен однажды и затем никогда снова.
5 ответов
Прежде всего, это разовый запрос или это повторяющийся запрос? Если вам нужно сделать это только один раз, вы можете захотеть выполнить запрос в параллельном режиме. В любом случае вам придется сканировать все строки, вы можете либо самостоятельно разделить рабочую нагрузку с помощью диапазонов ROWID (самодельный параллелизм), либо использовать встроенные функции Oracle.
Предполагая, что вы хотите запускать его часто и хотите оптимизировать этот запрос, количество строк со столбцом field как NULL в конечном итоге будет небольшим по сравнению с общим количеством строк. В этом случае индекс может ускорить процесс. Oracle не индексирует строки, все индексированные столбцы которых имеют значение NULL, поэтому индекс в поле не будет использоваться вашим запросом (поскольку вы хотите найти все строки, в которых поле имеет значение NULL ).
Либо:
- создать индекс для
(FIELD, 0),0будет действовать как псевдостолбец, отличный от NULL, и все строки будут проиндексированы в таблице. создать функциональный индекс для
(CASE WHEN field IS NULL THEN 1 END), это будет индексировать только строки, которые имеют NULL (поэтому индекс будет очень компактным). В этом случае вам придется переписать свой запрос:UPDATE [TABLE] SET [FIELD] = 0 WHERE (CASE WHEN field IS NULL THEN 1 END) = 1
Edit:
Поскольку это один -time, вы можете использовать подсказку PARALLEL :
SQL> EXPLAIN PLAN FOR
2 UPDATE /*+ PARALLEL(test_table 4)*/ test_table
3 SET field=0
4 WHERE field IS NULL;
Explained
SQL> select * from table( dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4026746538
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 22793 | 289K| 12 (9)| 00:00:
| 1 | UPDATE | TEST_TABLE | | | |
| 2 | PX COORDINATOR | | | | |
| 3 | PX SEND QC (RANDOM)| :TQ10000 | 22793 | 289K| 12 (9)| 00:00:
| 4 | PX BLOCK ITERATOR | | 22793 | 289K| 12 (9)| 00:00:
|* 5 | TABLE ACCESS FULL| TEST_TABLE | 22793 | 289K| 12 (9)| 00:00:
--------------------------------------------------------------------------------
Винсент уже отлично ответил на ваш вопрос, но я ' Мне любопытно, «почему» за этим действием. Почему вы обновляете все значения NULL до 0?
С уважением, Роб.
Некоторые предложения:
Удалите все индексы, содержащие FIELD, перед выполнением оператора UPDATE, а затем повторно добавьте их позже.
Напишите для этого процедуру PL / SQL, которая фиксируется после каждых 1000 или 10000 строк.
Надеюсь, это поможет.
Вы можете получить тот же результат без обновления, используя таблицу ALTER, чтобы установить для столбцов «DEFAULT» значение 0.
Обновляют ли другие пользователи одни и те же строки в таблице одновременно?
Если да, то вы можете столкнуться с множеством проблем параллелизма (ожидание блокировок), и это может стоить разбивая его на более мелкие транзакции.
DECLARE
v_cnt number := 1;
BEGIN
WHILE v_cnt > 0 LOOP
UPDATE [TABLE] SET [FIELD]=0 WHERE [FIELD] IS NULL AND ROWNUM < 50000;
v_cnt := SQL%ROWCOUNT;
COMMIT;
END LOOP;
END;
/
Чем меньше ограничение ROWNUM, тем меньше проблем с параллелизмом / блокировкой вы столкнетесь, но тем больше времени вы потратите на сканирование таблицы.