Oracle – How to Handle Duplicate Data in a Table

oracleoracle-12cplsql

For a performance testing application we need to have lot more data in our tables than what we have now. How do I duplicate the data in a table.

In the process of duplicating I also need to set new values for primary key column. I'm thinking getting the max value of primary key column and adding a incrementing number to it will solve the primary key issue.

Is it possible to do this in PL/SQL.

— more information

This table have over 200 columns and 3 unique keys (there are no table level constants), all the operations are happening through views and oracle packages, these packages are responsible for keeping the primary key combination unique. In the duplication process If I could add a incrementing value (starting with max+1 of the column) to a single column I can keep the uniqueness of the key combination.

Best Answer

I like to use rownum. Using a CTE to get the current maximum PK value, you can easily double-up your data.

Example below.

Test table:

create table yourperftest
( pk number,
  col1 number,
  col2 number,
  col3 number
);

Test data:

insert into yourperftest values (1,1,1,1);
insert into yourperftest values (2,2,2,2);
insert into yourperftest values (3,3,3,3);
insert into yourperftest values (4,4,4,4);
insert into yourperftest values (5,5,5,5);

Query to "double up" the table: 

insert into yourperftest
with maxvalue as (
  select max(pk) as maxvalue
  from yourperftest
)
select rownum+maxvalue, col1, col2, col3
from yourperftest,maxvalue;

I'd probably use an /*+APPEND*/ hint too, plus make sure you gather stats on the table afterwards.

Having said that, stats play a big part in testing - you want to make sure that your data/performance test environment is as close to production as possible. Going forward, the stats are the key to this. Stats that are different between test and production will cause different query plans to be picked by the optimiser, which may make your performance testing invalid when production is going tits-up.

EDIT:

Since you ask about doing this for more than 1 column, here's another example:

 insert into yourperftest
with maxvalue as (
  select max(pk) as maxvalue
  from yourperftest
), col1tab as (
   select max(col1) as col1maxvalue
   from yourperftest
)
select rownum+maxvalue, col1maxvalue+rownum, col2, col3
from yourperftest,maxvalue,col1tab ;

CTEs are brilliant.

Related Question