Bulk Data for Testing: Single loop insert vs. Direct-Load Insert

Dear All,

I have a challenge to multiply data within a database table to a size equal to 1 terabyte. Therefore, the approach that l take is very important to reduce the amount of time that my program runs to reach these data volumes.

HAS ANYBODY GOT ANY EXPERIENCE OF DOING THIS? All indexes will be taken off the table for the bulking excercise, and then put back on afterwards.

There is existing data inside the database, with one primary key. This primary key is alpha-numeric.

I will need to produce this alpha-numeric key for each record to be bulked up.

There are two solutions to this problem:

1. Use PL/SQL to loop a single insert
2. Create the insert statement for adding one record
3. Start Loop
4. Replace the primary key field with a unique number: start at ...AAA for record 1, ...AAB for record 2, ...AAa for record 27, ...ABA for record 53 etc.
5. Execute insert statement
6. Loop back to B n times
7. Use PL/SQL to create SQL insert
8. Create an Oracle sequence (test_seq) starting at 1
9. Create an Oracle function(test_fn) which takes in a number and produces a alphanumeric value which represents that number, e.g. if the primary key was five characters in length then passing in 1 would give AAAAA, and passing in 2 would give AAAAB etc.
10. Start loop
11. Create the insert statement, e.g. insert into table_a select column1, column2, test_fn(test_seq.nextval), ... from table_a
12. Execute the sql statement
13. Loop back to C n times

Moreover, will the test_seq.nextval as used in solution 2 bring back an unique sequence number for new row inserted?

Many thanks for any replies.

Arv' Received on Wed Feb 13 2002 - 11:14:33 CST