I got a following query from Suvankar Das.
"I need to unload few Oracle tables with average data volume of 15M records to flat files. Is the SQL* Plus spool a good option for this volume of data from performance point of view OR is it better to do through PL/SQL UTL_FILE option."
And this is how I responded:
"SQL* Plus will have the fastest result to the flat file. Only use UTL_FILE if you need some more programmatic control in between the spooling.
To make sure first take a sample set of data (some thousands of records) for spooling using SQL *Plus and do the same using UTL_FILE. Compare both the timings.
Also based on the sample estimate the time for the entire 15M records."
The Verdict:
There is no hard rule that says either SQL *Plus or UTL_FILE will perform faster. It varies from environment to environment.
Where SQL *Plus/UTL_FILE could perform faster
SQL *Plus is a client program which is accessing Oracle database. Therefore it highly depends on the network. If the network traffic to the server is slow, then SQL *Plus will perform slower. No doubt. If the server and client are in same machine expect SQL *Plus to perform faster.
UTL_FILE is a server-side program residing within the Oracle database server. Use this procedure if you need to spool something in the server. This will perform faster in such cases. In client machine it always depends on the network load. Whether you are using SQL *Plus or UTL_FILE the network load is always a factor to check with.
