Bulk Insert into Oracle Database: Which Is Better: for Cursor Loop or a Simple Select

Bulk Insert into Oracle database: Which is better: FOR Cursor loop or a simple Select?

I would recommend the Select option because cursors take longer.

Also using the Select is much easier to understand for anyone who has to modify your query

Bulk Insert into Oracle database: Which is better: FOR Cursor loop or a simple Select?

I would recommend the Select option because cursors take longer.

Also using the Select is much easier to understand for anyone who has to modify your query

Best way to do multi-row insert in Oracle?

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

Query performance difference pl/sql forall insert and plain SQL insert

Some experimental data for your problem (Oracle 9.2)

bulk collect

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

insert

-- 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

direct path insert
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

BULK COLLECT in Oracle runs into an infinite loop

EXIT the loop when there are no more rows and CLOSE your cursor (yes, it should be implicitly closed but its better to get into good habits and always close it for when you work with a language/driver where the cursors aren't implicitly closed):

create or replace package body emp_pkg Is

PROCEDURE p_displayEmpName
IS
CURSOR c_rec IS select * from emp where deptno = 30;
v_tbl_emp tbl_emp;
BEGIN
open c_rec;
loop
EXIT WHEN c_rec%NOTFOUND;
fetch c_rec bulk collect into v_tbl_emp LIMIT 50;

for i in 1..v_tbl_emp.count loop
dbms_output.put_line(v_tbl_emp(i).ename || ',' || v_tbl_emp(i).hiredate);
end loop;
end loop;

CLOSE c_rec;
END p_displayEmpName;
end emp_pkg;
/

db<>fiddle here

Best way to bulk insert data into Oracle database

Load the records in a stage table via SQL*Loader. Then use bulk operations:

  • INSERT INTO SELECT (for example "Bulk Insert into Oracle database")
  • mass UPDATE ("Oracle - Update statement with inner join")
  • or a single MERGE statement

Oracle SQL insert large data set efficiently via cursor use and single commit

I have found the following will do 130 million inserts in about 49 minutes.

INSERT INTO tracker t  
SELECT * FROM tableOne to
WHERE NOT EXISTS (SELECT * FROM tableTwo tt
WHERE TO.FOO = TT.FOO
AND TO.BAR = TT.BAR);


Related Topics



Leave a reply



Submit