Skip to Main Content
  • Questions

  • Which is faster - Database or a programming language like C++

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Pawan.

Asked: October 19, 2002 - 8:48 pm UTC

Last updated: May 01, 2007 - 11:39 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

I recently interviewed at a very large organization for a Data Warehouse Developer position. One of the questions I was asked was what would be the best way to load huge amounts of data (600 million records) from a flat file to a table or from one or more tables into a target table. I said depending on the complexity and other issues I will use SQL*loader or Direct path insert etc. But the interviewer was of the opinion that writing code in C++ was much more efficient than doing it in the database. I have had prior experience of loading very complex data and always used the database to do it and never was performance an issue. I just wanted to know your opinion in this regard.
Thanks

and Tom said...

Wow -- this is like the tenth interview question this WEEKEND! Whats up with that (not all from you of course -- just got lots of them)


The interviewer was, IMHO, smoking something. Totally.


I would love to see their code beat the direct path loader -- I seriously doubt they were using the direct path OCI API's.

Even if theirs tweaked out the direct path loader, I would still say "not the right way, I personally do not want to maintain a ton of custom C++ (especially c++) code" -- but, it wouldn't.

My answer would have been:

you want to be in 9i using external tables so you can do a direct path load using CREATE TABLE AS SELECT or INSERT /*+ APPEND */ in parallel (letting the database determine the DOP even) without generating the gobs of undo and redo you are.

Short of that, if you force me to use 817 even today -- I would use sqlldr and if the data needed to be "tweaked" a bit before going in -- a simple

cat inputs | sed | awk > named_piped &
sqllldr data = named_pipe

would be preferable to tons of custom code.




Rating

  (3 ratings)

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

Comments

Thanks

Pawan Putra, October 20, 2002 - 9:03 pm UTC

I talked about the External table feature and the interviwer said - " Not until 2004 are we moving to 9i". Anyway I came to know from the recruiter that I might be offerred the position but I am seriously thinking if I should take it and then have arguments everyday.
You will be happy to know that another person who interviewed me at the same place asked questions ( about 15) all chapter wise starting with Architecture to Invoker and definer rights from your book.

One Suggestion - Is it possible to compile all the interview type questions and place it under a separate subject?
Thanks again.

Tom Kyte
October 20, 2002 - 9:20 pm UTC

2004, great, 10i release 2 will be out by then.


anyway - since I didn't have a "faq" for interviews (and I've been really truly lazy about using that feature here), no -- beyond searching for "interview" here, no not really. sorry.

Regarding Your Reluctance to Create an Interview FAQ.

Richard, October 21, 2002 - 10:31 am UTC

Oh, go on, Tom! Imagine the good will engendered by setting up that FAQ... it could lead to an international hands-across-the-water love-in!

Oracle 10G or application language

Piysuh, May 01, 2007 - 11:29 am UTC

Hi Tom,

I really liked the reply. Can I know what would have been your answer if the database was 10G.

Moreover, please reply in context to both Oracle on Unix and Windows.

Regards,
Piyush
Tom Kyte
May 01, 2007 - 11:39 am UTC

same answer on 10g, and it works the same on unix as on windows. that is the beauty of doing things IN the database (external tables), you achieve the ultimate in operating system portability.
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

Hints

Need more information on hints? Check out the complete hint descriptions