Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
What is wrong with the current process that makes you think using external tables will be in some way better? –  mustaccio Jul 3 '13 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 Jul 3 '13 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 Jul 3 '13 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 Jul 4 '13 at 6:23
    
I'm talking a PRODUCTION data feed that must be loaded periodically with data that is changing over time (database marketing). The data quality issues recur in unpredictable ways and we must report them to the originators. Right now we are using SQLloader exclusively on a daily, weekly, or other interval as SLA requires. –  Andrew Wolfe Jul 4 '13 at 22:47
show 1 more comment

2 Answers

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. :)

share|improve this answer
add comment

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 ....

share|improve this answer
    
I'm not a DBA so I feel justified in up-voting :) –  Paul White Jul 8 '13 at 23:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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