Re: Best way for updating EVERY row in a large database?

From: doid <doid_at_usa.net>
Date: Thu, 23 Apr 1998 18:08:12 GMT
Message-ID: <353F848F.E01A1717@usa.net>

Been there...

Sounds like you're using OCI or a precompiler but this should work for u:

    Don't open the cursor "FOR UPDATE";     fetch the rowid;
    use to rowid to identify the row u wish to update or delete.

I've used this from PL/SQL many times. Oracle documents it, but points out that you lose most of the locking and read-consistency protection that you get when u open a cursor FOR UPDATE. The advantage is that you won't have to re-open the cursor after a commit. This will work for any cursor which fetches a rowid, even if you're using a much different syntax than I've used in this example.

Hope this helps.

for csr in (

                select   rowid, col1, col2, ..... colN
                from    mytab

)loop

            /* do some stuff */
            .
            .
            update mytab
                  set ....
              where rowid = csr.rowid;

            commit work;  /* u prob'ly won't do this after EVERY fetch */

end loop;

---

Jim Morgan wrote:

> This program is taking over 9 hours to run, and I am wondering if there is
> something I can do about that. I've looked into what I saw as the only
> viable alternative, and that is to use the WHERE CURRENT OF positioned
> cursor update instead of doing a separate UPDATE call for each fetched row.
> The problem with this approach is that, according to Oracle doc, you cannot
> fetch from the cursor anymore after you do a commit (you will get a 1002).
> I simply cannot update 3,000,000 rows without doing periodic commits.
>
> --
> Regards,
> Jim

--
The views expressed here are mine and do not reflect the official position of my employer or the organization through which the Internet was accessed. Received on Thu Apr 23 1998 - 13:08:12 CDT