查询性能差异pl/sql forall插入和普通SQL插入 [英] Query performance difference pl/sql forall insert and plain SQL insert

问题描述

我们一直在使用临时表在pl/sql存储过程中存储中间结果.谁能说出通过pl/sql进行批量收集插入与普通的SQL插入之间是否存在性能差异.

We have been using temporary table to store intermediate results in pl/sql Stored procedure. Could anyone tell if there is a performance difference between doing bulk collect insert through pl/sql and a plain SQL insert.

插入[表名] [选择查询以返回大量数据]

Insert into [Table name] [Select query Returning huge amount of data]

或

[选择查询返回大量数据]的光标

Cursor for [Select query returning huge amount of data]

打开光标

将光标批量收集到集合中

fetch cursor bulk collect into collection

使用FORALL执行插入

Use FORALL to perform insert

以上2个选项中的哪一个最好插入大量临时数据?

Which of the above 2 options is better to insert huge amount of temporary data?.

推荐答案

一些针对您问题的实验数据(Oracle 9.2)

Some experimental data for your problem (Oracle 9.2)

批量收集

DECLARE 
  TYPE t_number_table IS TABLE OF NUMBER;
  v_tab t_number_table;
BEGIN
  SELECT ROWNUM
  BULK COLLECT INTO v_tab
  FROM dual
  CONNECT BY LEVEL < 100000;

  FORALL i IN 1..v_tab.COUNT
    INSERT INTO test VALUES (v_tab(i));
END;
/
-- 2.6 sec

插入

-- test table 
CREATE global TEMPORARY TABLE test (id number)
ON COMMIT preserve ROWS;

BEGIN
  INSERT INTO test
  SELECT ROWNUM FROM dual
  CONNECT BY LEVEL < 100000;
END;
/
-- 1.4 sec

直接路径插入 http://download.oracle.com/docs/cd /B10500_01/server.920/a96524/c21dlins.htm

BEGIN
  INSERT /*+ append */ INTO test
  SELECT ROWNUM FROM dual
  CONNECT BY LEVEL < 100000;
END;
/
-- 1.2 sec

这篇关于查询性能差异pl/sql forall插入和普通SQL插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！