Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kunal.

Asked: May 24, 2016 - 3:42 am UTC

Last updated: May 25, 2016 - 3:38 am UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I really appreciate your help/insight that you have been providing over the years to Oracle professionals in distress.
Ok here is my problem. I have a database 'A' which is on 11g which is my source database and a database 'B' on 12c which is my target database, both running on Exadata. I have to convert about 15 TB of data from A to B in a day over DB links(cannot use import export as structure is diff). To achieve this I have created multiple views which point to specific partitions in source table(typically 256 hash partitions in a table).Now i run multiple threads(32) which select from one view in source database and insert into specific target table partition. I have to explicitly select the target partition as I am using append hint, if I do not select partition append hint would lock the entire table and would stop me from multithreading. So i just lock individual partition and Insert append the data into target table. With this strategy im able to insert almost 5 billion rows in 30 mins. Now the problem I run into is sometime the target partitions are system generated (SYS_****), So the source partition and the target partitions are not same, in this kind of scenario how should I select the target partition and at the same time multithread

Thanks,
Kunal

and Connor said...

Go with partition_position instead

SQL> create table t1
  2   ( x int, y int )
  3   partition by hash(x) partitions 8;

Table created.

SQL>
SQL> create table t2
  2   ( a int, b int )
  3   partition by hash(a) partitions 8;

Table created.

SQL>
SQL> insert into t1
  2  select object_id, rownum
  3  from all_objects;

95367 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> col t1_name format a30
SQL> col t2_name format a30
SQL> select t1.partition_name t1_name, t2.partition_name t2_name
  2  from   user_tab_partitions t1,
  3         user_tab_partitions t2
  4  where t1.table_name = 'T1'
  5  and   t2.table_name = 'T2'
  6  and   t1.partition_position = t2.partition_position;

T1_NAME                        T2_NAME
------------------------------ ------------------------------
SYS_P13484                     SYS_P13492
SYS_P13485                     SYS_P13493
SYS_P13486                     SYS_P13494
SYS_P13487                     SYS_P13495
SYS_P13488                     SYS_P13496
SYS_P13489                     SYS_P13497
SYS_P13490                     SYS_P13498
SYS_P13491                     SYS_P13499

8 rows selected.

SQL>
SQL> begin
  2  for i in (
  3    select t1.partition_name t1_name, t2.partition_name t2_name
  4    from   user_tab_partitions t1,
  5           user_tab_partitions t2
  6    where t1.table_name = 'T1'
  7    and   t2.table_name = 'T2'
  8    and   t1.partition_position = t2.partition_position
  9  )
 10  loop
 11    execute immediate 'insert into t2 partition ( '||i.t2_name||') select * from t1 partition ('||i.t1_name||')';
 12  end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Parallel DML

Rajeshwaran, Jeyabal, May 24, 2016 - 6:37 am UTC

In addition to direct path insert, any option to use Parallel DML for this huge set (since parallel dml works by direct path default) ?
Connor McDonald
May 24, 2016 - 6:44 am UTC

Agreed - certainly worth considering.

Significance of partition_position in two databases

A reader, May 24, 2016 - 12:18 pm UTC

Thanks a lot for your response. I had a doubt, since we are dealing with data migration involving two diff databases, would comparing the partition_position between two partition for a table,across databases, give me correct result?
Connor McDonald
May 25, 2016 - 3:16 am UTC

You should be ok, and it's trivial to validate. For example, see what happens when I repeat the demo and deliberately match on *different* partition position

SQL> create table t1
  2   ( x int, y int )
  3   partition by hash(x) partitions 8;

Table created.

SQL>
SQL> create table t2
  2   ( a int, b int )
  3   partition by hash(a) partitions 8;

Table created.

SQL>
SQL> insert into t1
  2  select object_id, rownum
  3  from all_objects;

95352 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin
  2  for i in (
  3    select t1.partition_name t1_name, t2.partition_name t2_name
  4    from   user_tab_partitions t1,
  5           user_tab_partitions t2
  6    where t1.table_name = 'T1'
  7    and   t2.table_name = 'T2'
  8    and   t1.partition_position = t2.partition_position+1  <<=====
  9  )
 10  loop
 11    execute immediate 'insert into t2 partition ( '||i.t2_name||') select * from t1 partition ('||i.t1_name||')';
 12  end loop;
 13  end;
 14  /
begin
*
ERROR at line 1:
ORA-14401: inserted partition key is outside specified partition
ORA-06512: at line 11


Significance of partition_position in two databases

Rajeshwaran, Jeyabal, May 24, 2016 - 4:40 pm UTC

I had a doubt, since we are dealing with data migration involving two diff databases, would comparing the partition_position between two partition for a table,across databases, give me correct result?

and you said this too..

..which point to specific partitions in source table(typically 256 hash partitions in a table)...

so you have two different database, but the table structure is same in both database and so the number of partitions.

so across the database, partition name could be different (in case of internally generated names) but partition_position would be the same.


rajesh@ORA11G> drop table t purge;

Table dropped.

rajesh@ORA11G> create table t
  2  partition by hash(user_id)
  3  partitions 2
  4  as
  5  select * from all_users ;

Table created.

rajesh@ORA11G>
rajesh@ORA11G> column partition_name format a10
rajesh@ORA11G> select partition_name,partition_position
  2  from user_tab_partitions
  3  where table_name ='T';

PARTITION_ PARTITION_POSITION
---------- ------------------
SYS_P11763                  1
SYS_P11764                  2

2 rows selected.

rajesh@ORA11G>


from 12c

rajesh@ORA12C> drop table t purge;

Table dropped.

rajesh@ORA12C> create table t
  2  partition by hash(user_id)
  3  partitions 2
  4  as
  5  select * from all_users ;

Table created.

rajesh@ORA12C>
rajesh@ORA12C> column partition_name format a10
rajesh@ORA12C> select partition_name,partition_position
  2  from user_tab_partitions
  3  where table_name ='T';

PARTITION_ PARTITION_POSITION
---------- ------------------
SYS_P13729                  1
SYS_P13730                  2

2 rows selected.

rajesh@ORA12C>


you could see here, partition names are different (since internally generated) but partition position are same.

Hence AskTom Team was suggesting you to go with partition_position for this case. Hope this helps.

Thanks All

Kunal Gupta, May 25, 2016 - 3:22 am UTC

Thanks everyone for helping me. Really appreciate it

-Kunal
Connor McDonald
May 25, 2016 - 3:38 am UTC

Glad it helped.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.