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 information.

• 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

Since the 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 Oracle8 table

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.