Skip to Main Content
  • Questions

  • Huge data insert. From view to a table. Performance concerns

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aman.

Asked: May 16, 2020 - 2:13 pm UTC

Last updated: May 18, 2020 - 4:32 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,
I need to insert data from view having (average of 17 million records) to a table with few indexes.

The flow is something like this.

for i in (select something from tableA)
loop
view_sql = 'select xxx from tabl... where ...';

if () then
view_sql = view_sql || ' and ...' ;
else
view_sql = view_sql || ' and .....' ;
end if;

execute immediate 'insert into table ' || view_sql ; -- just need answer for this step. How to make this faster?
end loop;

and Connor said...

One (or both) of two things will cause your slow down:

1) the insert
2) the query

You can test the query is isolation by just output the "view_sql" and running some samples in SQL Plus.

A good technique is to use the gather_plan_statistics hint to assess where the slow down is. A example of that here

https://asktom.oracle.com/pls/asktom/asktom.search?tag=what-is-efficient-way-to-paginate-on-the-large-table

If the query is fast and the insert is slow, then throw a trace on via dbms_monitor.session_trace_enable (waits=>true) or look at the session wait statistics via v$session_event to see where the insert is being hampered. Common causes are

- triggers on the table
- lots of indexes on the table


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.