External tables were introduced in Oracle9i, and I would say that it has become a feature that all data warehouse...
architects should consider using. External tables allow you to define a table structure within the database, the database then links this definition to a file that resides on the operating system. You can then read this file just as you would any Oracle table. The basic implementation is done using SQL*Loader as the conduit to the file, in Oracle10g DataPump support has been added. This now allow for reading and writing of file.
Now as for why you should use them. The first consideration here is that I can limit the data that is loaded into the warehouse. We often are required to load data into a landing area. This is usually done through a bulk loader type program. The data is then manipulated in the database and moved into a staging area or the basic warehouse. Now with external tables there is no longer a need to load into a landing area. You can read data directly from this file via SQL using functions as well as conditional statements. This gives you all of the capabilities that you will need to load the data most efficiently. We have found using external tables for this purpose has reduced our load times significantly.
As for the downside of external tables. That's a tough one. I really like these objects and use them often. I guess one downside is that the existence of the file allows you to read it. If the file is not correct, you may not know that the data is out of date. So you need to manage the files more closely when using external tables.
Good luck, external tables can really give your warehouse a great boost.
Dig Deeper on Oracle data warehousing
Related Q&A from Ian Abramson
ETL tools provide very robust support for complex business logic. Continue Reading
What are the pros/cons of custom development vs. an ETL tool? The question of custom code versus the use an ETL tool is one that we are faced with ... Continue Reading
I am evaluating ETL tools for the organization. Considering that I am looking at three or four tools, how do I rate these and put a weight-age for ... Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.