Solved

Need a Bulk Insert Query

Posted on 2008-10-22
16
1,546 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

777 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