Преобразование данных 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: Too Many Values. Я рассмотрел полный оператор вставки, сопоставляя поля со значениями. Я проверил функции преобразования данных - они работают для обычных столбцов в качестве параметров. Мне интересно, не работают ли они с BULK COLLECT и/или FORALL из-за индексов??

ОБНОВЛЕННАЯ ИНФОРМАЦИЯ: это в системе с ограниченным доступом, и до сих пор (ожидание учетных записей) мне приходилось удаленно диагностировать «настоящую» (клиентскую) систему DEV, работая с локальная система — код профилирования, данные, время и т. д. Мои рекомендации были внесены другим разработчиком, который сообщал мне результаты. Шутки в сторону. Однако... @Mark, @Justin - Обычно я бы избавлялся от любых курсоров, а не?Абсолютно? необходимо, и используйте SELECT INTO, где это возможно. Обычно это моя первая рекомендация по старому коду PL/SQL... ("Почему. Так. Курсор?" в гриме Джокера). Это первое, что я попробовал в локальной системе, но это просто замедлило работу сервера, и я прекратил тестирование. Это было до того, как был реализован сокращенный NOLOGGING — это то, что я попытаюсь сделать, когда смогу прикоснуться к системе разработки.
Посмотрев на время, запросы, соединения, индексы и поплакав, я рекомендовал NOLOGGING и преобразование в INSERT /* + APPEND */ -- что позволило выиграть время другим процессам, в основном таблицам, созданным на основе объединений.

re: "OID ‹= '000052000'" - когда они установили там первый преобразованный код в системе cust dev, им пришлось ограничить количество записей, которые они преобразовали из таблицы PMS_OHF. Первоначально они могли получить 13000 идентификаторов персонала для обработки за разумное время. Эти 13 000 идентификаторов будут составлять около 220 000 записей, так что это то, что они перемещали, когда я пришел на борт. Некоторые перезаписи, исправления соединения и добавление NOLOGGING/Insert Append имели достаточно большое значение, чтобы они продолжались. В локальной системе я подумал, что 13000 слишком мало — я не думаю, что получу осмысленное сравнение с устаревшим результатом — поэтому я увеличил его и увеличил. Я должен быть смелым и попробовать полное преобразование в системе разработки ноутбука - здесь я могу, по крайней мере, наблюдать за тем, что происходит через EM ... правительство не позволит своим администраторам баз данных использовать его. (!?)

ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ: -- после того, как снова обдумал ошибку 00913 и вспомнил о других проектах, я понял, что более ранние ошибки возникали, когда в функцию, которая ожидала один элемент, передавалось более одного элемента... что указывает мне на то, что я пытался использовать имена полей с индексами в цикле BULK COLLECT. Я пересмотрел пару презентаций Стивена Фейерштейна на YT и думаю, что наконец понял. Простые веб-примеры... Я делал свои шрифты горизонтально, а не вертикально (или наоборот)... чтобы получить вызовы функций для работы, я думаю, что мне нужно создать ТИП для каждого поля и МАССИВ/ТАБЛИЦУ этого ТИПА. Внезапно (170 раз) я думаю, что я посмотрю некоторые уроки Тома Кайта по ручному параллелизму и спрошу wx, у меня будет доступ к новому (11.2?) интерфейсу DBMS_PARALLEL_EXECUTE - в чем я сомневаюсь. Кроме того, не зная больше о системе cust dev, кроме описаний, которые лучше всего назвать «неадекватными», я не знаю, что wx //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;