Re: select/insert/delete

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Wed, 8 Jun 2011 14:02:51 -0700
Message-ID: <BANLkTi=Q+twU=Ag+9jGWbAZGA+gqX=poLQ_at_mail.gmail.com>

Hi Timo,
You might not have caught my response to Jonathan. ...

Quoting myself,

"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 those transactions 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."

Also, there is some data transforms between tables A and B. I.e the data is slightly different.

Regards,
Mike

On Wed, Jun 8, 2011 at 1:30 PM, Timo Raitalaakso <rafu_at_iki.fi> wrote:

>
> You did not give us the reason you needed the select part in your single
> statement. Do you have actual reasons for being stuck with your current
> tables? I am not saying you should change your interface of a and b tables
> to the application. Just change the tables to views. Did you notice I was
> inserting through a view? Edition based redefinition is also using kind of
> such approach. Se editioning views
> http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17125/adfns_editions.htm#ADFNS020
> Well, possiby you can find a good enough reason not to use this update
> approach. This was a nice question, made me think of modeling and combine
> several ideas.
>
>
> Timo Raitalaakso
> http://rafudb.blogspot.com
>
> 8.6.2011 19:24, Michael Moore wrote:
>
> I'm pretty much locked into the two table design. There are existing tables
> with a lot of code that depends on them being just the way they are. I can't
> add columns, views, partitions etc. These are not options for me.
>
> insert into a (n) values (1);
>>
>>
>

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

Received on Wed Jun 08 2011 - 16:02:51 CDT