How to load the data from flat file? -- urgent please [message #437252] |
Sat, 02 January 2010 13:20 |
arunn
Messages: 52 Registered: September 2008 Location: usa
|
Member |
|
|
Friends,
I have 1 million data in the flat files. i want the technique to load the data in oracle database tables....
which one is best in the below?
a. bulk collect
b. external table
c. sql loader
d. split the data using unix scripting.
e. split the data using pl/sql( when i use the commit? like for every 25000 thousand rows )
or else other technique?
thanks in advance friends...
|
|
|
|
|
|
|
|
Re: How to load the data from flat file? -- urgent please [message #437269 is a reply to message #437260] |
Sat, 02 January 2010 18:17 |
arunn
Messages: 52 Registered: September 2008 Location: usa
|
Member |
|
|
Michel Cadot wrote on Sat, 02 January 2010 13:56External table is far faster than PL/SQL including bulk one.
Besides how do you want to use bulk collect?
Regards
Michel
thanks mr. michel cadot, using the bulk collect load the data from external table to normal table.
|
|
|
|
|
Re: How to load the data from flat file? -- urgent please [message #437272 is a reply to message #437252] |
Sat, 02 January 2010 19:08 |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
arunn wrote on Sat, 02 January 2010 20:20I have 1 million data in the flat files. A megabyte of data? million of files? million of directories with billions of huge files in every of them?
arunn wrote on Sat, 02 January 2010 20:20which one is best in the below? Like Michel wrote - define "best". If there was simply "the best" then there would be only a single method available because there is no point in keeping "not the best".
arunn wrote on Sat, 02 January 2010 20:20 a. bulk collect
b. external table
c. sql loader
d. split the data using unix scripting.
e. split the data using pl/sql( when i use the commit? like for every 25000 thousand rows )
sqlloader is above-average. In direct mode it is very fast. You might think that some of the options are exactly the same as sqlloader (because this is the same mechanism internally).
If you want to use unix scripts to split the data then it is a good solution for common repeated task (which is repeated often). But there is a price - this is not really that fast.
pl/sql is programming language. You usually use it to do something on the server because there is no need to download whole database to your application. This is why it was created and why it is called programming language sql.
As for the commit - it is just the finalization of your transaction. Best is to use rarely - but it might be risky and you might run out of resources in which case it might be tragic.arunn wrote on Sat, 02 January 2010 20:20or else other technique? There are many ways. No one can help you without proper information.
arunn wrote on Sat, 02 January 2010 20:20-- urgent please I hope that "--" was just a pl/sql comment... if not then you should start with reading the posting rules. Usually I simply ignore posts with "urgent" (or similar) word in their abstract.
|
|
|
Re: How to load the data from flat file? -- urgent please [message #437280 is a reply to message #437269] |
Sun, 03 January 2010 01:34 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:thanks mr. michel cadot, using the bulk collect load the data from external table to normal table.
insert select is surely faster.
Quote:is there any other technique to load the data from flat file to db?
Sure, here's one: use sed or awk to transform each line of your file to an insert statement and then use sqlplus to execute it, now a SQL script.
Is this fast, surely not, the fastest way has already been given.
Regards
Michel
|
|
|