16.3 How do I…Create and use object tables?
Problem
I have created some relational tables containing column objects. If I reuse an object type definition as a column in other relational tables, then I will store the same data in more than one place. This seems inefficient and may adversely effect data integrity. How can I avoid these problems and still make use of Oracle8’s object extensions?
Technique
How-To 16.1 introduced a relational table called EMP16 with an embedded object type called DEPT_TYPE16 to store three department attributes. Suppose that the organization of interest has some limited resource, say a set of graphics workstations, and management wants to maintain a record of which departments have received a share of this resource. In this case, it makes sense to enhance the corporate database to include a table containing workstation numbers and the departments where the graphics machines reside. The script in Listing 16.1 will do this.
Listing 16.1 A script to create a table called WORKSTN_LOC
SQL> CREATE TABLE workstn_loc16
2 ( workstn_number number(4),
3 dept dept_type16>
4 );
Now the table EMP16 and the table WORKSTN_LOC16 both contain the DEPT_TYPE16 object type as an embedded object. There must be a way to duplicate the functionality of foreign key constraints without introducing this redundancy, right?
Right. The place to start is with an object table. An object table is a table where each row is an instantiation of an object type. Other objects, like relational tables, can point to the rows of an object table through the Oracle8 REF operator. The single argument to this operator is a table alias of a row table; the operator returns the object ID of the requested row.
Steps
1. Start SQL*Plus, connect as the WAITE user, and use the START command to load and execute the script CHP16_6.SQL. This single line script and its output appear in Figure 16.6.
The script creates an object table called DEPTS16.
2. Use the START command to load and execute the script CHP16_7.SQL. The script and its output appear in Figure 16.7.
This script creates a new version of the EMP16 table. This table differs from the earlier version of EMP16 only through the datatype of the DEPT column. Here, the DEPT column is a reference (or pointer) to an object of type DEPT_TYPE16.
Note-REF datatypes are more strongly typed than other datatypes in that they will only accept pointers to objects of one type.
3. Add a few rows to the DEPTS16 table by using the START command to load and execute the script CHP16_8.SQL. The script and its output appear in Figure 16.8.
Notice that the first insert statement uses standard SQL syntax and that the second statement uses the constructor method for the DEPT_TYPE16 object type.
4. Now add two employees to the EMP16 table by using the START command to load and execute the script in CHP16_9.SQL; Figure 16.9 shows this script and its output.
The script uses two INSERT INTO…SELECT FROM… statements to insert two rows into the EMP16 table, one for a programmer and a second for his supervisor. The REF operator appears in line 4 in both statements and takes the correlation variable for the DEPTS16 table as an argument.
5. To select rows from the EMP16 table, you will need to dereference the pointer to the DEPTS16 table. The CHP16_10.SQL script shows you what output to expect if you do not dereference an object pointer. The script also shows how to use the DEREF operator or dot notation to dereference an object pointer. The script and its output appear in Figure 16.10.
The first SELECT statement does not dereference the object pointer to DEPT16 and the result is garbage. The second SELECT statement uses the DEREF operator and the third uses dot notation to dereference the object pointer.
How It Works
Step 1 creates an object table called DEPTS16 to store objects of the type DEPT_TYPE16. The second step creates the EMP16 table and references the object table DEPTS16 using the REF datatype. Steps 3 and 4 populate the DEPTS16 and EMP16 tables, respectively. Step 5 shows two ways to dereference the object pointers in the EMP16 table.
Comments
Although object pointers complicate the syntax for the INSERT statement slightly, they are a valuable object extension to the Oracle8 server. This is because dereferencing an object pointer is essentially a very fast join with almost no I/O overhead. In effect, Oracle8 enhances join performance considerably at the expense of slightly more complicated and frequent INSERTs. The trade off, however, is worthwhile.