Home » SQL & PL/SQL » SQL & PL/SQL » IS Paralle Insert possible ??
IS Paralle Insert possible ?? [message #36643] Sat, 08 December 2001 11:18 Go to next message
Shradha
Messages: 8
Registered: December 2001
Junior Member
I am migrating data from one datamodel to another.
Around six million records are taking 2 hours to insert.
not bad but we can't afford these two hours also.

I cannot disable constraints or afford to use the copy command
since we want to handle the errors.

Does anybody know how i could go about doing a parallel
insert which may get this time down.

Thanks in advance.
Shradha

----------------------------------------------------------------------
Re: IS Paralle Insert possible ?? [message #36644 is a reply to message #36643] Sat, 08 December 2001 12:06 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
are you using sqlloader?. if so use direct load option. it is fast.

----------------------------------------------------------------------
Re: IS Paralle Insert possible ?? [message #36676 is a reply to message #36644] Tue, 11 December 2001 17:49 Go to previous messageGo to next message
Shradha
Messages: 8
Registered: December 2001
Junior Member
Thanks Suresh,
No we aren't using sqlloader..

anything else that can be done programmatically ??

----------------------------------------------------------------------
Re: IS Paralle Insert possible ?? [message #36958 is a reply to message #36643] Mon, 07 January 2002 10:34 Go to previous messageGo to next message
chandra
Messages: 31
Registered: October 2000
Member
HI Shardha,
Would you please let me know on how you are inserting 6M rows in around 2 hours.
I have design a process to load 58 Million rows in less than 3 hours.
I am thinking of using direct load.
Thanks in advance
Chandra
Re: IS Paralle Insert possible ?? [message #37336 is a reply to message #36676] Thu, 31 January 2002 16:36 Go to previous message
Ajit Bhingarde
Messages: 1
Registered: January 2002
Junior Member
Hi all,
Yes, parallel insert is possible.
In oracle 7.3 it is as follows.
insert /*+ parallel(table_name1,DOP) */ into table_name1 select /*+ parallel(table_name2,DOP) */ * from table_name2;

in Oracle 8i it is much faster as follows.
insert/*+ parallel(table_name1,DOP) */ into
table_name1
nologging
select /*+ parallel(table_name2,DOP) */ * from table_name2; (this does not generate redo)
or
insert/*+ append */ into
table_name1
nologging
select /*+ parallel(table_name2,DOP) */ * from table_name2; (this does not generate redo)
The only care you have to take is that u should have sufficient space preallocated for insert as this inserts from the existing HWM.

There are some init.ora parameters which should be taken care of for the parallel execution.You can refer documentation for that or mail me if you don't find any info.
I hope this answers your query.
If any doubt contact me on my email.(give reference of oraperf.com on the subject)
Previous Topic: How do I convert the difference between two dates into a date format
Next Topic: Creating an Auto Increment in VB
Goto Forum:
  


Current Time: Tue Apr 07 03:22:30 CDT 2020