Hello,

I represent in my schema the following tables :

Container : contains a column ContainerName (unique) and represents a container
Container_Item : contains a column ContainerItemName (unique) and represents a container item
Container_Candidates: This table contains the link between the "Container" and the "Container_Item" table . Each "Container" can contain any number of "ContainerItems" (even "ContainerItems" that are located in another "Container" )
Elements: contains a column ElementName (unique) that represents an element.
Compounds: This table contains the link between the "ContainerItem" and the "Elements" table . Each "ContainerItem" can contain any number of "Elements" (even "Elements" that are located in another ContainerItem"

When i add a new container i perform the following operations :
1) create an insert in the "Container" table
2) use a procedure to insert a container_item :
* check if the container_item name exists . if exists, obtain it's id and use it in the "Container_Candidates" table
* in case the container_item name does not exist, create it and use the generated id in the "Container_Candidates" table
3) use a procedure to insert an element
* check if the element name exists . if exists, obtain it's id and use it in the "Compounds" table
* in case the element name does not exists, create it and use the generated id in the "Compounds" table

I Execute the upper three operations in a transaction.

This works for smaller structures, however in case i have a container that contains 5.000 items and each item can contain 1.000 unique elements i end up with a transaction of around 5.000.000 operations (1-3) described upper . This is really time consuming, non memory friendly and not user friendly.

Also when i delete the entire structure i have to check which of the inner elements (elements, container_items) are not used by another container/container_item . I perform this using some cursors. Again my code works but it is not very efficient.

Can you give me so ideas how can i achieve an add/update/delete operation of a large structure using the described table structure from above. I would like to have also the benefits of using a transaction.

Thank you.

SQL> create table t1 ( x1 int );

Table created.

SQL>
SQL> create or replace
  2  type numlist is table of number;
  3  /

Type created.

SQL>
SQL> set serverout on
SQL> declare
  2    n numlist := numlist();
  3    s timestamp;
  4  begin
  5    n.extend(1);
  6    n(1) := 10;
  7
  8    s := localtimestamp;
  9    insert into t1
 10    select column_value
 11    from table(n);
 12    dbms_output.put_line(localtimestamp-s);
 13
 14    for i in 2 .. 100 loop
 15      n.extend;
 16      n(i) := i;
 17    end loop;
 18
 19    s := localtimestamp;
 20    insert into t1
 21    select column_value
 22    from table(n);
 23    dbms_output.put_line(localtimestamp-s);
 24
 25    for i in 101 .. 1000 loop
 26      n.extend;
 27      n(i) := i;
 28    end loop;
 29
 30    s := localtimestamp;
 31    insert into t1
 32    select column_value
 33    from table(n);
 34    dbms_output.put_line(localtimestamp-s);
 35
 36  end;
 37  /
+000000000 00:00:00.003000000
+000000000 00:00:00.001000000
+000000000 00:00:00.000000000

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1101

1 row selected.

SQL>