Nested tables

Posted on 2006-11-23
Last Modified: 2008-02-01
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.

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

    RESOURCEID   INTEGER not null,
    NAME         VARCHAR2(128) not null,
    VALUE        DM_tVarchar2Array,
  ) organization index
    ( (constraint PK_RESOURCEPROPERTIES_VALUE PRIMARY KEY (NESTED_TABLE_ID)) organization index overflow );
This one fails
  values (1, 'name1', DM_tVarchar2Array('a1', 'a2'));
Question by:imaniuk_atreus
  • 2
LVL 18

Expert Comment

ID: 18005104
LVL 27

Expert Comment

ID: 18007750
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.

Author Comment

ID: 18007798
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.
LVL 27

Accepted Solution

sujith80 earned 125 total points
ID: 18007853
>>"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:

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Export table into csv file in oracle 10 274
Oracle 10g standard edition server with 4 processors 3 69
Oracle collections 15 29
oracle sqlplus query delimiter 8 24
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question