Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Oracle table design options

Oracle Tips by Burleson 

Chapter 6 Oracle Table Design

The data storage structures within Oracle are plentiful and range from very simple to extremely complex.  During your physical design you have a choice between standard relational tables and one of the many table extensions offered within the Oracle software.  This chapter will address the following table design issues:

  • Design with Replicated tables

  • Design with External tables

  • Design with Materialized Views

  • Design with Oracle Object Tables

  • Design with Abstract data types

  • Design with Oracle object IDs

  • Design with Pointer-based tables

  • Design with Nested tables

  • Design with Oracle member methods

Let’s begin with a review of the Oracle replicated table structure and see how it can be incorporated into a physical design.

Table Replication Design

Determining which type of replication your situation requires is very important.  Remember these two adages:

•               More is NOT always better!

•               Just because Oracle includes it in the database, you don’t have to use it!   

One if the biggest mistakes a company can make is to implement advanced replication when all they need is read-only materialized views.  With replication, “more” is harder to implement, harder to maintain, harder to troubleshoot, takes more of your time. 

Here is a criterion that you can use to determine the level of replication that best fits your situation.

1.  Is the transfer of data time sensitive? 

Many DBAs believe that data is time sensitive when in fact it is not.  If the data is being moved to a data warehouse to be used for data mining, or report generation, the data probably is not time sensitive.  A daily transfer (or even weekly) may meet the entire business requirement.  Ask management; a daily report in the morning may be acceptable, instead of a report available on demand with the most recent data.  Many DBAs are finding that even internal materialized views are taking so long to update that they have to update them at night or only on weekends.

2.  Is the number of tables manageable?

If you are using Oracle Applications, forget about replicating the entire database.  Oracle Apps consist of tens of thousands of tables.  This is not a candidate for replication.  However, replicating parts of large databases are possible.  Remember that each replicated object adds overhead to the databases and takes network bandwidth.  There are practical limits to the number of objects you can replicate, depending on the capability of the database server and the network connections.  Replicating 100 tables is easy, a thousand may not be possible, ten thousand - forget it. 

3.  Do all your replicated tables need to be updatable?

A shop will often set up full multi-master replication because the database is supporting an application that has to update certain tables.  Tables that need to be updated at both locations must use advanced replication, however all remaining tables can use basic replication.  This ability to mix replication types can significantly lower the replication overhead.  Remember, less is best.

4.  Does your database change constantly?

Does QA roll in a new update every weekend?  If so, replication may not be for you.  Table changes may force you either to rebuild the replication or implement advanced replication.  Maintaining replication in a changing database will entail a significant increase in the DBA’s workload. 

5.  Is the number of transactions manageable?

The number of transactions per minute is another one of those variables that must be considered.  A replication based on a few tables will be better able to handle high numbers of transactions.  A large replication may not be able to keep up on a high transaction system, this again depends on the server capabilities and the network bandwidth.

6.  Are you replicating between different versions of Oracle or different operating systems?

Many shops choose replication rather than a standby database precisely because replication can operate between either different versions of the Oracle database, or between Oracle databases running on different operating systems.  Because replication is passed across database links, different versions of Oracle can be used.  An Oracle database on Windows can be replicated in a database on a Sun Server, thereby providing a failover solution if needed.

7.  Do both sites require the ability to update the same tables?

If both sides of the replication must update data (insert, update, delete), then you must implement advanced replication.  Use advanced replication only on the tables that must be updated on both sides of the replication.

8.  Does the replicated site require the ability to replicate to another site?

A master site can replicate with other sites.  If the remote site only replicates with one master site, use updatable materialized views.  If the remote site must replicate the data further, then it too must be a master site, and multi-master replication is required.

As you might have figured, replication is difficult to understand and time-consuming to setup.  But its daunting reputation is much worse than reality.  Once you get it set up and operating, you will find it really isn’t very intimidating.  Remember to replicate at the lowest level possible.  Don’t use advanced replication where basic replication will work.  Don’t try to replicate more objects than your server and network are able to support.

Now let’s look at Oracle9i external tables and see how they can incorporate into your logical data model.

Oracle External Tables

One of the most exciting advances in Oracle9i is the ability to access non-Oracle files with Oracle SQL. This new functionality, called external tables, has important ramifications for systems where external files need to be available for non-database applications and appear to be a table within Oracle.

External tables allow you to define the structure of almost any flat file on your server and have it appear to Oracle as if it were a real table, as we’ve diagrammed (Figure 6.1).


Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2016 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks