Преобразование данных Oracle - Bulk/ForALL не будет работать
Я пытаюсь найти лучший способ перенести данные из таблицы в другую как часть более крупного проекта обработки. Я думал, что смогу сделать это через BULK COLLECT и FORALL и набрать значительную скорость, но я не думаю, что смогу обрабатывать ссылки на отдельные столбцы, используя BULK COLLECT...
У меня есть проект переноса данных / приложений (MSSQL в Oracle 11.2) по наследованию. Я пытаюсь оптимизировать и проверять от начала до конца... Первым шагом процесса является импорт устаревших данных (таблица базы данных, 4,5 млн записей, 170 столбцов, все в строковом формате) в другую таблицу.
Первоначальное преобразование было основано на курсоре, циклически проходя строка за строкой, причем каждый столбец проходил хотя бы одну функцию для очистки / преобразования. Это работало, но в тестовой системе это заняло слишком много времени - более 12 часов, чтобы перевести 4,5 миллиона записей из одной таблицы в другую с очень простыми функциями. В локальной реализации, к которой у меня есть доступ, они ограничены 13000 идентификаторами номеров идентификаторов более 220 тыс. Записей.
Я установил еще более ограниченную систему разработки на своем ноутбуке для тестирования альтернативных методов - и могу увеличить скорость импорта более чем в 5 раз, но это все еще курсор / строка за строкой. Я установил таблицу на NOLOGGING и использую подсказку APPEND. Я тестировал с / без индексов. Я не могу сделать SELECT INTO с этой таблицей размеров - она просто задыхается.
Есть ли другая / лучшая техника? Как еще можно повысить скорость конвертации? Я делаю это неправильно с BULK COLLECT (т.е. есть ли способ ссылки на отдельные поля?)
Если у кого-то есть понимание, пожалуйста, включите! Я включил очень упрощенную версию процедуры, чтобы я мог показать мою попытку использования. Это то же самое (в значительной степени) работает как обычный цикл курсора, но не с подписками FORALL и (i). Ошибка, которую я получаю, была ORA-00913: слишком много значений. Я прошел полный оператор вставки, сопоставляя поля со значениями. Я проверил функции преобразования данных - они работают для обычных столбцов в качестве параметров. Мне интересно, если они не работают с BULK COLLECT и / или FORALL из-за подписки??
ОБНОВЛЕННАЯ ИНФОРМАЦИЯ: Это в системе с ограниченным доступом, и до сих пор (в ожидании учетных записей) мне приходилось удаленно диагностировать "настоящую" (клиентскую) систему DEV, работая на локальной системе - код профилирования, данные, сроки и т. д. Мои рекомендации были внесены другим разработчиком, который сообщит мне результаты. Шутки в сторону. Однако...
@Mark, @Justin - Обычно я избавляюсь от каких-либо курсоров, не совсем? необходимо, и используйте SELECT INTO, где это возможно. Обычно это моя первая рекомендация по старому коду PL/SQL... ("Почему. Итак. Курсор?" С макияжем Джокера). Это первое, что я попробовал в локальной системе, но это просто замедлило работу сервера до сканирования, и я прекратил тестирование. Это было до внедрения сокращенного NOLOGGING - это то, что я попытаюсь, когда смогу прикоснуться к системе разработки.
Посмотрев время, запросы, объединения, индексы и плач, я порекомендовал NOLOGGING и преобразовать его в INSERT /*+ APPEND */ -, который выиграл время в других процессах, в основном в таблицах, построенных из объединений.
re: " OID <= '000052000' " - когда они устанавливали там первый преобразованный код в системе cust dev, им приходилось ограничивать количество записей, которые они конвертировали из таблицы PMS_OHF. Первоначально они могли получить 13000 идентификаторов персонала для обработки в разумные сроки. Эти 13000 ID были бы в записях около 220K, так что это то, что они двигали, когда я вошел на борт. Некоторые переписывают, исправляют соединения, и NOLOGGING/Insert Append сделали достаточно большую разницу, что они продолжили. В локальной системе я думал, что 13000 слишком мало - я не думаю, что получаю значимое сравнение с унаследованным результатом - поэтому я поднял его и увеличил. Я должен быть смелым и попробовать полное преобразование в системе разработки ноутбука - здесь я могу по крайней мере наблюдать, что происходит через EM... правительство не позволит своим администраторам использовать его. (!?)
БОЛЬШАЯ ИНФОРМАЦИЯ: - снова обдумав ошибку 00913 и вспомнив другие проекты, я понял, что более ранние ошибки были, когда более чем один элемент был передан в функцию, которая ожидала один элемент... что указывает мне назад на мои попытки использовать подписанные имена полей в цикле BULK COLLECT. Я пересмотрел несколько презентаций Стивена Фойерштейна YT, и я думаю, что он наконец-то проснулся. Простые примеры из Интернета... Я делал свои шрифты горизонтально, а не вертикально (или наоборот)... чтобы получить мой вызовы функций для работы, я думаю, что я должен сделать ТИП для каждого поля, и массив / таблица этого типа. Внезапно (170 раз) я думаю, что я посмотрю на некоторые уроки Тома Кайта по параллелизму вручную и спрошу у wx, что у меня будет доступ к новому (11.2?) Интерфейсу DBMS_PARALLEL_EXECUTE - в чем я сомневаюсь. Кроме того, не зная больше о системе cust dev, кроме описаний, которые лучше всего назвать "неадекватными", я не знаю, что wx //ism будет огромной помощью. Мне нужно читать на // ism
Все, что я знаю, это то, что мне нужно закончить несколько полных заездов, иначе мне будет неудобно говорить, что наши результаты "достаточно близки" к прежним результатам. У нас может не быть большого выбора по сравнению с многодневным полным прогоном для нашего тестирования.
PROCEDURE CONVERT_FA IS
CURSOR L_OHF IS -- Cursor used to get SOURCE TABLE data
SELECT *
FROM TEST.PMS_OHF -- OHF is legacy data source
where OID <= '000052000' -- limits OHF data to a smaller subset
ORDER BY ID ;
L_OHF_DATA TEST.PMS_OHF%ROWTYPE;
L_SHDATA TEST.OPTM_SHIST%ROWTYPE;
Type hist_Array is table of TEST.PMS_OHF%ROWTYPE;
SHF_INPUT hist_array ;
Type Ohist_Array is table of TEST.OPTM_SHIST%ROWTYPE;
TARG_SHIST ohist_Array ;
n_limit number := 1000 ;
BEGIN
begin
OPEN L_OHF;
LOOP
FETCH L_OHF BULK COLLECT INTO SHF_INPUT LIMIT n_limit ;
FORALL i in 1 .. n_limit
INSERT INTO TEST.OPTM_SHIST
( -- There are 170 columns in target table, requiring diff't xformations
RECORD_NUMBER , UNIQUE_ID , STRENGTH_YEAR_MONTH , FY , FM , ETHNIC ,
SOURCE_CODE_CURR , SOURCE_CODE_CURR_STAT ,
-- ... a LOT more fields
DESG_DT_01 ,
-- and some place holders for later
SOURCE_CALC , PSID , GAIN_CURR_DT_CALC
)
values
( -- examples of xformatiosn
SHF_INPUT.ID(i) ,
'00000000000000000000000' || SHF_INPUT.IOD(i) ,
TEST.PMS_UTIL.STR_TO_YM_DATE( SHF_INPUT.STRYRMO(i) ) ,
TEST.PMS_UTIL.STR_TO_YEAR( SHF_INPUT.STRYRMO(i) ) ,
TEST.PMS_UTIL.STR_TO_MONTH( SHF_INPUT.STRYRMO(i) ) ,
TEST.PMS_UTIL.REMOVE_NONASCII( SHF_INPUT.ETHNIC(i) ) ,
-- ... there are a lot of columns
TEST.PMS_UTIL.REMOVE_NONASCII( SUBSTR( SHF_INPUT.SCCURPRICL(i),1,2 ) ) ,
TEST.PMS_UTIL.REMOVE_NONASCII( SUBSTR( SHF_INPUT.SCCURPRICL(i),3,1 ) ) ,
-- an example of other transformations
( case
when (
(
SHF_INPUT.STRYRMO(i) >= '09801'
AND
SHF_INPUT.STRYRMO(i) < '10900'
)
OR
(
SHF_INPUT.STRYRMO(i) = '10901'
AND
SHF_INPUT.DESCHGCT01(i) = '081'
)
)
then TEST.PMS_UTIL.STR_TO_DATE( SHF_INPUT.DESCHGCT01(i) || SHF_INPUT.DESCHGST01(i) )
else TEST.PMS_UTIL.STR_TO_DATE( SHF_INPUT.DESCHGDT01(i) )
end ),
-- below are fields that will be filled later
null , -- SOURCE_CALC ,
SHF_INPUT.OID(i) ,
null -- GAIN_CURR_DT_CALC
) ;
EXIT WHEN L_OHF%NOTFOUND; -- exit when last row is fetched
END LOOP;
COMMIT;
close L_OHF;
END;
end CONVERT_OHF_FA;
2 ответа
Отбросив это по другим вопросам, я снова поднял это сегодня.
Кто-то прислал мне фрагмент своего похожего кода, и я решил, что собираюсь сесть и просто перебрать проблему: перейти к минимальному количеству столбцов и сопоставить значения, увеличить столбцы / значения и перекомпилировать...
А потом меня поразило... мой индекс был не в том месте.
НЕПРАВИЛЬНАЯ форма:
SHF_INPUT.ID(i) ,
'00000000000000000000000' || SHF_INPUT.IOD(i) ,
TEST.PMS_UTIL.STR_TO_YM_DATE( SHF_INPUT.STRYRMO(i) ) ,
Правильная форма:
SHF_INPUT(i).ID ,
'00000000000000000000000' || SHF_Input(i).IOD ,
TEST.PMS_UTIL.STR_TO_YM_DATE( SHF_Input(i).STRYRMO ) ,
Я виню в этом, глядя на ранние многоколоночные примеры массового сбора, и предполагаю, что смогу превратить их в примеры%ROWTYPE. Я потерял терпение и не проверял.
Спасибо за вашу помощь и рекомендации.
execute immediate 'alter session enable parallel dml';
INSERT /*+ APPEND PARALLEL */ INTO TEST.OPTM_SHIST(...)
SELECT ...
FROM TEST.PMS_OHF
WHER OID <= '000052000';
Это способ делать большие загрузки данных. Не дайте себя одурачить всеми такими модными опциями PL/SQL, как массовый сбор, конвейерные таблицы и т. Д. Они редко бывают быстрее или проще в использовании, чем обычный старый SQL. Основным преимуществом этих возможностей является повышение производительности процесса строка за агонизирующей строкой без значительного рефакторинга.
В этом случае похоже, что в PL/SQL уже практически нет логики. Почти все PL/SQL могут быть выброшены и заменены одним запросом. Это значительно облегчает изменение, отладку, добавление параллелизма и т. Д.
Некоторые другие советы:
ORDER BYвероятно, не полезно для загрузки данных. Если вы не пытаетесь сделать что-то необычное с индексами, например, улучшить фактор кластеризации или перестроить без сортировки.- Убедитесь, что ваши функции объявлены как DETERMINISTIC, если выходные данные всегда идентичны для одного и того же входа. Это может помочь Oracle избежать вызова функции для того же результата. Для еще большей производительности вы можете встроить все функции в оператор SQL, но это может привести к путанице.
- Если вам все еще нужно использовать
BULK COLLECT, используйте подсказкуAPPEND_VALUESнеAPPEND,