Re: select/insert/delete

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Wed, 8 Jun 2011 09:56:24 -0700
Message-ID: <BANLkTim3pfqHFeyAyUCYti4zAiwKkbgf+w_at_mail.gmail.com>

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_at_jlcomp.demon.co.uk>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_at_gmail.com>
> To: "ORACLE-L" <oracle-l_at_freelists.org>
> 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
>>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 08 2011 - 11:56:24 CDT