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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now