Skip to Main Content
  • Questions
  • Extracting 500 Million Rows from Oracle DB -- Pro*C OR PL/SQL UTL_FILE? Deep Dive into WHY?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Suman.

Asked: March 26, 2018 - 6:20 am UTC

Last updated: March 27, 2018 - 7:02 am UTC

Version: 10.1.2

Viewed 1000+ times

You Asked

Hi Tom,

The question is simple. I need 500 Mil rows to offload from oracle, what should I choose to to develop an application with ? PRO*C or PL/SQL UTL_FILE? and why ?


I have gone through the debated topics on which approach between PRO*C or PL/SQL is faster, but I need a detailed explanation to the solution for the specifically mentioned case.

Speaking of detail, It would be very much appreciated if following points are taken into consideration.

1. Memory Management -- should data be chunked with a size limitation for multiple batches?
2. Processing speed -- which option between proc and pl/sql if there are minimal/heavy processing involved.
3. Network / IO overhead -- which approach tops the other?
4. Complexity of programming -- Debugging/Maintainability

It would be very helpful if the answer is elaborated on the underlying architecture level to accomplish the "why" part in the question.


Thanks.

and Connor said...

1. Memory Management -- should data be chunked with a size limitation for multiple batches?

Definitely. 500 million rows *might* fit in memory, it might not. In *any* solution, you read in batches.

3. Network / IO overhead -- which approach tops the other?


Neither if you run both on the database server.

4. Complexity of programming -- Debugging/Maintainability


PL/SQL is easier to code and maintain, but see (2) below

2. Processing speed -- which option between proc and pl/sql if there are minimal/heavy processing involved.


PL/SQL is fundamentally about data-processing not file processing. So whilst PLSQL and Pro*c (on the db server) will both run pretty much the same speed at *retrieving* your data, you are likely to have more options for performance tuning the file *writing* aspect. You can get an example of a Pro*C routine to unload data here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=yet-another-ascii-dump-issue


For a one-off exercise, I'd use PL/SQL (or even SQL Plus) because by the time I've written the Pro*c the unload will be done already! For repeated usage, I'd look at PL/SQL and then go to Pro*C if its too slow.



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database