Data Structures   «Prev  Next»
Lesson 6Using VARRAYs in Tables
ObjectiveDefine a table with a VARRAY.

Using Oracle VARRAYs

Creating non-first normal form Tables

It has long been argued that relational databases should be able to contain repeating data items. Until recently, C. J. Date and E. F. Codd , the creators of the relational database model, expressed concern about the viability of non-first normal form tables.

Relational Database Model

Here is the formal definition of a relational database management system. While working at IBM I was involved in technical planning and design for the IBM products 1) SQL/DS and 2) DB2. In addition, I was also involved with Edgar F. Codd's relational model for database management.

Database Management Systems

The methodology presented on this site for relational (DBMSs) Database Management Systems has been tried and tested over the years in both industrial and academic environments. It consists of three main phases:
  1. conceptual,
  2. logical, and
  3. physical
database design. The first phase starts with the production of a conceptual data model that is independent of all physical considerations. This model is then refined in the second phase into a logical data model by removing constructs that cannot be represented in relational systems. In the third phase, the logical data model is translated into a physical design for the target DBMS. The physical design phase considers the storage structures and access methods required for efficient and secure access to the database on secondary storage.The methodology in each phase is presented as a series of steps. For the inexperienced designer, it is expected that the steps will be followed in the order described, and guidelines are provided throughout to help with this process. For the experienced designer, the methodology can be less prescriptive, acting more as a framework or checklist.

E. F. Codd

Dr. E. F. Codd, the inventor of the relational database model, developed a list of criteria that a relational database must meet. This list is often referred to as Codd's Rules. Chris Date's more basic definition and Codd's twelve-rule test for relational systems form a general definition described below. To be considered fully relational, a database management system must:
  1. Maintain a logical definition of data separate from its physical storage characteristics
  2. Represent all information in the database in table form
  3. Use one, high-level language for structure, query, and modification of database information to handle communications with the database (most use SQL)
  4. Support the main relational operations, including selection of rows and columns and the join operation, and set operations, union, intersection, division, and difference
  5. Support views to allow the user to create other ways of looking at data using virtual tables
  6. Provide a means of differentiating between null or unknown values and a blank or zero
  7. Support mechanisms for managing integrity, permissions, transactions, and data recovery


Constructing a Table with repeating Values

Prior to Oracle, there was no way to construct a table with repeating values. The Oracle designer had to create a subordinate table and use a JOIN to get the information. Oracle has recognized that there are cases where it is better to allow repeating items in a table.
The series of images below describes how the use of repeating groups or VARRAYS allows us to dramatically improve the performance of Oracle queries because subordinate tables are no longer required in order to represent a one-to-many relationship.

1) Create a data type called jobhist that contains the previous employers name and phone number, and the salary of the employee.
1) 
CREATE TYPE
jobhist
AS object(
employer_name NUMBER,
employer_phone VARCHAR(20),
sal NUMBER);
Create a data type called jobhist that contains the previous employers name and phone number, and the salary of the employee.

2) Next we create a type called jobhist_arr. This creates a repeating group that can repeat job histories up to 10 times
2) 
CREATE TYPE
  jobhist_arr
AS
  VARRAY(10) OF jobhist;
Next we create a type called jobhist_arr. This creates a repeating group that can repeat job histories up to 10 times.

3) We create our employee table, specifying a column called 'emp'. As we see, the emp colum is of the datatype jobhist_arr.
3) 
CREATE TABLE
employee
(
  empno NUMBER,
  dname VARCHAR2(20),
  location VARCHAR(20),
  emp jobhist_arr
);
We create our employee table, specifying a column called emp. As we see, the emp colum is of the datatype jobhist_arr.

Oracle offers a variety of data structures to help create robust database systems. Oracle supports the full use of
  1. (BLOB) binary large objects,
  2. nested tables,
  3. non first-normal-form table structures (VARRAY tables), and
  4. object-oriented table structures.
In addition, flat files are treated as if they were tables within the Oracle database.
It is a challenge to many Oracle professionals to know when to use these Oracle data model extensions. Here is a brief review of advanced Oracle topics and how they are used to design high-performance Oracle databases. The ability of Oracle to support object types (sometimes called user-defined datatypes) has profound implications for Oracle design and implementation. User-defined datatypes will enable the database designer to:
  1. Create aggregate datatypes: Aggregate datatypes are datatypes that contain other datatypes. For example, you could create a type called FULL_ADDRESS that contains all of the subfields necessary for a complete mailing address.
  2. Nest user-defined datatypes: Datatypes can be placed within other user-defined datatypes to create data structures that can be easily reused within Oracle tables and PL/SQL.
    For example, you could create a datatype called CUSTOMER that contains a datatype called CUSTOMER_DEMOGRAPHICS, which in turn contains a datatype called JOB_HISTORY, and so on.
One of the user-defined data types in the Oracle object-relational model is a "pointer" data type. A pointer is a unique reference to a row in a relational table. The ability to store these row IDs inside a relational table extends the traditional relational model and enhances the ability of an object-relational database to establish relationships between tables.

This technique is very important to Oracle performance. Prior to Oracle, repeating groups had to be isolated into a JOBHIST table, and an SQL JOIN operation was required to access the job history for an employee.

Advantages of using tables with VARRAYs

Now that Oracle provides the ability to allow repeating groups in a table, we have:
  1. Condensed a one-to-many data relationship into a single table, and can retrieve employee information and job history in a singleI/O
  2. Avoided the requirement to create a separate table and to join multiple tables to get repeating values
The next lesson wraps up this module.

Create varray - Exercise

Before you move on to the next lesson, click the Exercise link below to test your knowledge of VARRAYs.
Create varray - Exercise