4

In our data feeds, we have a stack of XML files as well as numerous flat files to put into an Oracle11g database. We flatten the XML into delimited files, and load the whole set using SQLLoader.

I want to try a proof-of-concept loading via TABLE ORGANIZED EXTERNALLY but I need to make a convincing case to DBA's that it won't do something evil to the server. The only plausible test files I have are 400-600 MB, in production we'd add some multi-gigabyte files.

What are the risks and how should I approach them, any ideas?

UPDATE: Thanks for all of the helpful comments, folks. More discussions with have yielded "we can't give you shell access on the database server to load files, we can't mount remote files via NFS" - these are basically security concerns. We handle PII so DBA's are touchy. Also, some concerns about who's providing the storage.

Any further suggested 'slam dunk' arguments on why 1) external tables are so much better than sqlloader or 2) why a testbed for external tables is low-risk?

Thanks again,

Andrew Wolfe

Improve this question
6
  • What is wrong with the current process that makes you think using external tables will be in some way better?
    – mustaccio
    Commented Jul 3, 2013 at 18:21
  • There are various inconsistencies in incoming data files that are currently difficult to detect, report, and correct. I hope external tables will be better so I want to set up a testbed.
    – Andrew Wolfe
    Commented Jul 3, 2013 at 21:04
  • 1
    Doesn't sound too convincing (I'm speaking from the point of view of a DBA here). How would external tables help resolve data quality issues residing outside the database? Since you can't modify external tables, you will still end up with regular staging tables for data cleansing and correction, so maintaining external tables just becomes an extra effort for DBAs, I'm afraid. I don't believe there are any security risks (if I understand your do something evil to the server the way it was meant), just more work plus extra storage utilization on the server.
    – mustaccio
    Commented Jul 3, 2013 at 21:26
  • another reason why i wont want to give my developers access to external tables is that every time you query the table the sqlldr script is running and loading the server (as you said , 5G of IO). this can be solved by using an MV.
    – haki
    Commented Jul 4, 2013 at 6:23
  • 1
    @haki Do you want your i/o from files outside the database of from tables inside? If you get the loading design right with external tables, you read them once and perform all of your loading in as few queries as possible -- with SQL*Loader you have to use an external scheduling tool, load into a table with minimum flexibility on data transformations, and then query the data again after it's loaded. External tables are superior in almost every respect.
    – David Aldridge
    Commented Jul 8, 2013 at 23:42

2 Answers 2

Reset to default
7

IMO, an external table is much easier to manage and flexible than a SQLLoader script. So if you're already doing a recurring SQLLoader load, I see no possibilities of evil in switching to external tables.

By doing the external table, even though the base of the table resides in a text file outside the database, the data can be accessed from within the database, no need for a separate SQLLoader tool/scripts. So you can write procedures/packages to manipulate the data straight from the external table. No need to load it anywhere...as long as it's in the directory you have set, it's already "loaded".

As a DBA, I'd much prefer managing a DML script using an external table than having to manage a SQLLoader script. But SQLLoader has been around a while....if your DBA has too, it may be a tough sell. :)

Improve this answer
5

If your DBA's won't allow you to even explore the benefits in a proof of concept, time to move companies.

External tables go back to version 10.1 at least, so they're not cutting edge technology. They're so widely (see Tom Kyte et al) held to be superior for many reasons (more flexible data transformations on import, MERGEing into other tables, multitable inserts, better parallelism) that making this case ought to be trivial.

Unfortunately there's a breed of DBA who sees it as their job to say "no", and maybe you're stuck with them.

Bring on the downvotes, DBA's ....

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.