Home » RDBMS Server » Performance Tuning » insert query (oracle 11g)
insert query [message #664521] Thu, 20 July 2017 15:17 Go to next message
POGAKU_SANTHOSH
Messages: 39
Registered: April 2017
Member
hi team ,

I have about 9 million records in a table and in need to insert those 9 million records into anther table but its taking too long for the insertion of the records in the table in oracle . Can any one suggest how to insert the records into the table faster .
Re: insert query [message #664522 is a reply to message #664521] Thu, 20 July 2017 15:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
POGAKU_SANTHOSH wrote on Thu, 20 July 2017 13:17
hi team ,

I have about 9 million records in a table and in need to insert those 9 million records into anther table but its taking too long for the insertion of the records in the table in oracle . Can any one suggest how to insert the records into the table faster .
since you decide to NOT show us what exactly you did do, we can't know what might be faster.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read


Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: insert query [message #664523 is a reply to message #664522] Thu, 20 July 2017 15:44 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
does the table need to be available during the load. How many indexes do you have? How about giving a little more information on your tables, indexes, and data to be inserted. Is the table partitioned? What is the oracle database version?

Any actual information would be nice.
Re: insert query [message #664533 is a reply to message #664523] Fri, 21 July 2017 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How many triggers?
How do you load it?

And what about feedback in your previous topics?

[Updated on: Fri, 21 July 2017 01:16]

Report message to a moderator

Re: insert query [message #664538 is a reply to message #664521] Fri, 21 July 2017 01:41 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
If you use direct load and nologging you can minimize the generation of undo and redo, which may help.

By the way, I wish you would not say "record" when you mean "row".
Previous Topic: insert time is drastically reduced
Next Topic: Performance issue to Update data
Goto Forum:
  


Current Time: Thu Mar 28 04:29:28 CDT 2024