7 Replies Latest reply: Feb 24, 2013 8:19 PM by 969208 RSS

    Merge into nested table

    969208
      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
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Merge into nested table
            rp0428
            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
              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
                >
                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
                  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
                    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
                      Thanks odie_63 for your detailed explaination,
                      I get it now.