Solved

Need a Bulk Insert Query

Posted on 2008-10-22
16
1,547 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

821 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