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
Sql: Using Dateadd with Bigints
SQL Same Unit Between Two Tables Needs Order Numbers in 1 Cell
Oracle SQL: Fill in Missing Dates
Update Int Column in Table with Unique Incrementing Values
Order by with Inner Query, Giving Ora-00907 Missing Right Parenthesis
How to Return Two Columns with Function
How Can This SQL Be Wrong? What am I Not Seeing
When Should I Use Stored Procedures
In SQL Server, When Should You Use Go and When Should You Use Semi-Colon ;
Execute Sp_Executesql for Select...Into #Table But Can't Select Out Temp Table Data
Select Distinct Is Slower Than Expected on My Table in Postgresql