Jump to content
manisht

Perf _ Insert into select Vs ForAll

Recommended Posts

I need to perform db operations where I need to simple inserts data from another query:

 

As of now I am using INSERT INTO SELECT inside apl/sq package.

but some time I am observing for same data set this queries takes more time 

Will For All perform better tan insert into select

Share this post


Link to post
Share on other sites

Hi Manish, and welcome to the forum!

The “insert select into” is native SQL, and there is no need for any PL/SQL:

insert /*+ append */ into
   finance_users
(
   name,
   salary
)
   select name, salary from emp where dept = ‘FINANCE’;

It is basically a SQL subquery, and if you suspect varying response times you need to examine the execution plans to insure that they are the same:

http://dba-oracle.com/googlesearchsite_proc.htm?cx=000522505899594707971%3A4-ldikxixw4&cof=FORID%3A10&ie=UTF-8&q=Historic+sql+plans&sa=Search&siteurl=www.dba-oracle.com%2FOracle_insert_select_tips.htm&ref=www.google.com%2F&ss=12124j11913678j18

You can always use SQL profiles or optimizer plan stability to “freeze” the execution plans:

http://www.dba-oracle.com/t_oracle_sql_profile.htm

Please check and see if your plans are changing.

Some folks use temporary tables in lieu of a insert subquery:

http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm

As to using FORALL, sometimes that can help:

http://www.remote-dba.net/t_op_sql_insert_speed.htm

Please let us know about you execution plans and options!

Good luck!

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×