Lesson 4	Navigating with object IDs (OIDs)
Objective	Embed an OID in a table.

Navigating Oracle object IDs (OIDs)

One of the most exciting object-oriented features of Oracle is the option to define and store object ID's (OID's) inside Oracle tables. An OID is like a Row ID, and is used to quickly retrieve a row. Unlike a row ID that may change after database reorganization, OIDs are absolute, and move with the target row.
Oracle uses the term OID to mean two very different things.

An OID can be an Object ID, or
an OID can be the Oracle Internet Directory.

Be careful when reading the Oracle documentation.

Define an OID using DEREF

Object ID's are used with the SQL DEREF command. You can define a table with a column that contains an OID, and then DEREF that OID to see the contents of that row.
Example: Performing a traditional SQL join

We will now look at a simple example. Suppose that you have an employee and a department table. Without OIDs, you must perform a traditional SQL JOIN to see the department information for an employee:

Select
   Emp_name,
   Dept_name,
   Dept_supervisor
From
   Emp,
   Dept
Where
   Emp.dept_no = dept.dept_no;

Performing a SQL join normally involves reading two indexes and this consumes I/O within Oracle.

Using an OID

As an alternative, let us define an OID and store the ID of the department row directly in the employee table: The following series of images describe how an OID is used in Oracle.

1) First we create an object type called dept_type that holds the department column definitions dept_name and dept_supervisor — 1)
Create type dept_type as object( dept_name char(40), dept_supervisor char(40) );
First we create an object type called dept_type that holds the department column definitions dept_name and dept_supervisor

2) Next we define the department table using this type. — 2)
create table dept of dept_type;
Next we define the department table using this type.

3) Now we create the employee_type. Note that the department column is strongly typed. — 3)
create type emp_type as object ( emp_name char(40), department ref(dept_type) );

Now we create the employee_type. Note that the department column is strongly typed. That is, the department column may only contain OID's belonging to department objects. Oracle does not allow you to insert an OID for any other table into this column.

4) Finally we define the employee table — 4)
create table employee of emp_type;
Finally we define the employee table

Object view is a Virtual Object Table

Just as a view is a virtual table, an object view is a virtual object table. Each row in the view is an object, which is an instance of an object type. An object type is a userdefined data type and you can

retrieve,
update,
insert, and
delete

relational data as if it were stored as an object type. You can also define views with columns that are object data types, such as objects, REFs, and collections (nested tables and VARRAYs). Like relational views, object views can present only the data that database administrators want users to see. For example, an object view could present data about IT programmers but omit sensitive data about salaries. The following example creates an employee_type object and then the view it_prog_view based on this object:

CREATE TYPE employee_type AS OBJECT
(
employee_id NUMBER (6),
last_name VARCHAR2 (25),
job_id VARCHAR2 (10)
);
/
CREATE VIEW it_prog_view OF employee_type
WITH OBJECT IDENTIFIER (employee_id) AS
SELECT e.employee_id, e.last_name, e.job_id
FROM employees e
WHERE job_id = 'IT_PROG';

Object Views

Object views are useful in prototyping or transitioning to object-oriented applications because the data in the view can be taken from relational tables and accessed as if the table were defined as an object table. You can run object-oriented applications without converting existing tables to a different physical structure.

It should be clear that dereferencing an OID is far faster than using a JOIN to get the department information. In many cases, dereferencing OID's causes only one I/O, as opposed to several I/O for an SQL JOIN. In summary, the embedded OID allows navigation between tables and means that we no longer need to rely on the costly join. The next lesson explores the Oracle nested table.

Assigning an Object Identifier to an Object Type

The CREATE TYPE statement has an optional keyword OID, which associates a user-specified (OID) object identifier with the type definition. It should be used by anyone who creates an object type that will be used in more than one database. Each type has an OID and if you create an object type and do not specify an OID. In addition, Oracle generates an OID and assigns it to the type. Oracle uses the OID internally for operations pertaining to that type. Using the same OID for a type is important if you plan to share instances of the type across databases for such operations as export/import and distributed queries.
Note: In CREATE TYPE with OID, an OID is assigned to the type itself. Each row in a table with a column of the specified type will have a row-specific OID.

Suppose that you want to create a SpecialPerson type, and then instantiate this type in two different databases in tables named SpecialPersonTable1 and SpecialPersonTable2. The RDBMS needs to know that the SpecialPerson type is the same type in both instances, and therefore the type must be defined using the same OID in both databases. If you do not specify an OID with CREATE TYPE, a unique identifier is created automatically by the RDBMS. The syntax for specifying an OID for an object type is in Example 3.
Example 3: How to Specify an ODI for an Object Type

CREATE OR REPLACE TYPE type_name OID 'oid' 
AS OBJECT (attribute datatype [,...]);