RelationalDBDesign





PL/SQL   «Prev  Next»
Lesson 6 Nested tables
Objective Determine when to use nested tables in querying.

Querying Nested Tables using PL/SQL

A nested table is a single-dimension, unbounded collection of homogenous elements. It is initially dense and can become sparse through deletions. The association of a nested table with another table is similar to a parent-child relationship. You have the option of adding or deleting records from the nested table. Therefore, when you define a nested table, you define its attributes and associate it with an object table.
When you add records to the nested table, it is considered dense. When you delete records from the nested table, it becomes sparse.
The primary difference between a nested table and a varray is that a nested table is unbounded. In other words, there is no preset limit on the maximum number of elements that the table can hold. On the other hand, a varray is bounded. You must specify an upper limit that indicates how large the varray can be. After you define the upper limit, the varray is always considered dense.
The following example defines DETAIL_TABLE as a nested table, each row of which contains an object of type SALE_DETAIL. To represent the sale details, a nested table is a better choice than a varray for the following reasons:

  1. Querying the contents of sale details is likely to be a requirement for most applications. This is an inefficient operation for a varray because a varray has a fixed number of elements. On the other hand, a table can have unlimited number of elements (that is, records).
  2. Indexing on sale details data may be a requirement for some applications. A nested table allows indexing. However, this is not possible with a varray.
  3. There is no practical upper bound on the number of sale details on a sale. Using a nested table does not require specifying an upper bound; using a varray does.
The following Slide Show explains the three steps used in creating a nested table and associating it with a different table:



Netsted Oracle Tables
We will look in more detail at querying varrays and nested tables later in the course.
In the next lesson, you will learn how to write SQL to query an object table.

Nested Table - Quiz

Click the Quiz link below to test your knowledge of object-relational concepts.
Nested Table - Quiz