Как оптимизировать SQL-обновление, которое выполняется в таблице Oracle с числами 700M 

UPDATE [TABLE] SET [FIELD]=0 WHERE [FIELD] IS NULL

[TABLE] - таблица базы данных Oracle с более чем 700 миллионами строк. Я отменил выполнение SQL после того, как он работал в течение 6 часов.

Есть ли подсказка SQL, которая может повысить производительность? Или любое другое решение для ускорения этого?

EDIT: Этот запрос будет запускаться один раз, а затем снова никогда.

Ответы

Ответ 1

Прежде всего, это одноразовый запрос или это повторяющийся запрос? Если вам нужно сделать это только после того, как захотите просмотреть запрос в параллельном режиме. Вам все равно придется сканировать все строки, вы можете либо разделить рабочую нагрузку с диапазонами ROWID (do-it-yourself parallelism), либо использовать встроенные функции Oracle.

Предполагая, что вы хотите часто запускать его и хотите оптимизировать этот запрос, количество строк с столбцом field как NULL в конечном итоге будет небольшим по сравнению с общим количеством строк. В этом случае индекс может ускорить процесс. Oracle не индексирует строки, у которых все индексированные столбцы равны NULL, поэтому индекс в field не будет использоваться вашим запросом (так как вы хотите найти все строки, где field равно 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:

Поскольку это одноразовый сценарий, вы можете использовать подсказку 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:
--------------------------------------------------------------------------------

Ответ 2

Другие пользователи одновременно обновляют одни и те же строки в таблице?

Если это так, вы можете столкнуться с множеством проблем concurrency (ожидая блокировок), и это может стоить разбить его на более мелкие транзакции.

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 ограничивает меньше проблем с concurrency/​​блокировкой, тем больше времени вы потратите на сканирование таблицы.

Ответ 3

Винсент уже отлично ответил на ваш вопрос, но мне любопытно, почему "за" за этим действием. Почему вы обновляете все NULL до 0?

С уважением, Роб.

Ответ 4

Некоторые предложения:

  • Отбросьте любые индексы, содержащие FIELD, перед запуском вашего оператора UPDATE, а затем снова добавьте их позже.

  • Напишите процедуру PL/SQL, которая будет выполняться после каждых 1000 или 10000 строк.

Надеюсь, что это поможет.

Ответ 5

Вы можете получить тот же результат без обновления, используя таблицу ALTER, чтобы установить для столбцов значение "DEFAULT" равным 0.