Oracle FORALL_Example

优化批量插入数据库性能

最新推荐文章于 2024-07-17 08:15:00 发布

原创最新推荐文章于 2024-07-17 08:15:00 发布 · 97 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

Oracle 专栏收录该内容

84 篇文章

订阅专栏

本文通过比较使用FOR loop和FORALL方法在Oracle中批量插入数据的执行时间，展示了FORALL方法在处理大量数据时的性能优势。


CREATE TABLE blktest (num NUMBER( 20), name varchar2( 50));

CREATE OR REPLACE PROCEDURE bulktest IS
    TYPE numtab IS TABLE OF NUMBER (20) INDEX BY BINARY_INTEGER;

    TYPE nametab IS TABLE OF VARCHAR2 (50) INDEX BY BINARY_INTEGER;
    pnums  numtab;
    pnames nametab;
    t1     NUMBER;
    t2     NUMBER;
    t3     NUMBER;
BEGIN
    FOR j IN 1 .. 1000000
    LOOP
        pnums(j) := j;
        pnames(j) := 'Seq No. ' || to_char(j);
    END LOOP ;

    SELECT dbms_utility.get_time
    INTO   t1
    FROM   dual;

    FOR i IN 1 .. 1000000
    LOOP
        INSERT INTO blktest
        VALUES
            (pnums(i)
            ,pnames(i));
    END LOOP ;

    SELECT dbms_utility.get_time
    INTO   t2
    FROM   dual;

    FORALL i IN 1 .. 1000000
        INSERT INTO blktest

        VALUES
            (pnums(i)
            ,pnames(i));

    SELECT dbms_utility.get_time
    INTO   t3
    FROM   dual;
    dbms_output.put_line( 'Execution Time (hsecs)');
    dbms_output.put_line( '---------------------');
    dbms_output.put_line( 'FOR loop: ' || to_char(t2 - t1));
    dbms_output.put_line( 'FORALL:   ' || to_char(t3 - t2));
END;

Execution Time (hsecs)
---------------------
FOR loop: 5747
FORALL:   90