Home » SQL & PL/SQL » SQL & PL/SQL » Inserting %RowType objects "faster" (11g)
Inserting %RowType objects "faster" [message #675846] Tue, 23 April 2019 13:55 Go to next message
Messages: 1
Registered: April 2019
Junior Member
I've inherited some fun stuff.

There's a stored-procedure that generates, in memory, a collection of %RowType objects. ~200,000 of them. This stored-procedure is run fairly often during an application's lifecycle.

Generating these objects only takes about 8 seconds. However, replacing the persisted records (delete xxx where xxx .... forall xxx insert into xxx ...) takes several minutes --- ~5 minutes.

I'm tasked with making this persistence portion faster.

I've checked out FK's and Indexes and such ... and nothing appears to be out-of-sorts. I messed around with the few FK's and Indexes that are there anyways, just to see what might happen, and didn't get anything close to the results we're looking for.

Would anyone have any ideas? Any pointers or places/things to look for or try to make this persist faster?
Re: Inserting %RowType objects "faster" [message #675847 is a reply to message #675846] Tue, 23 April 2019 14:12 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.

1/ Are you sure you need to generate the in-memory collection and not use a simple INSERT-SELECT?
2/ Of course, FORALL loop is slower than a pure SQL solution
3/ Of course, writes (INSERT) are slower than reads (SELECT) (above all with indexes, check also triggers)

Post your code.
Before, Please read How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

You can also where the time is spent using SQL trace and PL/SQL profiler.

Previous Topic: Query taking 40 hours to execute
Next Topic: Converting returned rows into columns
Goto Forum:

Current Time: Mon Sep 21 15:03:25 CDT 2020