Re: select/insert/delete

  • From: Michael Moore <michaeljmoore@xxxxxxxxx>
  • To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Wed, 8 Jun 2011 09:56:24 -0700

Thanks Jonathan,
My assertion was true, basically I just wanted confirmation of that fact.

Beyond that, your threat concerns are spot on. I'm not looking for further
help because I don't like to waste peoples time on things I can figure out
myself, given a bit of effort. However, for the curious minded I will
provide a description of my situation.

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.

Regards,
and thanks again,
Mike




On Wed, Jun 8, 2011 at 9:32 AM, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx>wrote:

>
> Not possible - but the best option may depend on what problem you see as
> the biggest threat:
>   Volume of data to move
>   Complexity of identifying query
>   Guarantee of correctness
>   Threats from concurrency
>   Complexity of code
>
> In outline the following may be appropriate for high speed on a few
> thousand rows.
> PL/SQL
>   select bulk collect from tableA for update
>   forall insert into tableB
>   forall delete from tableA
>
> I'm assuming you can do the bulk collect with a For Update - but I may be
> wrong.
>
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
>
> ----- Original Message ----- From: "Michael Moore" <
> michaeljmoore@xxxxxxxxx>
> To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
> Sent: Wednesday, June 08, 2011 12:12 AM
> Subject: select/insert/delete
>
>
>  I think the answer to this is NO, but just to be sure,
>>
>> Is there a single SQL statement that can:
>>
>> 1) SELECT row-X from table A
>> 2) INSERT row-X into table B
>> 3) DELETE row-X from table A
>>
>> It would be nice to be able to do this without a context switch.
>>
>> Regards,
>> Mike
>>
>>
>>
>> -----
>> No virus found in this message.
>> Checked by AVG - www.avg.com
>> Version: 10.0.1382 / Virus Database: 1511/3687 - Release Date: 06/07/11
>>
>
>

Other related posts: