Re: Six of one . . .

Ed, my gut agrees with your gut. Initiating a new query for each row to be processed sounds pretty resource-heavy compared with submitting a single query that identifies all rows to be processed. Plus, the where clause on the UPDATE is searching the indexes/table based on criteria when you already know exactly which row needs to be updated (having just selected it).

• Select the rowid in addition to your other columns. Update where rowid = :ws-rowid. This will short circuit the searching for each update.
• Consider concurrency. Is it *guaranteed* that this program is the only concurrent process performing this particular task or updating these particular rows? Your programmer did not SELECT .... FOR UPDATE NOWAIT and therefore is not requesting exclusive row locks on the selected rows so there could be lock contention issues. NOWAIT will cause an exception if a row lock is not available, instead of hanging indefinitely waiting for it to become available, so your program logic can somehow handle the situation and keep plowing ahead. Getting a row lock will not prevent ordinary SELECTs by other sessions, only operations like SELECT FOR UPDATE, UPDATE, DELETE among others.
• A commit after every row updated seems like overkill (unless it is a strict requirement for the situation). You are incurring a lot of transaction overhead for each row. Think about the most appropriate logical transaction design (e.g. commit after every 100 rows?) to improve throughput while maintaining reasonable concurrency. N.B. a commit releases all row locks, so it's not possible to do a SELECT .... FOR UPDATE NOWAIT of all rows at once (and bad for concurrency). You may instead want to do a normal SELECT for all eligible rows, then do a SELECT .. WHERE ROWID = :ws-rowid FOR UPDATE NOWAIT to gain the row lock immediately before the UPDATE statement. Alternatively you could redesign the query WHERE clause to include criteria that will break up the total eligible rows into "fun size" chunks that can be handled in a single logical transaction, and place the cursor loop inside an outer loop to cycle through the values for the chunking criteria and commit after each chunk.

Too many options?

HTH
Martin Doherty

Ed Stevens wrote:

> Platform: Oracle 8.1.7 on NT4
>
> Developer is involved in a project of converting an application (written in
> Microfocus Cobol) from DB2/2 on OS/2 to Oracle on NT. The general problem at
> hand is to identify a selection of rows, sorted ascending by a 'timestamp' field
> (implemented in our 8.1.7 db as a char(26)), processing each row in turn.
> Processing of each row includes modifying a 'status' column that removes it from
> the original selection criteria.
>
> In the original application, it is implemented as shown with this pseudo code:
>
> ---- begin pseudo code example -----
>
> Begin loop
>
> SELECT MIN(CTE_DTS)
> INTO :ws-dts
> FROM MY_TABLE
> WHERE APPC_STA = 'P'
> AND CTE_DTS > '0001-01-01-00.00.00.000000'
>
> --- do a bunch of processing based on the row returned above, then change the
> status of that row ---
>
> UPDATE my_table
> SET appc_sta = 'Z'
> WHERE APPC_STA = 'P'
> AND CTE_DTS = :ws-dts
>
> commit
> End loop
> ---- end pseudo code example -----
>
> She was having some difficulty with inconsistent results, which I chalked up to
> inconsistent test cases. (She's a fairly inexperienced programmer, and this is
> her first exposure to Oracle). By the time I was able to take a look at it,
> she had replaced the above with a cursor, thusly;
>
> ---- begin pseudo code example -----
>
> Declare cursor c1 as
> SELECT CTE_DTS
> FROM MY_TABLE
> WHERE APPC_STA = 'P'
> AND CTE_DTS > '0001-01-01-00.00.00.000000'
> ORDER BY CTE_DTS
>
> Loop until no-more-rows
> Fetch cursor c1 into . . . .
> Do a bunch of processing
> Update row returned by the fetch
> commit
> end loop
> ---- end pseudo code example -----
>
> So now I'm wondering how to adviser her on the best approach from an Oracle
> standpoint. Which general approach would be preferred? My gut feel is the
> cursor loop.
>
> Begin-loop
> SELECT MIN(timestamp-field)
> WHERE condition-1
>
> UPDATE so that condition-1 is no longer true
> COMMIT
> End-loop
>
> OR . . .
>
> DECLARE CURSOR
> WHERE condition-1
> ORDER BY timestamp-field
>
> Begin-loop
> FETCH CURSOR
> UPDATE so that condition-1 is no longer true
> COMMIT
> End-loop
>
> I'm shooting from the hip here, so I may have missed some detail, but if you
> remember that I'm only presenting pseudo code, the general problem and question
> should be apparent.
>
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)