How to fast insert table [message #401481] |
Tue, 05 May 2009 03:22  |
Theracersman
Messages: 28 Registered: November 2007 Location: Indonesia
|
Junior Member |

|
|
hi all..
i want to know, how to fast insert into table.
i want insert 2million record 2 minute.help me please..
|
|
|
|
Re: How to fast insert table [message #401498 is a reply to message #401482] |
Tue, 05 May 2009 04:38   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
For that sort of performance, you could try a DIRECT PATH LOAD, however the table you load into would need to have no indexes (or the data would need to be pre-sorted).
If the data is already in another Oracle database, then Oracle Data Pump might be able to do it too. Again, the table would need to be unindexed.
If you have plenty of time to PREPARE the data, but not much time to LOAD it (ie. limited window), you could potentially use PARTITION EXCHANGE LOAD or TRANSPORTABLE TABLESPACE.
Ross Leishman
|
|
|
|
|
|
|
Re: How to fast insert table [message #401648 is a reply to message #401644] |
Tue, 05 May 2009 23:08   |
trivendra
Messages: 211 Registered: October 2007 Location: Phoenix
|
Senior Member |
|
|
BlackSwan wrote on Wed, 06 May 2009 08:35 | ALTER SESSION SET FAST_INSERT=TRUE
|
What is this and how this helps, I am not able to find any thing about this.
Thanks
Trivendra
|
|
|
Re: How to fast insert table [message #401650 is a reply to message #401640] |
Tue, 05 May 2009 23:23   |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Theracersman wrote on Wed, 06 May 2009 04:33 | i can't remove index,cause data is unique.
i hear,with bulk can insert faster..
|
I mentioned 3 possible cumulative and not exclusive ways to fasten insert.
You can't do one, ok, try the other ones.
Remember one SQL statement is always faster than any PL/SQL code (true at 99.99999%, at least).
Regards
Michel
[Updated on: Tue, 05 May 2009 23:23] Report message to a moderator
|
|
|
|
|
Re: How to fast insert table [message #401661 is a reply to message #401653] |
Wed, 06 May 2009 00:48   |
 |
Michel Cadot
Messages: 68734 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
tejas.patel wrote on Wed, 06 May 2009 06:54 | B]HOW TO FAST INSERT TABLE?[[/B]
if you have any data in table that table first taken dump after that dump file import in your database.Using DBA Concepts first check DBA commands.
|
Import is NOT a fast insert, it is a slow insert.
Maybe the slowest one; ok you can find slower like insert row per row in a PL/SQL loop... with a commit at each row.
Regards
Michel
[Updated on: Wed, 06 May 2009 00:49] Report message to a moderator
|
|
|
Re: How to fast insert table [message #401735 is a reply to message #401640] |
Wed, 06 May 2009 05:05  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can remove the index - the insert process won't change the data, and it won't change the uniqueness.
Are you inserting additional data into a pre-existing table, or is the data you want to insert going to be the only data in the table?
If it's the latter, then Ross's suggestion of Transportable Tablespaces is a good one.
Alternatively, you could load the data into another table, and simply do a RENAME TABLE.
If you provide a bit more detail about what you're trying to do, we can provide better solutions.
|
|
|