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