This discussion is archived
7 Replies Latest reply: Feb 24, 2013 6:19 PM by 969208 RSS

Merge into nested table

969208 Newbie
Currently Being Moderated
hi, all
Can I merge records into nested table
I have a table with 2 colum : col_1, and col_2
col_1: is the id (varchar2)
col_2: is a nested column with table type (sub_col_1 (number), sub_col_2 (number))

for example : I have 2 records the table ( 'a', (1,2)) and ('b', (3,4)))

what I want is merging into this table with values ('a', '4,5')) + ('c', (6,7)) IN a only one statement

What I shoud have after that operation ('a', (1,2,4,5)) + ('b', (3,4)) + ('c', (6,7)).
Can I do that??
CREATE OR REPLACE TYPE TEST_TYPE AS OBJECT(SUB_COL_1 NUMBER, SUB_COL_2 NUMBER) ;

CREATE OR REPLACE TYPE TEST_TYPE_TABLE IS TABLE OF TEST_TYPE;

CREATE TABLE TEST_MERGE_NESTED_TABLE (COL_1 VARCHAR2(1 CHAR), COL_2 TEST_TYPE_TABLE)
    NESTED TABLE COL_2 STORE AS COL_2_NESTED;
    
INSERT INTO TEST_MERGE_NESTED_TABLE VALUES('a',TEST_TYPE_TABLE(TEST_TYPE(1,2) ));
INSERT INTO TEST_MERGE_NESTED_TABLE VALUES('b',TEST_TYPE_TABLE(TEST_TYPE(3,4) ));
Oracle version : 11.2.0.3
Thanks all.

Edited by: 966205 on 20:18 21-02-2013

Edited by: 966205 on 20:42 21-02-2013
  • 1. Re: Merge into nested table
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: Merge into nested table
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    Whenever you post provide your four digit Oracle version (result of SELECT * FROM V$VERSION)
    >
    Can I merge records into nested table
    I have a table with 2 colum : col_1, and col_2
    col_1: is the id (varchar2)
    col_2: is a nested column with table type (sub_col_1 (number), sub_col_2 (number))

    for example : I have 2 records the table ( 'a', (1,2)) and ('b', (3,4)))

    what I want is merging into this table with values ('a', '4,5')) + ('c', (6,7)) IN a only one statement

    What I shoud have after that operation ('a', (1,2,4,5)) + ('b', (3,4)) + ('c', (6,7)).
    Can I do that??
    >
    Yes - but you will need to use the MULTISET operators to combine the nested table data

    See the SQL Language doc for examples of using the MULTISET operators.
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/operators006.htm

    The same doc has examples for MERGE.
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm
  • 3. Re: Merge into nested table
    969208 Newbie
    Currently Being Moderated
    Thanks rp0428, and sorry sb92075 and thanks as well

    This is what i did, and it works
    MERGE INTO TEST_MERGE_NESTED_TABLE DEST
    USING (SELECT 'a' COL_1, CAST(MULTISET (SELECT TEST_TYPE(4,5) FROM DUAL ) AS TEST_TYPE_TABLE) COL_2  FROM DUAL 
           UNION ALL SELECT 'c' COL_1, CAST(MULTISET (SELECT TEST_TYPE(6,7) FROM DUAL ) AS TEST_TYPE_TABLE) COL_2 FROM DUAL 
           ) SRC
    ON(DEST.COL_1 = SRC.COL_1)
    WHEN NOT MATCHED THEN
    INSERT (COL_1, COL_2)
    VALUES (SRC.COL_1, SRC.COL_2)
    WHEN MATCHED THEN
    UPDATE SET COL_2 = DEST.COL_2 MULTISET UNION SRC.COL_2
    but the prolem is that:
    UPDATE SET COL_2 = DEST.COL_2 MULTISET UNION SRC.COL_2
    I have a big data and I have to split it into many pieces of process , and with this above operation, it will recreate the col_2 so It takes resource and time so much, so is it posible the statement will append the new records into the old ones
  • 4. Re: Merge into nested table
    rp0428 Guru
    Currently Being Moderated
    >
    but the prolem is that:
    UPDATE SET COL_2 = DEST.COL_2 MULTISET UNION SRC.COL_2
    I have a big data and I have to split it into many pieces of process , and with this above operation, it will recreate the col_2 so It takes resource and time so much, so is it posible the statement will append the new records into the old ones
    >
    That is what UNION does by default. The nested table is actually stored as a table so Oracle will just do an insert.

    See MULTISET UNION in the doc link I provided
    >
    MULTISET UNION MULTISET UNION takes as arguments two nested tables and returns a nested table whose values are those of the two input nested tables. The two input nested tables must be of the same type, and the returned nested table is of the same type as well.

    •The ALL keyword instructs Oracle to return all elements that are in the two input nested tables, including duplicate values and duplicate NULL occurrences. This is the default.

    •The DISTINCT keyword instructs Oracle to eliminate duplicates from the returned nested table, including duplicates of NULL, if they exist.

    •The element types of the nested tables must be comparable. Refer to "Comparison Conditions" for information on the comparability of nonscalar types.
    >
    And you don't need to do all that casting in the sample code. See this sample code for a cleaner way to do it.
    drop type nt_test;
    
    create type nt_test as table of number;
    
    drop table merge_nt_test cascade constraints;
    
    create table merge_nt_test (id varchar2(10), col_2 nt_test,
        CONSTRAINT    id_pk
                        PRIMARY KEY (id )
    )
    NESTED TABLE col_2 STORE AS col2_2_nt;
    
    insert into merge_nt_test values ('a', nt_test(1,2));
    
    insert into merge_nt_test values ('b', nt_test(3,4));
    
    MERGE INTO merge_nt_test D
       USING (SELECT 'a' id, nt_test(4,5) nt from dual union all select 'c', nt_test(6,7) from dual) s
       ON (D.id = S.id)
       WHEN NOT MATCHED THEN INSERT (D.id, D.col_2)
         VALUES (S.id, S.nt)
       WHEN MATCHED THEN UPDATE SET D.col_2 = 
            d.col_2 multiset UNION s.nt
  • 5. Re: Merge into nested table
    969208 Newbie
    Currently Being Moderated
    Hi rp0428,
    That is what UNION does by default. The nested table is actually stored as a table so Oracle will just do an insert.
    It means that It does the same thing which what I want (appending ?? not recreating that column in physical layer). I'm not sure about what it really does in physical layer,
    Now, I'm using that statement but It's quite slow, do you have any idea to make it better?
    Thank for your reply so much.
  • 6. Re: Merge into nested table
    odie_63 Guru
    Currently Being Moderated
    966205 wrote:
    It means that It does the same thing which what I want (appending ?? not recreating that column in physical layer).
    Actually, no.
    The whole content of the nested table is first deleted (for the given FK), then the result of the MULTISET UNION is inserted again.

    Proof on :
    SQL> alter session set events '10046 trace name context forever, level 12';
    
    Session altered.
    
    SQL> merge into test_merge_nested_table t
      2  using (
      3    select 'a' col_1, TEST_TYPE_TABLE(TEST_TYPE(4,5)) col_2 from dual union all
      4    select 'c' , TEST_TYPE_TABLE(TEST_TYPE(6,7))            from dual
      5  ) v
      6  on ( t.col_1 = v.col_1 )
      7  when matched then update
      8    set t.col_2 = t.col_2 multiset union v.col_2
      9  when not matched then insert (col_1, col_2)
     10   values (v.col_1, v.col_2) ;
    
    2 rows merged.
    
    SQL> alter session set events '10046 trace name context off';
    
    Session altered.
    
    SQL> select * from test_merge_nested_table;
    
    C COL_2(SUB_COL_1, SUB_COL_2)
    - --------------------------------------------------------------------------------
    c TEST_TYPE_TABLE(TEST_TYPE(6, 7))
    a TEST_TYPE_TABLE(TEST_TYPE(1, 2), TEST_TYPE(4, 5))
    b TEST_TYPE_TABLE(TEST_TYPE(3, 4))
    TKPROF output :

    The DELETE statement targets the nested table related to 'a' and delete the one row it contains (1,2).
    The subsequent INSERT is run twice (execute count = 2) and targets both nested table 'a' by inserting 2 rows : the old one (1,2) + the new one (4,5), and nested table 'c' by inserting 1 row (6,7).
    SQL ID: 6bjc2z2t53csn Plan Hash: 132214516
    
    DELETE /*+ REF_CASCADE_CURSOR */ FROM "DEV"."COL_2_NESTED"  
    WHERE
     "NESTED_TABLE_ID" = :1
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          1          3           1
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        2      0.00       0.00          0          1          3           1
    
    Misses in library cache during parse: 0
    Optimizer mode: CHOOSE
    Parsing user id: SYS   (recursive depth: 1)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             0          0          0  DELETE  COL_2_NESTED (cr=1 pr=0 pw=0 time=44 us)
             1          1          1   INDEX RANGE SCAN SYS_FK0000023444N00002$ (cr=1 pr=0 pw=0 time=10 us)(object id 23446)
    
    ********************************************************************************
    
     <snip>
    
    ********************************************************************************
    
    SQL ID: 0fzd5yk23jyas Plan Hash: 0
    
    INSERT /*+ NO_PARTIAL_COMMIT REF_CASCADE_CURSOR */ INTO "DEV"."COL_2_NESTED" 
      ("NESTED_TABLE_ID","SYS_NC_ROWINFO$") 
    VALUES
    (:1, :2)
    
    
    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        2      0.00       0.00          0          0          0           0
    Execute      2      0.00       0.00          0          2         10           3
    Fetch        0      0.00       0.00          0          0          0           0
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        4      0.00       0.00          0          2         10           3
    
    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 50     (recursive depth: 1)
    Number of plan statistics captured: 1
    
    Rows (1st) Rows (avg) Rows (max)  Row Source Operation
    ---------- ---------- ----------  ---------------------------------------------------
             0          0          0  LOAD TABLE CONVENTIONAL  (cr=1 pr=0 pw=0 time=113 us)
    
    ********************************************************************************
  • 7. Re: Merge into nested table
    969208 Newbie
    Currently Being Moderated
    Thanks odie_63 for your detailed explaination,
    I get it now.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points