Database Reference
In-Depth Information
NAME SEGCOLLENGTH
--------------- ------------
SYS_NC_OID$ 16
SYS_NC_ROWINFO$ 1
NAME 30
DOB 7
HOME_ADDRESS 1
SYS_NC00006$ 30
SYS_NC00007$ 30
SYS_NC00008$ 2
SYS_NC00009$ 22
WORK_ADDRESS 1
SYS_NC00011$ 30
SYS_NC00012$ 30
SYS_NC00013$ 2
SYS_NC00014$ 22
14 rows selected.
This looks quite different from what
DESCRIBE
tells us. Apparently, there are 14 columns in this table, not 4. In this
case, they are:
•
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:
EODA@ORA12CR1> select sys_nc_rowinfo$ from people;
SYS_NC_ROWINFO$(NAME, DOB, HOME_ADDRESS(CITY, STREET, STATE, ZIP),
WORK_ADDRESS(CITY, STREET, STATE,
----------------------------------------------------------------------------------------------------
PERSON_TYPE('Tom', '15-MAR-65', ADDRESS_TYPE('Denver', '123 Main Street', 'Co', 12345), ADDRESS_TYPE
('Redwood', '1 Oracle Way', 'Ca', 23456))
•
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.
