Re: select/insert/delete

  • From: Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 9 Jun 2011 08:47:49 +0200

> Currently there is an Online Approval screen (java) where the user
> approves up to say 200,000 transactions. When the user hits SUBMIT, a
> PL/SQL process is invoked. The PL/SQL process first applies several
> business edits to the approved transactions: do they all have acceptable
> status codes; is referential data in place; and so on. The process then
> reads all of the approved transaction from table A, inserts those
> transactions into table B and then deletes that transaction from table
> A. The whole process can take up to 10 minutes depending on system load.
> Meanwhile the user sits there waiting for control of the screen to
> return. There ARE some concurrency concerns during this 10 minute window. 
> I need to make this run a hell of a lot faster. Good news is, I'm pretty
> sure I can do it.
What You could do (which does not speedy the thing at all ;-) still
improves a situation from a user point of view, assuming the application
is under Your control) is to run this as a job (or even few jobs if You
are able to divide a work between few processes) and provide in some
table a status to read - user is able to get the control almost
immediately, and do other things.

And another thought (if You have partitioning option) is to partition by
user which approves transactions - everything one needs would be to
exchange partition into another name (possibly twice if You want to have
it within another "more general" table)

Pole nakazi

Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
pos   : DBA at DIiUSI
addr  : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia, 
wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem: 
dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku, 
VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego, 
o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych, 
NIP: 586-000-78-20, REGON: 190024711--

Other related posts: