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

This was last published in May 2005

Dig Deeper on Oracle data warehousing

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close