Types of Tables in Oracle
June 28, 2006
If a co-worker were to ask you to name as many Oracle table types as you can, what would be in your list? More than likely you would start off with relational, object, "normal," cluster, index-organized, nested, partitioned, and so on. "No, not those table types," your friend says, "I mean these types: stage, history, archive, operational, fact, dimension, and so on." The correct answer in this scenario depends on the context or frame or reference being used. In designing a database, it is easy to focus on the structure of tables and to lose sight of how tables are going to be used. Put another way, what is the purpose or function of a table?
Being able to classify a table helps in two common situations: at design time when designing a schema/database, and in cases where you inherit someone else's work, that is, when you need to understand what is already in place. You may already have an intuitive understanding of how tables are used, but the ability to specifically identify a table's purpose or function is useful in that it allows you to communicate requirements to others, whether they are database administrators or database developers. Let's start off by considering a data workflow consisting of in, out, and record.
For many applications with a database back end, stage tables are the first point of entry (or first line of defense, depending on your perspective) for data entering a database. In their simplest form, stage tables are images of the contents of a data file. In terms of purpose, stage tables serve as intake, upload, or collector mechanisms. A very common method of populating stage tables is by using SQL*Loader. Stage table loading can also be used to perform data hygiene. Create a unique index on a particular column, and the upload process takes care of removing duplicates. Other bad or discarded line items can be collected and reported back to the data provider. Depending on your needs, you can also use stage tables to begin or perform transformation processes.
From a use perspective, you will probably want to reuse stage tables. The application or database - it doesnt matter which one, just as long as it gets done - should perform a clean-up or preparation step prior to uploading data. Is it done by using REPLACE in SQL*Loader or by a prepared statement in Java which uses TRUNCATE TABLE? From a design perspective, does the table need to have logging enabled? If so, why? Are you ever going to rollback or recover anything in a stage table? Probably not, so keep the DDL type of features or options to a minimum. Now that the data is inside that database, where does it go next?
One way to define tables of this type is to consider what they contain. Live data, such as customer or account information, can be stored in operational tables. These tables are the guts of the database, so to speak. In one form or another, everything in an application revolves around the contents of these tables. Operational tables should be first and foremost with respect to security, backup, and recovery, and therefore, should employ whatever means or features to meet these requirements. This includes logging, enabled row movement for flashback, explicit permissions, auditing, fine grained access, virtual private database, and whatever else your situation/application may require.
These tables are further characterized by how they are designed. Normalization is a word or process that should immediately come to mind, and along with this comes the consideration of related objects such as indexes, views, sequences, and triggers, to name a few. These tables are what I referred to as "normal" tables in the introduction and normal seems to be a good fit for two reasons. First is that these tables are normally seen throughout a schema, and second is that these tables typically need to be normalized.
Another type of operational table is one related to how an application or schema operates. An example would be storing information about disposition tables (see below). Another example is familiar for those who design security (roles and permissions) into a schema as opposed to having Oracle manage this function.
Lookup or intersection tables fall into this category as their main purpose is to perform a function (i.e., resolve a many-to-many relationship). Another type of functional table is a shopping cart/order entry table (contains an order number and one or more line items). These tables are relatively easy to identify and their purpose is discussed in virtually all database design books. Functional tables bear mentioning because of their close association with operational tables; however, not all tables are created equal.
Identify the crucial functional tables and place them into the same category with respect to the importance placed on operational tables. How can you distinguish between them? One way is to consider how the contents change, that is, is the data static or not? Static tables (e.g., job code versus job description) can be repopulated from a script. Dynamic tables require recovery. Do you directly populate these tables or is it done indirectly (via a trigger)?
Tables of this type are frequently created by performing a join between a stage table and a combination of one or more operational and functional tables. Disposition tables are commonly used for reporting or staging data to be spooled out into disposition files. You may find many tables of this type in a schema, all with the same definition. What differentiates them is the date they were created or the number or records (weekly report or newsletter recipient list consisting of fewer rows as compared to an end of month report or recipient list).
A disposition table can also be similar to a stage table in that its contents are routinely flushed and repopulated for a pending disposition or report process. An alternative means of keeping disposition data is to borrow from the data warehouse side of things and use a flight history type of table. You will not need to retain 100% of the disposition table's contents, but you will need to capture what is essential. Who sent what, and when, and can details be reconstructed if necessary? If details are lost due to data changes elsewhere in the database, then the record keeping requirements become more complicated. It then becomes a trade off between keeping scores of disposition tables and maintaining a huge flight history type of table.
A close cousin on the production side to a warehouse's flight history table is the archive table. By its nature, an archive table's content is permanent, so one way to delineate the life span of what is in a disposition table is to consider the degree or permanency of the data. Forever (or, at least whatever the service level agreement retention policy is) equals archive, and something more short term is subject to being disposed of in a disposition table.
Audit tables are discussed in detail throughout much of Oracle's documentation and generally so in most textbooks. It is not the data, but who did what to the data and when. One type of table being seen more in applications is the metadata table (data about data). Not sure of what an example of this type of table looks like? Several tables in Oracle's data dictionary are excellent examples. The DBA/ALL/USER_TABLE family contains data about a table's data (number of rows, last analyzed, etc.).
Production database tables, for the most part, seem to lack the same naming clarity as seen in data warehouses. Fact and dimension tables have clearly defined names because of their purpose, but what type of table is your customer account table? Whether or not you agree that the type name should be operational, what does matter is that everyone involved in the management of this type of table have the same understanding as to its purpose. If someone asks you to name table types in Oracle (and this applies to other database systems), ask that person if he or she meant physical or logical, how they are designed (DDL) or how they are used, production or warehouse, and so on. You can help expand that person's frame of reference by going beyond what is in the Database Concepts documentation.