How to make the pl/sql insert fast?

1) have the Oracle package dbms_random generate the data (strings and numbers).
2) want to populate the tables with the random data.
3) if using normal insert, it is possible, but it is very slow, esp. for over 10 million record tables.Under the above conditions, do we have any methods available to make it really fast? How to implement it if any?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jl66ConsultantAuthor Commented:
If insert one record at a time, that is very slow. I heard the bulk loader. Is it practical?
calling dbms_random will add overhead.
but you should be able to do all of the inserts in a single sql statement.

how random do you want your data to be?  can you use a sequence with an increment greater than 1 so your data isn't sequential?
also, how fast do you need?  I tried this...

insert into your_table(number_column, string_column)
   SELECT ROUND(DBMS_RANDOM.VALUE(1, 1000)) num,          
            DBMS_RANDOM.string('x', 20) str
     CONNECT BY LEVEL <= 1000000

and it ran in less than a minute to generate a million rows, and this was just my laptop, not a server class machine.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jl66ConsultantAuthor Commented:
Thanks a lot. This is a good example. I tested it. Super fast.
Now get near real situation:
1) have random number/string cross 3 tables in two databases (one table via db link) to maintain the integrity of the tree tables.
2) for the tables have other columns with business rules. For example,
table T1 has the two generated random number/string plus the 3rd column taking value 1,2, or 3

Is it possible to use hte technique like "CONNECT BY LEVEL <= 1000000"?

can you give an example? say 5 rows of each of the three tables

I'm not sure exactly what you're describing
jl66ConsultantAuthor Commented:
Sorry. The situation is like that:
table T1 (ID number, Name varchar2(20), Created  timestamp)
table T2( ID number, Name varchar2(20),  Indicator Number(3)) @DBLINK ( via DB link)
table T3(ID number, location varchar2(50) )

1) Need the generated random IDs same across the 3 tables.
2) Need  the generated random Names same across T1 and T2.
3) Indicator takes different values 1 to 10 and location takes 5 diff. values. The order don't matter.

Under the conditions, can the super fast method be used to populate  the 3 tables, say 5 million records each?  

use the method above to populate one of the tables (t1)
then populate the other two

with something of the form  insert into t2@dblink(id,name) (select id,name, 123 from t1)

change "123" to another dbms_random call, or a lookup query or function or whatever is appropriate to populate that.

same idea for t3
alternatively you could try a multi-table insert
maybe something like this...
  INTO t1(id, name, created)
VALUES (id, name, created)
  INTO t2(id,name,indicator)
VALUES (id,name,indicator)
  INTO t3(id,location)
VALUES (id,location)
 SELECT ROUND(DBMS_RANDOM.VALUE(1, 1000)) id,           
            DBMS_RANDOM.string('x', 20) name,
            systimestamp created,
            round(dbms_random.value(1,10)) indicator,
            round(dbms_random.value(1,5)) location
     CONNECT BY LEVEL <= 1000000

Open in new window

jl66ConsultantAuthor Commented:
Great. Will test the logic with our example.
jl66ConsultantAuthor Commented:
As mentioned, one of tables is remote table via DB link. It does not seem this kind of operation is allowed here.
  INTO t2@DBLINK (id,name,indicator)
ERROR at line 4:
ORA-02021: DDL operations are not allowed on a remote database

But I made sure that the db link works before I did the above.
SQL> select count(1) from t2@DBLINK ;

How to deal with this?
I hadn't tried insert all for a remote table.  So it might not be supported, sorry.  So, you may want to use it for the 2 local tables and then use the other suggestion to handle the remote db.

that is,  populate the 2 local tables first, then do the remote with something like

insert into t2@dblink select ... from t1
jl66ConsultantAuthor Commented:
Very helpful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.