Эффективный способ обновить все строки в таблице
У меня есть таблица с большим количеством записей (могли быть больше чем 500 000 или 1 000 000). Я добавил новый столбец в этой таблице, и я должен заполнить значение для каждой строки в столбце, с помощью соответствующего значения строки другого столбца в этой таблице.
Я пытался использовать отдельные транзакции для выбора каждого следующего блока 100 записей и обновить значение для них, но все еще это занимает часы для обновления всех записей в Oracle10, например.
Что самый эффективный путь состоит в том, чтобы сделать это в SQL, не используя некоторые определенные для диалекта функции, таким образом, он работает везде (Oracle, MSSQL, MySQL, PostGre и т.д.)?
ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ: нет никаких вычисляемых полей. Существуют индексы. Используемые сгенерированные SQL-операторы, которые обновляют строку таблицы строкой.
5 ответов
Обычный способ - использовать UPDATE:
UPDATE mytable
SET new_column = <expr containing old_column>
У вас должна быть возможность сделать это с помощью одной транзакции.
Возможно, вам не подойдет, но метод, который я использовал пару раз в прошлом для подобных обстоятельств.
создал updated_ {table_name}, затем выберите вставлять в эту таблицу партиями. После завершения все зависит от Oracle (которого я не знаю и не использую), поддерживающего возможность атомарного переименования таблиц. updated_ {table_name} становится {table_name}, а {table_name} становится исходным_ {table_name}.
В последний раз мне приходилось делать это для сильно индексированной таблицы с несколькими миллионами строк, которые абсолютно точно нельзя было заблокировать на время, необходимое для внесения в нее серьезных изменений.
Вы можете отбросить любые индексы в таблице, затем выполнить вставку, а затем воссоздать индексы.
Как предлагает Марсело:
UPDATE mytable
SET new_column = <expr containing old_column>;
Если это длится слишком долго и не удается из-за ошибок «слишком старый снимок» (например, если выражение запрашивает другую высокоактивную таблицу), и если новое значение для столбца всегда НЕ ПУСТО , вы можете обновлять таблицу партиями:
UPDATE mytable
SET new_column = <expr containing old_column>
WHERE new_column IS NULL
AND ROWNUM <= 100000;
Просто запустите этот оператор, COMMIT, а затем запустите его снова; промыть, повторять, пока не появится сообщение «0 строк обновлено». Это займет больше времени, но вероятность сбоя каждого обновления снижается.
РЕДАКТИРОВАТЬ:
Лучшей альтернативой, которая должна быть более эффективной, является использование DBMS_PARALLEL_EXECUTE API.
Пример кода (из документов Oracle):
DECLARE
l_sql_stmt VARCHAR2(1000);
l_try NUMBER;
l_status NUMBER;
BEGIN
-- Create the TASK
DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask');
-- Chunk the table by ROWID
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'HR', 'EMPLOYEES', true, 100);
-- Execute the DML in parallel
l_sql_stmt := 'update EMPLOYEES e
SET e.salary = e.salary + 10
WHERE rowid BETWEEN :start_id AND :end_id';
DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE,
parallel_level => 10);
-- If there is an error, RESUME it for at most 2 times.
l_try := 0;
l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
l_try := l_try + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask');
l_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask');
END LOOP;
-- Done with processing; drop the task
DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask');
END;
/
Документы Oracle: https://docs.oracle.com/database/121/ARPLS/d_parallel_ex.htm#ARPLS67333
Какая версия базы данных? Посмотрите виртуальные столбцы в 11g:
Добавление столбцов со значением по умолчанию http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-features/ 11g-schemamanagement.html