Today I was answering one of the queries, this question seriously made my interest. The question was
"I have 15 million records in my table. What do you suggest a best method for unloading them from Oracle table to a flat file".
Seriously till that time I had never done this. The need had not come. I clearly know two ways of doing this:
- Using SQL Plus and SPOOL command
- Using UTL_FILE built-in package
UTL_FILE vs SPOOL
I know that SPOOL command will be faster in execution than UTL_FILE package, but also I reminded myself that this may not be the case always. SPOOL commands operates in the client machine and depending upon the network traffic it might take time for the SPOOL operation than UTL_FILE.
SO I ADVICE TO TEST BOTH OPTIONS IN YOUR ENVIRONMENT BEFORE FINALISING ON ONE OF THE ABOVE.
I will soon publish routines for this purpose. Your patience is appreciated.
More resources for your inquisitive mind
A little surfing did a wonderful job in putting this article up. In asktom I found a beautiful document that should be the starting page for such exploration. Here is the link for you. I am not going to discuss how tom reviews this anymore. Check the link for yourself.
If you are not having Oracle client in your machine and still you need to unload the data to flat file, follow this link and introduce yourself to a tool OraCmd. I have not personally used this tool, but from the description in the website it promises to be one. I will test the performance issues with this as well in the near future. Here is the link for unloading using OraCmd.
Fake.
ReplyDeleteWrite better PL/SQL code. Try to unload ~15 million rows from BIG_EMP table into CSV file.
On my note (P-4 1,7 GHz, 1 Gb RAM, Win2003 server and Oracle 10.2.0.4) it takes only ~4 minutes with UTL_FILE.
Don't tell me about using SPOOL for this procedute.
Lamers. Go to school.
It's obvious why you are Anonymous.
ReplyDeleteYou are a jerk.
The person writing this answer was giving helpful advice, and doesn't deserve such rudeness.
Thanks Patricia for your constructive comments. We cannot censor the whole world. Everyone has their own methods to solve the problems in hand.
ReplyDeleteAll solutions are not always perfect solutions, because they are just perfect for the situation in hand.
there is very fast tool called fastreader for purpose of unloading into flat files from oracle. The key thing to do direct unload which gives the extract performance similar to input/output disk-storage speed. http://www.wisdomforce.com/dweb/resources/docs/fastreader_data_sheet.pdf
ReplyDeleteWe use wisdomforce expensively for both fast unload and migration into other environments