SQL Performance (MOSC)

MOSC Banner

Which is better (insert select) or (loop insert)

edited Nov 29, 2022 8:50AM in SQL Performance (MOSC) 4 commentsAnswered ✓

Hi All

Kindly, I have one view reading from different databases using DB links,

I need to build a procedure to move the data from that view to a table 2 times per day (around 700K records for each run),


Which is better from the performance point of view: or there is another (C) option which better from both of the following 2 options?

(A) insert as select all records from the view

Insert into Table1

select v1.*,null,null,null from View1 v1

join Table2 t2

on v1.id = t2.id;

commit;

(B) we have a table containing the parent id, to use in a loop and insert from 10 to 20 records with each loop and commit every 10K insert transactions

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center