Violating the Relational Rules
Oracle Tips by Burleson Consulting
List of repeating data items
For many years, the idea of repeating data
items within an object has been repugnant to relational database
designers. The foremost tenet of relational theory dictated that the
removal of repeating data items was the very first step toward a
clean data model. During normalization, the removal of repeating
groups in a relation was the very first step in relational database
design, and a table without any repeating values was said to be in
first-normal form (1NF). Hence, the re-introduction of repeating
values into Oracle8 tables is said to be a non-first-normal-form
table, or 0NF for short. Oracle8 implemented repeating groups within
tables by allowing a VARRAY datatype.
In fact, the widespread adoption of
non-first-normal-form datatypes in Oracle and other relational
databases caused C. J. Date to reconcile his definition of the
relational data model. Date introduced a new concept into the
relational model called a "set", to allow for 0NF relations to fit
into the relational paradigm.
However, there are some rules when deciding
to introduce a repeating group into an Oracle8 table.
The repeating data items should be small in size.
The data item should be static and rarely changed.
The repeating data should never need to be queried
as a "set".
We need to note that Oracle has provided us
with two ways to introduce repeating groups in a table design. We
can either use the varray construct or a nested table. While both
varray tables and nested tables serve the same purpose, they are
completely different in terms of internal structures and SQL syntax.
There are several differences between nested
tables and varray tables, and the characteristics of the data
determine which should be implemented.
Number of repeating groups - Nested tables
can have an infinite number of subordinate rows, whereas varrays
have a maximum size. Hence, small numbers of repeating groups
normally utilize varray tables. [Note: varray is ‘normal’ font and
also in italic font in many places. Need to be consistent.]
Element control - Individual elements can be
deleted from a nested table, but not from a varray. Therefore,
nested tables are best for highly dynamic data while varray tables
are best for static repeating groups such as prior employer
Internal Storage – Varray rows are stored by
Oracle in-line (in the same tablespace), whereas nested table data
is stored out-of-line in a store table, which is a
system-generated database table associated with the nested table.
Sequencing - Nested tables do not retain
their ordering and subscripts, whereas varray rows will always
retain their original sequences.
Index capabilities – Nested table entries
support indexes while varray rows do not support indexes. Hence,
repeating items that require index-based SQL should always be
implemented with nested tables.
To show how repeating
groups are implemented, let’s use a simple example. Suppose that we
are designing an employee database and we notice that every employee
has a history of prior employers. Since we only need to keep the
employers name, it does not make sense to create a subordinate table
to represent this structure. (Figure 3)
Figure 3: Normalization of repeating groups
into subordinate tables
employer_name column is the only prior employer information that
we need to keep, we can simplify the Oracle table structure and
remove an extra table join by including the previous employer names
in the employee table. Oracle provides a construct that
allows us to embed the name of all prior employers within a single
table row. (Figure 4)
Figure 4: Adding a repeating group to an
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.