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

Navigating Oracle object IDs (OIDs)

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

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:

First we create an object type called dept_type that holds the department column definitions dept_name and dept_supervisor
Next we define the department table using this type
Now we create the employee_type. Note that the department column is strongly typed.
Finally we define the employee table .

Embedded object Id

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 [,...]);