Problem solve Get help with specific problems with your technologies, process and projects.

External tables explained

I haven't used external tables before as a data source for loading data into a data warehouse. What are some of the pros and cons I should consider and what are some of the general "rules of thumb" that are important to deciding whether or not I should or should not use external tables? For example, is an external table better for tables over or under a given size?

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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.