Solved

Need a Bulk Insert Query

Posted on 2008-10-22
16
1,543 Views
Last Modified: 2013-12-07
I need to insert data from one table into another which have same columns. How do I do that with a Bulk Insert? Please suggest.
temp_table (102 columns) into prov_db_table (102 columns).
0
Comment
Question by:srikanthradix
  • 9
  • 4
  • 2
  • +1
16 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22782288
insert into prov_db_table
select * from temp_table

(assuming the columns are in the same order)
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22782411


if possible,  you can use  the below statement to create the  temp table which will have the same schema as prov_db_table,

create table temp_table as select * from prov_db_table where 1 = 2;

if the tables already exists, and two tables have the same schema, use the one TerryAtOpus provided,
otherwise, you have to list all columns
0
 

Author Comment

by:srikanthradix
ID: 22782519
I mean, the tables already exist.

I don't need the insert  query like
insert into prov_db_table select * from temp_table.

But I need a BULK COLLECT or something, which is faster than ordinary insert query. How can i do that?

Please suggest one that is faster than traditional insert query.
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 200 total points
ID: 22782544
Hmm, ok. When I've done VERY large insert queries, I've increased the performance by turning off the indexes and triggers on the table being inserted into. In an informix database, I've also turned off transaction logging on the database instance.

I think that's about the limit of my knowledge on any alternatives. How many rows do you have to insert?
0
 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 300 total points
ID: 22782701
TerryAtOpus is right, you can import the data and then create all indexes and triggers

you may commit the transaction for a limit # of records to keep transaction size low

and you can use the append hint as well

insert /*+ append */ into prov_db_table
select * from temp_table
0
 

Author Comment

by:srikanthradix
ID: 22783073
@TerryAtOpus
I have to insert 3 million rows and there are 102 columns.
@jamesqu
Can you please explain how can i do the transaction commit for a limit # of records to keep the transaction size low?
Can you please explain me how to use the append hint?
0
 

Author Comment

by:srikanthradix
ID: 22783113
@jamesqu
I have read about append hint and it says
"By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table". What does it mean?
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22783814
If you are looking for the fastest solution:
insert /*+ APPEND */  is the way to go.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 300 total points
ID: 22786359
commit for a limit # of records to keep the transaction size low?

try to find out some column(s) to subset the data from temp_table,say, the table has a record_no which is unique
(create an index on it)

do

insert /*+ append */ into prov_db_table
select * from temp_table where record_no <=100000;
commit;

insert /*+ append */ into prov_db_table
select * from temp_table where record_no <=200000;
commit;
...
0
 

Author Comment

by:srikanthradix
ID: 22787801
How about doing this?

alter index .... unusable;
alter session set skip_unusable_indexes=true;
insert ....
alter index ... rebuild.

Will this affect anything? Is this safe? Even if I can do this, Is this best practice to do so?
0
 

Author Comment

by:srikanthradix
ID: 22787835
And also in my Production DB, there are 5 billion rows, Does rebuilding indexes affect performance?
0
 

Author Comment

by:srikanthradix
ID: 22787852
or /* +APPEND */ for that matter. Does it affect any performance?
0
 

Author Comment

by:srikanthradix
ID: 22789180
How about using BULK COLLECT WITH LIMIT and FORALL like this? Will this be faster?

DECLARE

CURSOR temp_cur
IS
SELECT * FROM temp_table;

TYPE data_temp
      IS TABLE OF temp_cur%ROWTYPE;

bulk_data data_temp;

...
..
.
BEGIN
   OPEN temp_cur;
    LOOP
        FETCH temp_cur BULK COLLECT INTO bulk_data
                   FROM temp_table LIMIT 1000;
                   
        EXIT WHEN
      bulk_data.COUNT = 0;

        FORALL i IN 1..bulk_data.COUNT
             INSERT INTO my_table VALUES bulk_data(i);
       COMMIT;

    END LOOP;
   CLOSE temp_cur;
     
   ...
END;



0
 

Author Closing Comment

by:srikanthradix
ID: 31509458
/*+ APPEND */ hint seems to be better bet. Can you please explain me What does an /*+ APPEND */ hint tells the Oracle and how does it differ from ordinary insert?
0
 
LVL 9

Expert Comment

by:jamesgu
ID: 22792146
0
 

Author Comment

by:srikanthradix
ID: 22792870
Thanks, that explains a lot.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now