Database Reference
In-Depth Information
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 81
SYS_NC_ROWINFO$ 121 1
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:
EODA@ORA12CR1> insert into people (name) values ( 'Hello World!' );
1 row created.
EODA@ORA12CR1> select sys_nc_oid$ from people p;
SYS_NC_OID$
----------------------------------------------------------------------------------------------------
F04931FE974478A7E043B7D04F0A082000000017260100010001002900000000000C07001E0100002A00078401FE00000014
0C
48656C6C6F20576F726C6421
000000000000000000000000000000000000
EODA@ORA12CR1> select utl_raw.cast_to_raw( 'Hello World!' ) data from dual;
DATA
----------------------------------------------------------------------------------------------------
48656C6C6F20576F726C6421
EODA@ORA12CR1> select utl_raw.cast_to_varchar2(sys_nc_oid$) data from people;
DATA
-------------------------------------------------------------------------------
<garbage bits and bytes..>Hello World!
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
