 |
|
Oracle Table Design with object ID's
Oracle Tips by Burleson |
Chapter 6 Oracle Table Design
CREATE TABLE
CUSTOMER (customer_data customer_stuff);
Nesting abstract data types in this way allows
the duplication of the object-oriented concept of encapsulation. In
other words, groups of related data types are placed into a
container that is completely self-contained while retaining the full
capability of the innate relational data types, such as INT and
CHAR.
Nested abstract data types are displayed in the
same fashion as the sample queries seen earlier, except that the
target data types require several dots to delimit the levels of
nesting within the data structure. For example, the query below
displays the street_address for a customer:
SELECT
customer_stuff.customer_name.zip_code
FROM
CUSTOMER
WHERE
customer_stuff.customer_name.zip_code like ‘144%’;
The above reference to zip_code must be
preceded with customer_name since it is nested in this data type.In
turn, the customer_name data type is nested within the
customer_stuff data type. The proper SQL reference to zip_code is
therefore expressed as follows:
customer_stuff.customer_name.zip_code.
With this general understanding of how abstract
data types function and operate within Oracle9i, we are ready to see
how “pointers” are used within Oracle to establish relationships
between table rows.
Design with Oracle Object IDs
It is necessary to understand exactly what
object IDs represent in Oracle 9i and how they are implemented
before we discuss navigating the database with pointers. Each record
in a database had a unique database address in pre-relational
databases. The addresses corresponded to a physical database block,
similar to Oracle ROWIDS. The offset or displacement of the target
record into the block was included in the address. For example, an
address of 552:3 referred to the third record in block number 552
of the database. These addresses could be stored inside other
records once defined. This method allowed one record to point to
another record. In pre-relational databases, these pointers were the
foundation of relationships between entities.
Modern object databases store objects in an
object table with an assigned object ID, commonly called an OID. An
OID is guaranteed to be globally unique. Each OID consists of a
128-byte hexadecimal value. An OID cannot be used to locate an
object instance by itself. Only a REF (or reference, discussed later
in this chapter) containing location data can be used to locate an
object instance.
The concept of the ROWID, introduced in
Oracle7, is also used in Oracle 9i to uniquely identify each row in
each table in a database. The ROWID in Oracle7 is a VARCHAR2
representation of a binary value shown in hexadecimal format. It is
displayed as
bbbbbbbb.ssss.ffff
where
* bbbbbbbb is the block ID
* ssss is the sequence in the block
* ffff is the file ID
The ROWID can be used to detect and eliminate
duplicate rows in instances where a primary key for a table allows
duplicate values. ROWIDs for duplicate customers are displayed in
the following SQL:
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
