Nested tables

Hi,
I am trying to figure out how to properly use nested tables in Oracle. After I create type/table as below I can not insert an array of more then one value. I must be doing something wrong, but the insert below gives me a unique constraint PK_RESOURCEPROPERTIES_VALUE violation. PK_RESOURCEPROPERTIES_VALUE is a primary key on the nested table.
If I drop the primary key, then everything works. But in the case Oracle shows "full access table" on the nested table when performing a select. If I create an index on the nested table, than the plan is ok and the queries are fast, but I can not store a collection of more than one value.
If I make the nested table's primary key this way ( (constraint PK_RESOURCEPROPERTIES_VALUE PRIMARY KEY (NESTED_TABLE_ID, COLUMN_VALUE)) organization index compress ) then I can not have null values in this array.
Thanks

create or replace type DM_tVarchar2Array as table of Varchar2(1024);

create table DM_RESOURCEPROPERTIES (
    RESOURCEID   INTEGER not null,
    NAME         VARCHAR2(128) not null,
    VALUE        DM_tVarchar2Array,
    constraint PK_RESOURCEPROPERTIES primary key (RESOURCEID, NAME)
  ) organization index
    NESTED TABLE value STORE AS DM_RESOURCEPROPERTIES_VALUES
    ( (constraint PK_RESOURCEPROPERTIES_VALUE PRIMARY KEY (NESTED_TABLE_ID)) organization index overflow );
   
This one fails
insert into DM_RESOURCEPROPERTIES(RESOURCEID, NAME, VALUE)
  values (1, 'name1', DM_tVarchar2Array('a1', 'a2'));
imaniuk_atreusAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
SujithConnect With a Mentor Data ArchitectCommented:
>>"but then a full nested table scan will be used to find the values for every parent record"

Even if you have an index on this nested table how can this be avoided for every parent record?. This PK or index on the nested table could be helpful in case of nested tables of object types; and when you are manipulating individual nested table elements using the TABLE operator. At the main table level; the indexes may not be helpful.

Still if you like to have an index on the nested table; try out creating an index on the nested table itself; donno how far it will be useful:

something like:
create index test_idx on DM_RESOURCEPROPERTIES_VALUES(NESTED_TABLE_ID)
0
 
rbrookerCommented:
0
 
SujithData ArchitectCommented:
Conceptually what you are trying to do here is wrong.

NESTED_TABLE_ID is a unique value generated for each row in the table DM_RESOURCEPROPERTIES, and this ID is used to link to all the corresponding records in the "nested table". So, obviously there will be multiple records in the nested table for the same NESTED_TABLE_ID.
Creating a PK on this column doesnt make sense (is not possible also) in this scenario. Rather, if the nested table was on an object type, you could have used a combination of NESTED_TABLE_ID and a column from the object type to create the PK.

In short this insert "insert into DM_RESOURCEPROPERTIES(RESOURCEID, NAME, VALUE)
  values (1, 'name1', DM_tVarchar2Array('a1', 'a2'));"

Generates a NESTED_TABLE_ID corresponding to the record in the main table. And it inserts this ID into a hiddent column in the nested table along with values 'a1' and 'a2'. Since you have created a PK on NESTED_TABLE_ID it will fail.

Remove the PK constraint it will work.
0
 
imaniuk_atreusAuthor Commented:
You are right, it will work, but then a full nested table scan will be used to find the values for every parent record. I want to avoid that. That is why I need an index there. The only way I have found it will work is to create a pk on (NESTED_TABLE_ID, COLUMN_VALUE). Those are both hidden columns. This makes sure that the values in the array are unique (desirable), but a null/empty string is no longer allowed as an element of the nested array/collection. I can probably live with this limitation, but was wondering if there is a way to have an index and be able to store nulls in the array.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.