Using the Oracle nested table structure, subordinate data items can be directly linked to the base table.
First we create an Oracle TYPE, using a full_mailing_address_type abstract data type:
-- step #1
create type full_mailing_address_type
( st_addr varchar2(30),
-- step #2
create type prev_addrs as object
( prior_address -- created earlier
full_mailing_address_type -- created in step #1
-- Next, we create the nested object:
-- step #3
create type nested_address
as table of prev_addrs; -- created in step #2
-- Now, we create the parent table with the nested table.
-- step #4
create table emp
( last_name char(40),
current_address full_mailing_address_type, -- created in step #1
prev_address nested_address -- created in step #3
nested table prev_address -- created in step #2
store as nested_prev_address return as locator;
The nested_prev_address subordinate table can be indexed just like any other Oracle table.
Also, notice the use of the return as locator SQL syntax. In many cases, returning the entire nested table at query time can be time-consuming.
I have a situation. 1 friend can has many groups(Friends.bestfriends) and 1 groups can has many friends. My current design is a nested table which has a table call friendlist and each row is uniquely indentified by a customerId and each customre has a nested table of friends.
But previously when i designed this, i do not have the group function so this design works pretty well for me but now there is group function, I am considering having a nested table within another as under 1 customer can have many groups and under groups has many friends. So is this design right? can anyone out there help me? thanks