Re: Table Scan during update - when the key field is being changed

An Encode Vector Index [EVI] may give the optimizer enough information to still use that index... if the restriction is not there due to the optimizer not being smart enough to rule out a loop on updating the same row(s). Or the EVI could simply limit the rows to be scanned, such that the update query still does not use the key on the flag.

An SQL cursor using SELECT FOR UPDATE OF can be used instead of native I/O. Not sure if the same RC13 might not still occur however. Perhaps not, since the onus is then on the user not to cause a loop.

What is the DDL for the TABLE and INDEX objects? More specifically, what is the unique or other keys, beyond just a flag... something that can join matching rows, irrespective of existence of the flag? An AND can be added to the WHERE clause, including a subquery that matches the records with same FLAG='Y' test; the subquery of course is read-only, so the RC13 restriction does not apply. If the number of rows having value 'N' is always very small, this is probably the best option.

Regards, Chuck

Wilt, Charles wrote:

I've got a file with what amounts to basically a flag field in it.
There is a DDS keyed logical over this field.

Once the records with the flag set have been processed, I need to
reset the flag field.

Update mytable Set flag = 'N' Where flag = 'Y'

However, during the update a full table scan is always performed.
The debug message CPI432C - All access paths were considered for file
indicate that the access path over the was not used because of reason
code 13.

13 - The access path contains one or more keys which may be changed
by the query during an insert or update.

I suppose this makes sense, but this table has about 3 million
records, so a full table scan is costly when only 1-2% of the records
need updated.

We are running v5r2, I don't suppose that this limitation has changed
at v5r4?

Regardless, anybody have any tips or techniques to deal with this?

I suppose that there's always an RPG stored procedure that uses
native I/O.