Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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

 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks