| Inserting data into a table - Slow Performance [message #192183] |
Mon, 11 September 2006 06:53  |
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 #192268 is a reply to message #192183] |
Mon, 11 September 2006 11:37  |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
