We have already seen a partial example of an object table with nested tables. An object table is a table that is created based on a TYPE, not as a collection of columns. Normally, a CREATE TABLE statement would look like this:
create table t ( x int, y date, z varchar2(25) );

An object table creation statement looks more like this:
create table t of Some_Type;

The attributes (columns) of T are derived from the definition of SOME_TYPE. Let’s quickly look at an example involving a couple of types and then review the resulting data structures:

$ sqlplus eoda/foo@PDB1
SQL> create or replace type address_type as object
( city varchar2(30),street varchar2(30),state varchar2(2),zip number)/Type created.

SQL> create or replace type person_type as object( name varchar2(30),
dob date,home_address address_type,work_address address_type)/Type created.
SQL> create table people of person_type/Table created.

In a nutshell, that’s all there is to it. We create some type definitions, and then we can create tables of that type. The table appears to have four columns representing the four attributes of the PERSON_TYPE we created. We are at the point where we can now perform DML on the object table to create and query data:

SQL> insert into people values ( ‘Tom’, ’15-mar-1965′,address_type( ‘Denver’, ‘123 Main Street’, ‘Co’, ‘12345’ ),address_type( ‘Redwood’, ‘1 Oracle Way’, ‘Ca’, ‘23456’ ) );1 row created.
SQL> select name, dob, p.home_address Home, p.work_address work from people p;
Tom 15-MAR-65ADDRESS_TYPE(‘Denver’, ‘123 Main Street’, ‘Co’, 12345)
ADDRESS_TYPE(‘Redwood’, ‘1 Oracle Way’, ‘Ca’, 23456)
SQL> select name, p.home_address.city from people p;NAME HOME_ADDRESS.CITY

We’re starting to see some of the object syntax necessary to deal with object types. For example, in the INSERT statement we had to wrap the HOME_ADDRESS and WORK_ ADDRESS with a CAST. We cast the scalar values to be of an ADDRESS_TYPE. Another way of saying this is that we create an ADDRESS_TYPE instance for that row by using the default constructor for the ADDRESS_TYPE object.

Now, as far as the external face of the table is concerned, there are four columns in our table. By now, after seeing the hidden magic that took place for the nested tables, we can probably guess that there is something else going on. Oracle stores all

•\ SYS_NC_OID$: This is the system-generated object ID of the table. It is a unique RAW(16) column. It has a unique constraint on it, and there is a corresponding unique index created on it as well.

•\ SYS_NC_ROWINFO$: This is the same magic function we observed with the nested table. If we select that from the table, it returns the entire row as a single column:

SQL> select sys_nc_rowinfo$ from people;

•\ NAME, DOB: These are the scalar attributes of our object table. They are stored much as we would expect, as regular columns.

•\ HOME_ADDRESS, WORK_ADDRESS: These are magic functions as well. They return the collection of columns they represent as a single object. These consume no real space except to signify NULL or NOT NULL for the entity.

•\ SYS_NCnnnnn$: These are the scalar implementations of our embedded object types. Since the PERSON_TYPE had the ADDRESS_ TYPE embedded in it, Oracle needed to make room to store them in the appropriate type of columns. The system-generated names are necessary since a column name must be unique, and there is nothing stopping us from using the same object type more than once as we did here. If the names were not generated, we would have ended up with the ZIP column twice.

So, just like with the nested table, there is a lot going on here. A pseudo primary key of 16 bytes was added, there are virtual columns, and an index was created for us.

We can change the default behavior with regard to the value of the object identifier assigned to an object, as we’ll see in a moment. First, let’s look at the full verbose SQL that would generate our table for us.

This was generated using Data Pump, since I wanted to easily see the dependent objects, including all of the SQL needed to re-create this particular object instance. This was achieved via the following:

$ expdp eoda directory=tk tables=’PEOPLE’ dumpfile=p.dmp logfile=p.log
$ impdp eoda directory=tk dumpfile=p.dmp logfile=pi.log sqlfile=people.sql
Master table “EODA”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded
Starting “EODA”.”SYS_SQL_FILE_FULL_01″:
eoda/ directory=tk dumpfile=p.dmp logfile=pi.log sqlfile=people.sql
Review of the people.sql file that results would show this:
— new object type path: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE “EODA”.”PEOPLE” OF “EODA”.”PERSON_TYPE” OID ‘F0484A73A93A7093E043B7D04F0A821B’
OIDINDEX ( PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “USERS” )

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE “USERS” ;

This gives us a little more insight into what is actually taking place here. We see the OIDINDEX clause clearly now, and we see a reference to the OID column followed by a hex number.

The OID ” syntax is not documented in the Oracle documentation. All this is doing is ensuring that during an expdp and subsequent impdp, the underlying type PERSON_TYPE is, in fact, the same type. This will prevent an error that would occur if we performed the following steps:
\ 1.\ Create the PEOPLE table.
\ 2.\ Export the table.
\ 3.\ Drop the table and the underlying PERSON_TYPE.
\ 4.\ Create a new PERSON_TYPE with different attributes.
\ 5.\ Import the old PEOPLE data.

Obviously, this export cannot be imported into the new structure—it will not fit. This check prevents that from occurring.

If you remember, I mentioned that we can change the behavior of the object identifier assigned to an object instance. Instead of having the system generate a pseudo primary key for us, we can use the natural key of an object.

At first, this might appear self-defeating—the SYS_NC_OID$ column will still appear in the table definition in SYS. COL$, and, in fact, it will appear to consume massive amounts of storage as compared to the system-generated column. Once again, however, there is magic at work here.
The SYS_NC_OID$ column for an object table that is based on a primary key and not system generated is a virtual column and consumes no real storage on disk.

Here is an example that shows what happens in the data dictionary and demonstrates that there is no physical storage consumed for the SYS_NC_OID$ column. We’ll start with an analysis of the system-generated OID table:

SQL> create table people of person_type
SYS_NC_OID$ 23 16
SYS_NC_ROWINFO$ 121 1
SQL> insert into people(name) select rownum from all_objects; 72069 rows created.
SQL> exec dbms_stats.gather_table_stats( user, ‘PEOPLE’ ); PL/SQL procedure successfully completed.
SQL> select table_name, avg_row_len from user_object_tables;

We see here that the average row length is 24 bytes: 16 bytes for the SYS_NC_OID$ column and 8 bytes for the NAME column. Now, let’s do the same thing, but use a primary key on the NAME column as the object identifier:

SQL> CREATE TABLE “PEOPLE”OF “PERSON_TYPE”( constraint people_pk primary key(name) )object identifier is PRIMARY KEY/Table created.
SQL> select name, type#, segcollengthfrom sys.col$where obj# = ( select object_idfrom user_objectswhere object_name = ‘PEOPLE’ )

According to this, instead of a small 16-byte column, we have a large 81-byte column! In reality, there is no data stored in there. It will be null. The system will generate a unique ID based on the object table, its underlying type, and the value in the row itself. We can see this in the following:
SQL> insert into people (name) values ( ‘Hello World!’ ); 1 row created.
SQL> select sys_nc_oid$ from people p;

If we select out the SYS_NC_OID$ column and inspect the HEX dump of the string we inserted, we see that the row data itself is embedded in the object ID. Converting the object ID into a VARCHAR2, we can just confirm that visually. Does that mean our data is stored twice with a lot of overhead with it? No, it is not—it is just factored into that magic thing that is the SYS_NC_OID$ column upon retrieval. Oracle synthesizes the data upon selecting from the table.

Now for an opinion. The object-relational components (nested tables and object tables) are primarily what I call syntactic sugar. They are always translated into good old relational rows and columns. I prefer not to use them as physical storage mechanisms personally.

There are too many bits of magic happening—side effects that are not clear. You get hidden columns, extra indexes, surprise pseudo columns, and so on. This does not mean that the object-relational components are a waste of time. On the contrary, I use them in PL/SQL constantly. I use them with object views. I can achieve the benefits of a nested table construct (less data returned over the network for a master/detail relationship, conceptually easier to work with, and so on) without any of the physical storage concerns.

That is because I can use object views to synthesize my objects from my relational data. This solves most of my concerns with object tables/nested tables in that the physical storage is dictated by me, the join conditions are set up by me, and the tables are available as relational tables (which is what many third-party tools and applications will demand) naturally.

The people who require an object view of relational data can have it, and the people who need the relational view can have it. Since object tables are really relational tables in disguise, we are doing the same thing Oracle does for us behind the scenes, only we can do it more efficiently, since we don’t have to do it generically as they do.

For example, using the types defined earlier, I could just as easily use the following:
SQL> create table people_tab( name varchar2(30) primary key,dob date,
home_city varchar2(30),
home_street varchar2(30),
home_state varchar2(2),
home_zip number,
work_city varchar2(30),
work_street varchar2(30),
work_state varchar2(2),
work_zip number)/Table created.

SQL> create view people of person_type with object identifier (name)
asselect name, dob,
address_type(home_city,home_street,home_state,home_zip) home_adress,
address_type(work_city,work_street,work_state,work_zip) work_adress
from people_tab/
View created.
SQL> insert into people values ( ‘Tom’, ’15-mar-1965′,
address_type( ‘Denver’, ‘123 Main Street’, ‘Co’, ‘12345’ ),
address_type( ‘Redwood’, ‘1 Oracle Way’, ‘Ca’, ‘23456’ ) );1 row created.

However, I achieve very much the same effect; I know exactly what is stored, ow it is stored, and where it is stored. For more complex objects, we may have to code NSTEAD OF triggers on the object views to allow for modifications through the view.

Leave a Reply

Your email address will not be published. Required fields are marked *