 |
|
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:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
