Home » SQL & PL/SQL » SQL & PL/SQL » How to fast insert table
How to fast insert table [message #401481] Tue, 05 May 2009 03:22 Go to next message
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 #401482 is a reply to message #401481] Tue, 05 May 2009 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Where does your data come from? file? application? user inputs? other tables?...

Also always post your Oracle version (4 decimals).

Regards
Michel
Re: How to fast insert table [message #401498 is a reply to message #401482] Tue, 05 May 2009 04:38 Go to previous messageGo to next message
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 #401505 is a reply to message #401481] Tue, 05 May 2009 05:38 Go to previous messageGo to next message
Theracersman
Messages: 28
Registered: November 2007
Location: Indonesia
Junior Member

data come from select table,with index.. how to faster insert into table ?
Re: How to fast insert table [message #401508 is a reply to message #401505] Tue, 05 May 2009 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If possible, remove index on target table.
Activate direct mode with APPEND hint.
If hardware and workload allow it, activate parallelism with PARALLEL hint.

Regards
Michel
Re: How to fast insert table [message #401640 is a reply to message #401481] Tue, 05 May 2009 21:33 Go to previous messageGo to next message
Theracersman
Messages: 28
Registered: November 2007
Location: Indonesia
Junior Member

i can't remove index,cause data is unique.
i hear,with bulk can insert faster..
Re: How to fast insert table [message #401644 is a reply to message #401481] Tue, 05 May 2009 22:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET FAST_INSERT=TRUE
Re: How to fast insert table [message #401648 is a reply to message #401644] Tue, 05 May 2009 23:08 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #401653 is a reply to message #401481] Tue, 05 May 2009 23:54 Go to previous messageGo to next message
tejas.patel
Messages: 22
Registered: December 2008
Location: NJ
Junior Member

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.
Re: How to fast insert table [message #401654 is a reply to message #401653] Tue, 05 May 2009 23:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
tejas.patel wrote on Tue, 05 May 2009 21: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.

HUH? I do not understand.
Re: How to fast insert table [message #401661 is a reply to message #401653] Wed, 06 May 2009 00:48 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Locking on updation or Deletion (merged)
Next Topic: Employee database programme
Goto Forum:
  


Current Time: Wed Feb 12 05:10:59 CST 2025