Re: Update Query Question

Thanks for the response.

I wrote the PL/SQL, it wasn't a big deal. I was thinking there should be a "SQL" way to do this type of update. My thoughts were..."This is a pretty common action, why should it be so difficult?"

I did a similar update (Master table 3mil and temp table 300k, only 50k rows updated) in Access and it only took a few minutes using SQL.

I think that's pretty sad HP V2500 w/ 10 processors running Oracle = 1.5+ hrs, Dell 300 laptop running Access 97 10-20min.

The difference appears to be the the Access SQL syntax allows a join to occur prior to any rows from master being moved into "rollback segments" (undo exists in Access but is not a managed feature) whereas the Oracle syntax tries to move all 17mil rows into rollback segments priot to the update occurring of the affected 50k.

I was hoping that it was just a function of my less than adequate SQL syntax knowledge.

Any other thoughts...

Noodles wrote:
>
> Look into PL/SQL. Basically, you would open a cursor and perform your SQL
> select. Then you fetch values from that cursor into some variables and then
> perform your insert. You can buffer your commits so that you don't fill up
> your rollback segment.
>
> I'm using this logic in moving data from legacy system into a new table and
> each PL Procedure is about 20 lines long. Performance-wise I can update 500000
> records in about 4 minutes. ( HP 10.x running 8.0.5 )
>
> Respond if you need some example code.
>
>
Received on Sun Aug 22 1999 - 12:31:45 CDT