0

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.

  • Do you have a sequence for those primary keys? – a_horse_with_no_name Mar 2 '15 at 11:31
  • 3 primary keys? Seriously? Since that's impossible, we'll assume you meant 3 unique keys. O.o I'm also hoping you're not really enforcing PKs/uniqueness via packages .. and not via the unique constraints themselves. :( – Ditto Mar 2 '15 at 13:46
  • @Ditto sorry not primary, emulated unique key via package access. – Dhananjaya Mar 2 '15 at 13:57
  • 2
    @Dhananjaya Yeah, even then, you shouldn't enforce it via package. You should setup the unique CONSTRAINTs .. and let Oracle handle it. Doing it yourself is guaranteed to fail (think of multiple sessions running you routine at the same time .. it will fail - and I don't even need to see the code ;) Only way to prevent failure: LOCKs and giving up any form of scalability by creating unnecessary serialization) Please re-consider your design ;) – Ditto Mar 2 '15 at 14:01
  • yes yes I'd love to :) but this is a 20+ year old system, and and this table seems to be one of the oldest table in the system. – Dhananjaya Mar 2 '15 at 14:06
3

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.

  • thanks for the idea, CTE idea seems perfect for the new value for the columns – Dhananjaya Mar 2 '15 at 14:11
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.