Database Reference
In-Depth Information
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:
EODA@ORA12CR1> create table people of person_type
2 /
Table created.
EODA@ORA12CR1> select name, type#, segcollength
2 from sys.col$
3 where obj# = ( select object_id
4 from user_objects
5 where object_name = 'PEOPLE' )
6 and name like 'SYS\_NC\_%' escape '\'
7 /
NAME TYPE# SEGCOLLENGTH
-------------------- ---------- ------------
SYS_NC_OID$ 23 16
SYS_NC_ROWINFO$ 121 1
EODA@ORA12CR1> insert into people(name)
2 select rownum from all_objects;
72069 rows created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'PEOPLE' );
PL/SQL procedure successfully completed.
EODA@ORA12CR1> select table_name, avg_row_len from user_object_tables;
TABLE_NAME AVG_ROW_LEN
-------------------- -----------
PEOPLE 24
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:
EODA@ORA12CR1> CREATE TABLE "PEOPLE"
2 OF "PERSON_TYPE"
3 ( constraint people_pk primary key(name) )
4 object identifier is PRIMARY KEY
5 /
Table created.
