Oracle - МАССОВОЕ ОБНОВЛЕНИЕ с CURSOR LOOP

Я хочу выполнить обновление огромной таблицы на такой таблице (сейчас это не лучшая практика):

TARGET_TABLE (
TICKET_ID number,
product_id number,
NET number(15,2),
VAT number(15,2));

http://sqlfiddle.com/#!4/d39ed/3

Цель: UPDATE TARGET_TABLE set NET=VAT, VAT=NET

Я придумал МАССОВОЕ ОБНОВЛЕНИЕ, но получаю ORA-00913: «Многие значения» в строке 43, что я не могу объяснить. Кроме того, я не знаю, как обновить две строки сразу в этом варианте. Может ли кто-нибудь помочь?

DECLARE

-- new data
    CURSOR new_data_cur IS
      select 
                     a.rowid, 
                     a.TICKET_ID,
                     a.product_id,
                     b.NET,
                     b.VAT
 from TARGET_TABLE a
                     join TARGET_TABLE_COPY b
                     on  ( a.TICKET_ID=b.TICKET_ID AND  a.product_id =b.product_id ) ;

    TYPE new_data_type IS TABLE OF new_data_cur%rowtype INDEX BY PLS_INTEGER;
    new_data_tab       new_data_type;
    TYPE row_id_type IS TABLE OF ROWID INDEX BY PLS_INTEGER;
    row_id_tab         row_id_type;
    TYPE rt_update_cols IS RECORD (
        NET   TARGET_TABLE.NET%TYPE
      --  VAT   TARGET_TABLE.VAT%TYPE
    );
    TYPE update_cols_type IS
        TABLE OF rt_update_cols INDEX BY PLS_INTEGER;
    update_cols_tab1    update_cols_type;
    --update_cols_tab2    update_cols_type;
    dml_errors EXCEPTION;
    PRAGMA exception_init ( dml_errors,-24381 );

BEGIN

    OPEN new_data_cur;
    LOOP
        FETCH new_data_cur BULK COLLECT INTO new_data_tab LIMIT 50000;
        EXIT WHEN new_data_tab.count=0;
        FOR i IN new_data_tab.first..new_data_tab.last LOOP
            row_id_tab(i) := new_data_tab(i).rowid;
            update_cols_tab1(i).NET := new_data_tab(i).VAT;
           -- update_cols_tab2(i).VAT := new_data_tab(i).NET;
        END LOOP;

        FORALL i IN new_data_tab.first..new_data_tab.last SAVE EXCEPTIONS # ORA-00913: To many values
            UPDATE TARGET_TABLE
           -- SET row = update_cols_tab(i)
            SET row = update_cols_tab1(i) 
           --         row = update_cols_tab2(i) 
            WHERE ROWID = row_id_tab(i);

        COMMIT;
        EXIT WHEN new_data_tab.count=0;
    END LOOP;
    COMMIT;
    CLOSE new_data_cur;

    EXCEPTION
    WHEN dml_errors THEN

            FOR i IN 1..SQL%bulk_exceptions.count LOOP
            dbms_output.put_line('Some error occured');
            END LOOP;
    END;

person royskatt    schedule 05.10.2018    source источник
comment
Почему ты так поступаешь, когда можешь просто сделать UPDATE TARGET_TABLE set NET=VAT, VAT=NET;?   -  person Boneist    schedule 05.10.2018
comment
›500 000 000 записей   -  person royskatt    schedule 05.10.2018
comment
Так? Тем не менее будет быстрее обновить таблицу в одном операторе обновления, чем выполнять массовое обновление.   -  person Boneist    schedule 05.10.2018
comment
Как вариант, можно попробовать переименовать столбцы (net -> net_vat, vat -> net, net_vat -> vat). Это предполагает, что ничего не делает select * или вставляет в target_table без указания списка столбцов, вставляемых в ваш производственный код. Однако вы можете изменить порядок столбцов с помощью dbms_redefinition, если вам это абсолютно необходимо.   -  person Boneist    schedule 05.10.2018
comment
В качестве альтернативы вы можете временно добавить столбец UP2DATE INTEGER DEFAULT 0, а затем выполнить UPDATE TARGET_TABLE set NET=VAT, VAT=NET, UP2DATE = 1 WHERE UP2DATE = 0 AND ROWNUM < 10000, чтобы обновить фрагмент данных...   -  person Radagast81    schedule 05.10.2018
comment
@Boneist Не уверен, что один оператор обновления работает быстрее. Часто у вас возникают проблемы с сегментами отмены, когда вам нужно обновить огромные объемы данных, поэтому вы хотите делать это по частям, чтобы избежать подобных проблем...   -  person Radagast81    schedule 05.10.2018
comment
@ Radagast81, у вас может закончиться табличное пространство отмены, это правда. Но это не влияет на производительность, если табличное пространство отмены достаточно велико. Один единственный оператор обновления для всех строк будет быстрее, чем цикл по одной и той же таблице, какими бы большими ни были куски (теперь вы вводите переключение контекста, не говоря уже о повторных сканированиях таблицы/индекса!   -  person Boneist    schedule 05.10.2018
comment
@Boneist да, у вас может закончиться табличное пространство, но чаще вы получаете подкачку оперативной памяти. Таким образом, небольшое количество переключений контекста и сканирования индекса может быть быстрее, если вы можете избежать подкачки оперативной памяти на другой стороне...   -  person Radagast81    schedule 05.10.2018
comment
@Boneist Это никогда не закончится   -  person royskatt    schedule 05.10.2018


Ответы (1)


Я считаю, что вам не нужен дополнительный курсор, где вы меняете значения

FOR i IN new_data_tab.first..new_data_tab.last LOOP
        row_id_tab(i) := new_data_tab(i).rowid;
        update_cols_tab1(i).NET := new_data_tab(i).VAT;
       -- update_cols_tab2(i).VAT := new_data_tab(i).NET;
    END LOOP;

Таким образом, ваш код будет использовать эти значения в массовом обновлении.

DECLARE

-- new data
CURSOR new_data_cur IS
  select 
                 a.rowid, 
                 a.TICKET_ID,
                 a.product_id,
                 b.NET,
                 b.VAT
   from TARGET_TABLE a
                 join TARGET_TABLE_COPY b
                 on  ( a.TICKET_ID=b.TICKET_ID AND  a.product_id =b.product_id ) ;

TYPE new_data_type IS TABLE OF new_data_cur%rowtype INDEX BY PLS_INTEGER;
new_data_tab       new_data_type;
TYPE row_id_type IS TABLE OF ROWID INDEX BY PLS_INTEGER;
row_id_tab         row_id_type;
TYPE rt_update_cols IS RECORD (
    NET   TARGET_TABLE.NET%TYPE
  --  VAT   TARGET_TABLE.VAT%TYPE
);
TYPE update_cols_type IS
    TABLE OF rt_update_cols INDEX BY PLS_INTEGER;
update_cols_tab1    update_cols_type;
--update_cols_tab2    update_cols_type;
dml_errors EXCEPTION;
PRAGMA exception_init ( dml_errors,-24381 );

BEGIN

OPEN new_data_cur;
LOOP
    FETCH new_data_cur BULK COLLECT INTO new_data_tab LIMIT 50000;
    EXIT WHEN new_data_tab.count=0;


    FORALL i IN new_data_tab.first..new_data_tab.last SAVE EXCEPTIONS # ORA-00913: To many values
        UPDATE TARGET_TABLE
       -- SET row = update_cols_tab(i)
       -- SET row = update_cols_tab1(i) 
       --         row = update_cols_tab2(i) 
        NET =  update_cols_tab1(i).VAT
        VAT = update_cols_tab1(i).NET
        WHERE ROWID = row_id_tab(i);

    COMMIT;
    EXIT WHEN new_data_tab.count=0;
END LOOP;
COMMIT;
CLOSE new_data_cur;

EXCEPTION
WHEN dml_errors THEN

        FOR i IN 1..SQL%bulk_exceptions.count LOOP
        dbms_output.put_line('Some error occured');
        END LOOP;
END;
person kapil    schedule 05.10.2018