Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert to different schema - Optimization

Posted on 2011-03-22
3
Medium Priority
?
583 Views
Last Modified: 2012-06-22
I have a simple insert statement with in my Stored Procedure:
Insert into Schema2.T1
Select * from Schema1.T1

There are 3 million rows and Toad shows cost as 17939.
How do I optimize this and quicken this load?

0
Comment
Question by:codemonkey2480
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35193278
Use the APPEND hint to insert above the high water mark:

Insert /*+ APPEND */ into Schema2.T1
Select * from Schema1.T1



or

PL/SQL cursor loop with a commit every X rows to reduce undo?

declare
   counter number := 1;
begin
for i in (select * from Schema1.T1) loop
    insert into Schema2.T1(col1, col2) values(i.col1, i.col2);
    if mod(counter,1000)=0 then
         commit;
    end if;
    counter := counter+1;
end loop;
end;
/
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 600 total points
ID: 35194074
ignore COST

using the append hint is the fastest way to do your insert.

I wouldn't try using a loop, that simply adds extra steps and since it's an insert, the undo should be minimal.

If you have triggers, constraints or indexes on the target table, those will all make the insert slower.

You might not be able to disable the triggers, but possibly you could drop the indexes and disable the constraints.
Then recreate the indexes when done and reenable the constraints.

If you follow those, make sure nobody else is going to touch that table while you are inserting or their access will be slow and could create corrupt data while the constraints are off

0
 

Assisted Solution

by:josiethejewel
josiethejewel earned 400 total points
ID: 35197089
I would use the append hint and disable your indexes without dropping them. Disabling the indexes set them unusable so you will have to set your session to ignore unusable indexes. Once your data is inserted you can rebuild your indexes and everything will work fine. Every time oracle insert a row without the index disabled it has to rewrite the index. I did this for our database and took our loads( about 4 Million records) from 24 hours to about 2 hours.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

722 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