Oracle Database Tips by Donald Burleson
Over-Normalization of Oracle Entities
Some Oracle databases were modeled according to
the rules of normalization that were intended to eliminate
redundancy. However, a pure third-normal form (3NF) database can
cause high overhead on the Oracle database and
is a big problem, especially in databases that were designed when
disk cost was $250,000 per gigabyte.
I once met Ted Codd at the Database World
Conference where we both were speaking, and I asked him how he
came-up with the word "Normalization". He said that Nixon was
normalizing relations with China at the time, and if Nixon could
normalize relations, then so could he!
Obviously, the rules of Oracle normalization are
required to understand your relationships and functional
dependencies, but BCNF is just a starting point, not a completed
data model. Legacy systems tended to be more highly normalized than
today's databases because of the high cost of disk in the 1980's and
1990's. But disk is cheap today, RAM-SAN is coming, and denormalization is a Godsend.
If we introduce redundancy to
reduce joins, we can retrieve high-volume queries with far less
runtime overhead. In many OLTP
systems there are usually a small set of queries that account for
90% of the overhead, and these queries are the starting point for a
change in Oracle normalization. I've seen databases where a single
query form (show me all items for an order) was 75% of system
Oracle offers several popular denormalization tools, some that
create non first-normal form structures (0NF):
Normalization & Object tables - Oracle
has nested tables and varray table columns whereby repeating
groups are stored within a row, violating 1NF.
Denormalization & Materialized Views -
Tables are pre-joined together, queries are re-written to access
the MV, and a method (Oracle snapshots) keeps the
denormalization in-sync with the normalized representation of
How and where do we introduce redundancy to
remove table joins? The answer depends on the "redundancy
boundary", a function of the size and volatility of the
redundant item. Let's look at a real example where an 6-way table
join was required to display basic information about people.
Is this high-level of normalization required
just to display a person? Optimizing the SQL is time-consuming
and the query would be required to do at least five logical I/O's.
When we de-normalize to introduce redundancy to improve
performance and simplify the data model, we must always remember
that we have to code to go to several tables to update the redundant
data item. This overhead can be huge if we have large data items
that change frequently. In this example system, data was to be
stored with the historical values.
Now, in all fairness, a fully normalized Oracle
design (3NF) design was perfect
in 1986 when disk was expensive. Today, over-normalization of Oracle
databases adds a
huge burden on a high-performance online transaction processing
Complexity to the developers (lots of extra coding for n-way
Run-time overhead (complex SQL pre-processing by the CBO)
Higher disk I/O (many data blocks must be visited to fetch
This example illustrates the huge problems
associated with fixing a "bad" schema and shows how it can be a huge
and expensive undertaking to denormalize a schema in Oracle to reduce
unnecessary table joins. Some Oracle professionals use
Materialized Views to de-normalize 3NF
structures, but this only works in cases where the data changes
This is an excerpt from the book PL/SQL: The Definitive Reference by Boobal Ganesan.
Relational Model and Normal Form
In this section, the inadequacies of the existing non-inferential models and the concept of universal data sublanguage are discussed. The relational view of data provides the means of describing the data with its natural structure only without imposing any additional structure for the machine representation. This model also provides the overall idea of creating a superior data language which results in maximum independence between the programs and the machine representation.
Further advantages of the relational view are that it forms a strong base for consistency, derivability and redundancy of the relations, whereas on the other hand, the graphical model spawned confusions on these terminologies.
Finally, the relational view clearly evaluates the scope and the logical limitations of the present system and also the merits of competing the representations of data within a single system.
Data Dependencies in the Present Systems
The provision of data description tables in the modern system represents the major advancements in the data independently. These tables help in changing the characteristics of data representation preserved in the databases. There are three major kinds of data dependencies which are needed to be removed are ordering dependence, indexing dependence and access path dependence.
There are multiple different ways of storing the elements of data in the databases.
· Some data are not concerned about ordering.
· Some data are concerned with a particular ordering.
· Some data are concerned in several ordering.
The graphical models normally permit application programs to assume that the order of representation of the data from the file is similar to the data stored order. These kind of systems which take advantage of the stored ordering are likely to fail if the ordering is replaced by a different one. These kind of ordering dependency can be avoided by solving significant implementation.
In the context of formatted data, an index is usually thought to be a performance oriented component of a database and it tends to improve performance when an update is performed and at the same time, lose performance when insertions or deletions are performed.
Different data systems take widely different approach towards indexing. Some data systems provide indexing of all attributes and other provides users with a choice of no indexing at all or indexing only on the primary keys. Application programs enjoying the advantage of these indexing chains must refer to them by their names. These programs tend to fail when the chains are later removed.
Access Path Dependence
Many of the data systems provide users with data of tree structured files or slightly more general network models of the data. If these trees or networks changes, the applications developed to work with these systems tend to be logically impaired.
A Relational View of Data
This section urges the users to interact with a relational model of the data consisting of a collection of time varying relationships than relations.
The term relation is used in its accepted mathematical sense. Given sets A1 , A2 , . . . , A (not necessarily distinct), B is a relation on these n sets if it is a set of n-tuples, each of which has its first element from A1, its second element from A2, and so on.' We shall refer to a as the jet domain of B. As defined above, B is said to have degree n. Relations of degree 1 are often called unary, degree 2 binary, degree 3 ternary, and degree n n-ary.
The totality of the data in a database may be considered as a collection of time varying relations. These relations are of varied degrees and as time progresses, each n-nary relations may result in the insertion of new n-tuples, deletion/ modification of any of its existing n-tuples.
A relation whose simple domains can be stored in a two dimensional column arrays and non-simple domains can be stored in a complicated data structure. The procedure for eliminating the non-simple domains is called as normalization.
If the normalization as described above is considered to be applicable, the un-normalized relations must satisfy the below conditions.
1. The graph of interrelationships of the non-simple domains is a collection of time.
2. There should be no primary key with a non-simple domain component.
Some Linguistic Aspects
The acceptance of the relational model permits the creation of a universal sub language based on a relational calculus. Such a language will provide immense power to all other proposed data languages and it will be a strong choice for embedding with a varied host language.
Expressible, Named and Stored Relations
The named set is the total collection of the relations that a data language can identify it by a simple name. Examples of named sets are declarations and identifiers.
The expressible set is a collection of all relations that can be identified by expressions in a community. Examples of expressible sets are < and =.
Redundancy and Consistency
Operations on Relations
This section of the paper describes the manipulative part of the relational data model. The below operations are defined in this section.
If a permutation is applied to the columns of an n-nary relation, the resulting relation is said to be a permutation of the given relation.
If a certain number of columns are selected from a relation and then if the duplicated rows are removed from the resulting array, the final relational array is said to be a projection of the given relation.
The circumstances under which two relations having some domain in common can be combined together to form a relationship to preserve all the information in the given relation is said to be a join.
The two relations are composed only if there exists a join between them. If there are more than one join possible between the given two relations, it does not comply that there is a possibility of more than one composition between them.
The relation A acting upon the relation B to generate a subset of B is through the operation restriction of B by A.
This section explains about the strong and weak redundancies.
A collection of relations are is to be strongly redundant if it holds at least one relation that contains a projection which is derivable from the other projections of relations from the set.
A set of relations is said to be weakly redundant if it has a relation that contains a projection which is not derivable from the other members in the collection but is always a projection of some join of other projections of relations in that collection.
Considering a set of relations, the system should be provided with the information if there are any associated redundancies to this set, so that the set can enforce consistency. The set is said to be consistent only if it confirms to the provided redundancies.