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?
jl66Asked:
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.

jl66Author Commented:
If insert one record at a time, that is very slow. I heard the bulk loader. Is it practical?
0
sdstuberCommented:
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?
0
sdstuberCommented:
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
     FROM DUAL
     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.
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jl66Author 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"?

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

I'm not sure exactly what you're describing
0
jl66Author 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?  


0
sdstuberCommented:
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
0
sdstuberCommented:
alternatively you could try a multi-table insert
maybe something like this...
INSERT ALL
  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
     FROM DUAL
     CONNECT BY LEVEL <= 1000000

Open in new window

0
jl66Author Commented:
Great. Will test the logic with our example.
0
jl66Author 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 ;

  COUNT(1)
----------
         0
How to deal with this?
0
sdstuberCommented:
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
0
jl66Author Commented:
Very helpful.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.