BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter