Merge vs Insert/Update

Сравнение времени выполнения merge и insert/update.

Выводы: в рамках использованного в тесте распределения данных, лучше всего использовать один оператор merge для всех строк.

Для аналогичную конструкции INSERT/UPDATE в один (а вернее в два) оператора не получилось дождаться окончания запроса. Если обновление будет производиться не из запроса, а из индексированной таблицы, то вполне вероятно это решение будет так же жизнеспособно, но все равно не так удобно как один MERGE.

Если в один оператор поместиться не получается, то быстрее всего отрабатывает UPDATE/SQL%ROWCOUNT/INSERT с одним условием: из 200 000 строк обновляялась (т.е. не вызывала дальнейшей вставки) половина. Если все строки вставляются и UPDATE работает вхолостую, то производительность построчного MERGE получается выше.

SET SERVEROUTPUT ON SET FEEDBACK OFF SET TIMING OFF DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON BEGIN dbms_output.put_line('************************'); dbms_output.put_line('Merge bulk'); dbms_output.put_line('************************'); MERGE INTO tst USING (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i ON (tst.a = i.rn) WHEN MATCHED THEN UPDATE SET b = i.b WHEN NOT MATCHED THEN INSERT (a, b) VALUES (i.rn, i.b); END; / SET TIMING OFF ------------------------------------- -- Окончания этого теста дождаться не удалось -- ПРоблема в UPDATE. -- Разрешится, если вместо запроса у нас будет таблица, например --DROP TABLE tst PURGE; --CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); -- --INSERT INTO tst --SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual --CONNECT BY LEVEL <= 500000; --COMMIT; -- --EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) -- --SET timing ON --BEGIN -- dbms_output.put_line('************************'); -- dbms_output.put_line('INSERT - UPDATE bulk'); -- dbms_output.put_line('************************'); -- -- INSERT INTO tst -- SELECT i.rn, i.b -- FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i -- WHERE NOT EXISTS ( -- SELECT NULL -- FROM tst t -- WHERE t.a = i.rn); -- -- UPDATE tst -- SET b = (SELECT i.b FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i WHERE tst.a = i.rn) -- WHERE EXISTS (SELECT NULL FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i WHERE tst.a = i.rn); --END; --/ --SET TIMING OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON BEGIN dbms_output.put_line('************************'); dbms_output.put_line('Row by row merge'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP MERGE INTO tst USING (SELECT rec.rn rn, rec.b b FROM dual) i ON (tst.a = i.rn) WHEN MATCHED THEN UPDATE SET b = i.b WHEN NOT MATCHED THEN INSERT (a, b) VALUES (i.rn, i.b); END LOOP; END; / SET timing OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON BEGIN dbms_output.put_line('************************'); dbms_output.put_line('Dup val on index'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP BEGIN INSERT INTO tst VALUES (rec.rn, rec.b); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE tst SET b = rec.b WHERE a = rec.rn; END; END LOOP; END; / SET timing OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON DECLARE lCNT NUMBER; BEGIN dbms_output.put_line('************************'); dbms_output.put_line('Select - insert - update'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP SELECT COUNT(*) INTO lCNT FROM tst WHERE a = rec.rn AND ROWNUM = 1; IF lCNT = 0 THEN INSERT INTO tst VALUES (rec.rn, rec.b); ELSE UPDATE tst SET b = rec.b WHERE a = rec.rn; END IF; END LOOP; END; / SET timing OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON DECLARE lCNT NUMBER; BEGIN dbms_output.put_line('************************'); dbms_output.put_line('SQL%ROWCOUNT'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP UPDATE tst SET b = rec.b WHERE a = rec.rn; IF SQL%ROWCOUNT = 0 THEN INSERT INTO tst VALUES (rec.rn, rec.b); END IF; END LOOP; END; / SET timing OFF ------------------------------------- DROP TABLE tst PURGE; CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100)); INSERT INTO tst SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual CONNECT BY LEVEL <= 500000; COMMIT; EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE) SET timing ON DECLARE lCNT NUMBER; BEGIN dbms_output.put_line('************************'); dbms_output.put_line('SQL%ROWCOUNT INSERT ONLY'); dbms_output.put_line('************************'); FOR rec IN (SELECT ROWNUM + 500000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP UPDATE tst SET b = rec.b WHERE a = rec.rn; IF SQL%ROWCOUNT = 0 THEN INSERT INTO tst VALUES (rec.rn, rec.b); END IF; END LOOP; END; / SET timing OFF ************************ Merge bulk ************************ Executed in 5,241 seconds ************************ Row by row merge ************************ Executed in 14,914 seconds ************************ Dup val on index ************************ Executed in 180,681 seconds ************************ Select - insert - update ************************ Executed in 20,483 seconds ************************ SQL%ROWCOUNT ************************ Executed in 14,29 seconds ************************ SQL%ROWCOUNT INSERT ONLY ************************ Executed in 17,425 seconds