Duplicate data in a table given table name

Question

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.

Answer 1

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.

Answer 2

Take a look here - point 2 here (recipes for disaster),

2. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.

Jonathan Lewis is the man for Oracle and indexes.

If I were you, I would do something like (works in MySQL and PostgreSQL, don't have Oracle up and running at the moment).

mysql> 
mysql> 
mysql> create table billy (fred INT PRIMARY KEY AUTO_INCREMENT, mary INT);
Query OK, 0 rows affected (0.30 sec)

mysql> INSERT INTO billy (mary) VALUES( 3);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO billy (mary) VALUES( 4);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO billy (mary) VALUES( 5);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM billy;
+------+------+
| fred | mary |
+------+------+
|    1 |    3 |
|    2 |    4 |
|    3 |    5 |
+------+------+
3 rows in set (0.00 sec)

mysql> INSERT INTO billy (mary) SELECT mary FROM billy;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from billy;
+------+------+
| fred | mary |
+------+------+
|    1 |    3 |
|    2 |    4 |
|    3 |    5 |
|    4 |    3 |
|    5 |    4 |
|    6 |    5 |
+------+------+
6 rows in set (0.00 sec)

mysql> 

So, it's possible to do the first part without the use of CTEs