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

Constructing a table with repeating values

Oracle has recognized that there are cases where it is better to allow repeating items in a table.
The Slide Show 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
  2. Next we create a type called jobhist_arr
  3. We create our employee table, specifying a column called 'emp'.

Using varrays in 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