Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Nested tables

Posted on 2006-11-23
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 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