This discussion is archived
11 Replies Latest reply: Jan 7, 2013 7:06 PM by rp0428 RSS

Merge tables

9135487 Newbie
Currently Being Moderated
Hi,

I have two partitioned tables.
Table 1 has 15 partitions and each partition has around 3 million records.
Table 2 has 20 partitions and each partition has around 3 million records.
I want to merge these two tables in to one table so that the new table has all the partitions with respective tablespaces.
Any suggestions and what would be the optimal way to do this?

Thanks in advance.
  • 1. Re: Merge tables
    KeithJamieson Expert
    Currently Being Moderated
    Do the tables have the same structure?

    Are the tables partitioned using the same method.(eg both range partitioning)

    Do the partitions overlap?


    Simple solution would be:

    1: create a new table with all required partitions
    2: insert into new_table select /* +append */ from table1;
    3: insert into new_table select /* +append */ from table2;

    Drop table1;
    drop table2;
  • 2. Re: Merge tables
    9135487 Newbie
    Currently Being Moderated
    Yes both tables have same structure. Tables are range-hash partitioned. Insert is going to take a lot of time what do you say?
  • 3. Re: Merge tables
    KeithJamieson Expert
    Currently Being Moderated
    9135487 wrote:
    Yes both tables have same structure. Tables are range-hash partitioned. Insert is going to take a lot of time what do you say?
    Why was the second table created then?

    Insert as illustrated above is the quickest way of performing the insert.

    Any other method will take longer.

    If you are worried about it taking too long, then set up different sqlplus sessions and insert into a table only selecting data from each partition, but I don't think its necessary.

    I would expect the job to be completed in 2/3 hours.

    Edited by: Keith Jamieson on Jan 3, 2013 2:38 PM
  • 4. Re: Merge tables
    9135487 Newbie
    Currently Being Moderated
    I am asking it because they are the dummy tables which I have to to merge for testing purpose. Actual tables have like 14 billions records in each table. So merging them using insert would be taking too long, I would appreciate if you could share some other way keeping this criteria in mind.

    Thanks alot.
  • 5. Re: Merge tables
    KeithJamieson Expert
    Currently Being Moderated
    There is no other way that will be faster.
    Also you are not talking about a merge. You are talking about a consolidation.

    Making one table out of two separate tables.

    As I said, just do it by partition then (using several sessions running in parallel).

    Just insert the latest records first.
    Those are the ones most likely to be accessed.

    eg partition for year 2012
    --Dec 2012
    
    insert into new_table
    select /*+append*/ * 
    from table1
    where date_inserted >= to_date('01-12-2012','DD-MM-YYYY')
    and    date_inserted < to_date('01-01-2013','DD-MM-YYYY');
    November -- in a different session.
    --table 1 Nov 2012
    
    insert into new_table
    select /*+append*/ * 
    from table1
    where date_inserted >= to_date('01-11-2012','DD-MM-YYYY')
    and    date_inserted < to_date('01-12-2012','DD-MM-YYYY');
    November -- in a different session.
    --table2 Nov 2012
    
    insert into new_table
    select /*+append*/ * 
    from table2
    where date_inserted >= to_date('01-11-2012','DD-MM-YYYY')
    and    date_inserted < to_date('01-12-2012','DD-MM-YYYY');
    Note: Im assuming that you have a column called date_inserted of type date
    and this is the column that your partitioning is based on.

    You will more than likely have a different column name , but the same principle should apply.

    Just repeat as required.
  • 6. Re: Merge tables
    9135487 Newbie
    Currently Being Moderated
    Thanks a lot I appreciate it. I will try this approach and would share how it goes.

    Edited by: 9135487 on Jan 3, 2013 7:10 AM
  • 7. Re: Merge tables
    9135487 Newbie
    Currently Being Moderated
    Hi everyone,

    Can we have some alternate way for the above criteria. Any suggestions?

    Thanks
  • 8. Re: Merge tables
    rp0428 Guru
    Currently Being Moderated
    >
    Can we have some alternate way for the above criteria. Any suggestions?
    >
    Have you considered using EXCHANGE PARTITION?

    1. Create one work table with the proper structure and indexes. These must match exactly.

    2. Add the appropriate new partitions to one of the existing tables (e.g. table1). Pick the table that already has the most partitions so there are fewer exchanges to be done.

    3. Exchange one subpartition of real table2 with the work table.

    4. Exchange the work table with the new empty partition of table1 that the data belongs to.

    5. Truncate the work table

    6. repeat steps 3, 4 and 5 for each subpartition of table2 that needs to be moved.

    See the SQL Language doc
    http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_3001.htm
    >
    exchange_partition_subpart
    Use the EXCHANGE PARTITION or EXCHANGE SUBPARTITION clause to exchange the data and index segments of:

    •One nonpartitioned table with:

    ◦one range, list, or hash partition

    ◦one range, list, or hash subpartition

    •One range-partitioned table with the range subpartitions of a range-range or list-range composite-partitioned table partition

    •One hash-partitioned table with the hash subpartitions of a range-hash or list-hash composite-partitioned table partition

    •One list-partitioned table with the list subpartitions of a range-list or hash-list composite-partitioned table partition

    In all cases, the structure of the table and the partition or subpartition being exchanged, including their partitioning keys, must be identical. In the case of list partitions and subpartitions, the corresponding value lists must also match.
    >
    The relevant bullet above for your use case is this one
    >
    •One hash-partitioned table with the hash subpartitions of a range-hash or list-hash composite-partitioned table partition
  • 9. Re: Merge tables
    SalmanQureshi Expert
    Currently Being Moderated
    Hi,
    How about using a MERGE statement to merge the data?

    http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php

    Salman
  • 10. Re: Merge tables
    9135487 Newbie
    Currently Being Moderated
    Can I exchange partitions between two partitioned tables?
    In my case I Table1 has all the partitions created but not every partition has data in it. Table2 has partitions which have data in them.

    e.g

    create table table1 ( id(number))
    partition by range(id)
    subpartition by hash(id) subpartitions 10
    ( partition table1_p1 values less than('01-01-2012')
    .
    .
    .
    .
    partition table1_p1 values less than('09-01-2012')
    partition table1_p10 values less than(maxvalue)
    );


    create table table2 ( id(number))
    partition by range(id)
    subpartition by hash(id) subpartitions 10
    ( partition table2_p1 values less than('06-01-2012')
    .
    .
    .
    .partition table2_p5 values less than(maxvalue)
    );

    In the above cases table1 has same structure and all the partitions created which means i will be exchanging data in table1( my understanding )

    what do i do in this case?
  • 11. Re: Merge tables
    rp0428 Guru
    Currently Being Moderated
    >
    Can I exchange partitions between two partitioned tables?
    >
    Yes - but not the way you are trying. You have to RTFM! I pointed out the exact section of the document that applies to your use case. You have to do the exchange the way the documentation says: via a hash-partitioned work table.

    Here is a solution that people may want to add to their toolkit.

    This example code effectively moves the 4 partitions of the EMP_PART table to the same, but empty, partitions of the EMP_PART_NEW table using the EMP_PART_HASH work table.
    -- EMP_PART table has 4 range partitions with data in each partition
    -- this data will be moved (via partition exchange) to the EMP_PART_NEW table
    DROP TABLE EMP_PART;
    
    CREATE TABLE emp_part (empno number(4), ename varchar2(10),
     deptno number(2), created_date DATE default sysdate)
      partition by range (created_date)
         SUBPARTITION BY HASH(deptno) subpartitions 4
    ( partition p1 values less than (to_date('01-01-2013', 'mm-dd-yyyy')),
     partition p2 values less than (to_date('01-02-2013', 'mm-dd-yyyy')),
     partition p3 values less than (to_date('01-03-2013', 'mm-dd-yyyy')),
     partition p4 values less than (to_date('01-04-2013', 'mm-dd-yyyy')));
     
    INSERT INTO EMP_PART VALUES (1, 'EMP1', 1, SYSDATE - 7);
    
    INSERT INTO EMP_PART VALUES (1, 'EMP1', 1, SYSDATE - 6);
    
    INSERT INTO EMP_PART VALUES (1, 'EMP1', 1, SYSDATE - 5);
    
    INSERT INTO EMP_PART VALUES (1, 'EMP1', 1, SYSDATE - 4);
    
    -- EMP_PART_NEW table has 7 range partitions - only partitions p5, p6 and p7 have data
    -- Partitions p1, p2, p3 and p4 will receive data from the emp_part table via the emp_part_hash
    -- work table
    DROP TABLE EMP_PART_NEW;
    
    CREATE TABLE emp_part_new (empno number(4), ename varchar2(10),
     deptno number(2), created_date DATE default sysdate)
      partition by range (created_date)
         SUBPARTITION BY HASH(deptno) subpartitions 4
    ( partition p1 values less than (to_date('01-01-2013', 'mm-dd-yyyy')),
     partition p2 values less than (to_date('01-02-2013', 'mm-dd-yyyy')),
     partition p3 values less than (to_date('01-03-2013', 'mm-dd-yyyy')),
     partition p4 values less than (to_date('01-04-2013', 'mm-dd-yyyy')),
     partition p5 values less than (to_date('01-05-2013', 'mm-dd-yyyy')),
     partition p6 values less than (to_date('01-06-2013', 'mm-dd-yyyy')),
     partition p7 values less than (to_date('01-07-2013', 'mm-dd-yyyy')));
    
    INSERT INTO EMP_PART_NEW VALUES (1, 'EMP1', 1, SYSDATE - 3);
    
    INSERT INTO EMP_PART_NEW VALUES (1, 'EMP1', 1, SYSDATE - 2);
    
    INSERT INTO EMP_PART_NEW VALUES (1, 'EMP1', 1, SYSDATE - 1);
    
    -- work table for the exchange
    DROP TABLE EMP_PART_HASH;
    
    CREATE TABLE emp_part_hash (empno number(4), ename varchar2(10),
     deptno number(2), created_date DATE default sysdate)
      partition by hash (deptno) partitions 4;
    
    SELECT * FROM EMP_PART PARTITION (P2);
    
    EMPNO     ENAME     DEPTNO     CREATED_DATE
    1     EMP1     1     1/1/2013 6:52:54 PM
    
    select * from emp_part order by created_date;
    
    EMPNO     ENAME     DEPTNO     CREATED_DATE
    1     EMP1     1     12/31/2012 6:52:52 PM
    1     EMP1     1     1/1/2013 6:52:54 PM
    1     EMP1     1     1/2/2013 6:52:56 PM
    1     EMP1     1     1/3/2013 6:52:58 PM
    
    select * from emp_part_new partition (p6);
    
    EMPNO     ENAME     DEPTNO     CREATED_DATE
    1     EMP1     1     1/5/2013 6:53:21 PM
    
    select * from emp_part_new order by created_date;
    
    EMPNO     ENAME     DEPTNO     CREATED_DATE
    1     EMP1     1     1/4/2013 6:53:19 PM
    1     EMP1     1     1/5/2013 6:53:21 PM
    1     EMP1     1     1/6/2013 6:53:23 PM
    
    -- exchange each partition from emp_part with the work table
    -- and then exchange the work table with the same partition of the emp_part_new table
    alter table emp_part exchange partition p1 
    with table emp_part_hash;
    
    alter table emp_part_new exchange partition p1
    with table emp_part_hash;
    
    alter table emp_part exchange partition p2 
    with table emp_part_hash;
    
    alter table emp_part_new exchange partition p2
    with table emp_part_hash;
    
    alter table emp_part exchange partition p3 
    with table emp_part_hash;
    
    alter table emp_part_new exchange partition p3
    with table emp_part_hash;
    
    alter table emp_part exchange partition p4 
    with table emp_part_hash;
    
    alter table emp_part_new exchange partition p4
    with table emp_part_hash;
    
    select * from emp_part order by created_date;
    
    -- no data
    
    select * from emp_part_new order by created_date;
    
    EMPNO     ENAME     DEPTNO     CREATED_DATE
    1     EMP1     1     12/31/2012 6:52:52 PM
    1     EMP1     1     1/1/2013 6:52:54 PM
    1     EMP1     1     1/2/2013 6:52:56 PM
    1     EMP1     1     1/3/2013 6:52:58 PM
    1     EMP1     1     1/4/2013 6:53:19 PM
    1     EMP1     1     1/5/2013 6:53:21 PM
    1     EMP1     1     1/6/2013 6:53:23 PM
    Note that:

    1. All three tables have the same structure
    2. All three tables have the same number of HASH partitions. For the two real tables they are HASH subpartitions
    3. The work table has ONLY hash partitions: there are no range partitions.
    4. All of the exchanges are merely data dictionary updates and complete in a fraction of a second no matter how much data there is in any of the actual partitions.

Legend

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