Home » RDBMS Server » Server Administration » Inserting data into a table - Slow Performance
Inserting data into a table - Slow Performance [message #192183] Mon, 11 September 2006 06:53 Go to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

I'm inserting around 5,000,000 records into a table using

insert into table1
select * from table2

It takes around 2hrs to execute the above statement. Is there a better approach for the same or do i need to relook server configuration.

Brayan.
Re: Inserting data into a table - Slow Performance [message #192196 is a reply to message #192183] Mon, 11 September 2006 07:26 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Please consider trying parallel DML for the purpose.

have a look:
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96524/c20paral.htm#27551

Liza
Re: Inserting data into a table - Slow Performance [message #192206 is a reply to message #192183] Mon, 11 September 2006 07:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
and, APPPEND hint
search the forum for more details
Re: Inserting data into a table - Slow Performance [message #192268 is a reply to message #192183] Mon, 11 September 2006 11:37 Go to previous message
goudelly
Messages: 52
Registered: August 2006
Location: India
Member

Hi,

You can some other method but it is faster.

you have to craete the 'c:\utl_dir' in os level

in oracle :

log in sysdba user

create directory utl_dat as 'utl_dir';
grant read,write on directory utl_dat to scott;

conn scott/tiger;

For making the text file of your table :

declare
cursor c1 is select * from emp;
v_file utl_file.file_type;
begin
v_file := utl_file.fopen('c:\utl_dar',emp.txt','w');
for r1 in c1 loop
utl_file.put_line(v_file,r1.empid||'|'||r1.ename||'|'||r1.sal||
end loop;
utl_file.fclose(v_file);
end;


It will create the text file in your c:\utl_dit\emp.txt .


craete table emp1 as select * from emp where 1=2;

it will create the emp2 table structure of emp as same.

Now you can upload the data from emp.txt file to emp1 table.


emp.ctl

option(direct=true)
load data
infile='c:\utl_dit\emp.txt'
badfile='c:\utl_dir\emp.bad'
discardfile='c:\utl_dir\emp.dsc'
into table emp1
fileds terminated by "|"
trailing nullcols
(empid,ename,sal)

from DOS prompt :

c:\sqllldr userid=scott/tiger control=emp.ctl.


It will insert all the rows from emp.txt into emp1 table. You can insert any no of rows.

Thanks,

Mohan Reddy G


Previous Topic: what isdifferent between .ora and .dbf in datafiles.
Next Topic: total server
Goto Forum:
  


Current Time: Tue Dec 06 02:48:09 CST 2016

Total time taken to generate the page: 0.07741 seconds