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)
Regards
Remigiusz
--
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:
0000021828,
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--
//www.freelists.org/webpage/oracle-l
Other related posts:
