Skip to Main Content
  • Questions

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

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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.



We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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