Re: select/insert/delete

[..]
> 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_at_nordea.com>
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--
http://www.freelists.org/webpage/oracle-l