nested tables of user defined types
Hello all,
I have philosophical question I am hoping someone can answer for me, "To TYPE, or not to TYPE; that is the question."
I have created several layers of nested tables in the form:
CREATE TYPE xyz AS OBJECT();
CREATE TYPE table_xyz AS TABLE OF xyz;
CREATE TYPE abc AS OBJECT(
nested_table1 table_xyz;
);
..
and so on until I end up creating a main table with all these sub tables nested into it.
But from what I understand oracle stores all of the information in a column of type NESTED TABLE into a single table, rather than one table per row. This means that if I declare a primary key in the nested table it will be against all values in the outer column, not just limited to the values in an individual row of the outer table. Plus it will not let me create foreign keys with in a nested table.
So why should I use the above construct instead of simply creating separate tables, and use primary and foreign keys to relate them and joins to query?
I can see the use for types in small cases (addresses, person, etc..) but I do not see the advantage of nested tables. Could someone shed some light on the subject for me?
Thanks,
Thomas