REFs

The specification of course_type shows a REF to the professor_type. REFs are used to describe a relationship (or reference) between two distinct objects. The professor_type is not a building-block type of the course_type. Think in terms of real-world objects. A course has a course number, a course description, and a number of credits. Each of these items belongs to an individual course. A course number will only belong to one course. The professor is not a part of the single course. The professor is a separate object that is referenced by the course. In fact, a professor may teach several courses. The REF column stores an object ID for the object that is referenced. It will only refer to a single object, not a group of objects. One may also notice that the CREATE TABLE statement of the course_table uses the SCOPE FOR phrase. This is similar to the REFERENCES phrase in relational designs. It limits the possible objects that can be referenced. In this case, only an object from the professor table can be referenced in the course_table.professor attribute.

The REF attribute stores the OID of the object to be referenced. It is also worth noting that a scoped REF uses less space than an unscoped REF. A scoped REF uses 16 bytes, whereas an unscoped REF uses almost 200 percent more space (46 bytes). Since most REF columns will be created for a specific object, it is common to use scoped REFs.

For faster access, the ROWID may also be included in the REF column. The ROWID takes up additional space, but it retrieves the referenced object much faster. The WITH ROWID clause can be used with both scoped and unscoped REFss.

Nested Tables and VARRAYs

Oracle has provided a new mechanism of storing sets of data inside of a row. In a relational database, data sets associated with a row are stored in separate tables. This type of relationship is called a one-to-many relationship. The data set is related to the row through a primary key/foreign key relationship. For example, a musical CD-ROM will store a set of songs. A relational database would contain a parent record which holds information relating to the entire CD-ROM, as well as several child records in a separate table that contains data on each individual song (see Figure 15.5).

Figure 15.4.  A one-to-many relationship in a relational database.

Figure 15.5.  Nested table storage.

This method is inefficient. An expensive join must be performed every time the two sets of data are used together. Storing the child records inside the parent record is a better solution. This allows a faster retrieval of data as well as being a model that better resembles real-world objects. New object types in Oracle8 may be created with nested tables and VARRAYs.

Nested Tables

A nested table is a table that is embedded inside of a record in a table. Figure 15.6 shows the conceptual storage of a nested table, as well as how the data is actually stored in the database. A storage table is created for each column in a table that holds nested tables. A table with three nested table columns will have three storage tables associated with it. The nested tables for each row are stored in this storage table outside the main table. Storage tables cannot be accessed directly. They must only be accessed through the parent table.

Figure 15.6.  Storing files in a relational database.

A nested table can be used through the creation of a type. Once the type has been created, it may be used in a CREATE TABLE statement. Listing 15.7 shows some of the SQL that can be performed involving nested tables.

Listing 15.7. Nested tables.

— The AS TABLE OF clause is used to specify the definition of a nested

 table

CREATE OR REPLACE TYPE children_type AS TABLE OF varchar2(30);



CREATE TABLE family (family_last_name varchar2(40), children

 children_type)

NESTED TABLE children STORE AS children_table;



— The Smith family is added to the table

— The INSERT statement calls the type of the nested table

INSERT INTO family VALUES (‘Smith’, children_type

 (‘Sally’,’Billy’,’Joey’));



— Susy is born and is added to the family record

UPDATE family SET children = children_type(‘Sally’,’Billy’,’Joey’,’

Susy’);



— The Smith family is deleted from the table

DELETE FROM family WHERE family_last_name = ‘Smith’; 

The CREATE TABLE clause contains the NESTED TABLE clause. This clause specifies the name of the storage table that will be used to contain the rows for the nested tables. Again, this table cannot be accessed directly. The data must be accessed through the parent table.

VARRAYs

A VARRAY is similar to a nested table in the way it associates a set of child records with the parent record. Unlike the nested table, a VARRAY stores the child data in the same blocks as the table. VARRAYs also have a set limit on the number of values that can be stored. A maximum is set at the creation of the type. First, a type is created that will hold the VARRAY. Next, a table is defined that uses the type to create a VARRAY column. In Listing 15.8, a VARRAY type is created that specifies a maximum of 100 rows of varchar2(30) records. In this particular case, a maximum of 100 children can be in a single family. The name of each child can use a maximum of 30 characters. This listing also shows how to add, change, and delete a VARRAY. Notice that the SQL for inserts, updates, and deletes is similar (in this case identical) to the SQL for nested tables.

Listing 15.8. Using VARRAYs.

CREATE OR REPLACE TYPE children_type AS VARRAY(100) OF varchar2(30);

CREATE TABLE family (family_last_name varchar2(40), children

children_type);

— The Smith family is added to the table

INSERT INTO family VALUES (‘Smith’, children_type

 (‘Sally’,’Billy’,’Joey’));

— Susy is born and is added to the family record

UPDATE family SET children = children_type(‘Sally’,’Billy’,’Joey’,’

Susy’);

— The Smith family is deleted from the table

DELETE FROM family WHERE family_last_name = ‘Smith’;

---

CAUTION:  
Nested tables and VARRAYs should not be used for all relationships. If other applications will directly access the data, it may be best to stick with separate objects.

---