| Populating Text File with records from a table [message #200050] |
Fri, 27 October 2006 12:15  |
desigan
Messages: 8
Registered: October 2006
|
Junior Member |
|
|
Hi,
I have a table with 25 million rows. I need to sort these rows of the table and then copy all the records into a text file.
Following are the ways that I tried out.
1) I wrote an unix ".ksh" file, which will execute the select query and move the select query contents to a file.
It is like the following:
Select * from table > TextFile.txt
It takes 50 mintues to copy the entire data to this "TextFile.txt".
2) This is another idea, which I want to try out. Instead of writing the entire contents to a file, I plan to do the following:
a) Create 5 Threads (say in java)
b) Each thread will operate on 5 million rows and write them to a separate text file. All these threads will run in parallel.
c) At the end of all the threads, we will have 5 files, each with 5 million rows.
d) Concat these 5 files together.
I feel this should work faster than the earlier one.
Apart from the above two ways, Is there any other way by which I can achieve this (which will be faster too)? This is a daily job and hence I am looking out for performance here.
Regards,
Desigan
|
|
|
|
|
|
|
|
| Re: Populating Text File with records from a table [message #200367 is a reply to message #200207] |
Mon, 30 October 2006 09:30  |
desigan
Messages: 8
Registered: October 2006
|
Junior Member |
|
|
Mahesh,
Thanks a lot for your replies. I went through the URL you specified.
I feel the following two are meeting my requirement needs:
1) Using PL SQL utl_file to write the data
2) There is a may mentioned by using SQL Plus. It is followed by some query as follows:
$ sqlldr_exp scott/tiger dept
LOAD DATA
INFILE *
INTO TABLE dept
.........
$ sqlldr_exp scott/tiger dept > dept.ctl
Could you please explain how to run this? I tried running these lines in a SQLPLUS window but it gave me error as "unknown command".
[Sorry that I am new to Oracle and hence not able to follow your hint.]
Regards,
Desigan
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
