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

Charles,

Things may have changed with the SQE and access to more statistics, but to
the best of my knowledge, query optimizer treats an inequality operator
(which I'm guessing '>' qualifies for) as "there's likely 33% of rows to
qualify for this selection criteria". Since that's the only criteria in the
Where clause, and 33% is greater than 20% that's a cut-off for switching
over to a table scan, it'll opt for the table scan.
In other words, you have to 'convince' the query optimizer that less than
20% of rows will be affected for it to even consider an index
implementation. Fact that you're updating the same field that's being
queried on is likely to cause it not to use it anyway ... but your equality
test ('=') proved different, so it is possible...

Is it possible to enhance the selection criteria with some arbitrary
equality clause(s)? That may get it under the 20% estimate...

While in your testing mode, I would keep the EVI & RADIX index, and also
refresh the statistics on that column, just in case they're stale.
Statistics will of course help SQE only, so let me ask, is the UPDATE going
down the SQE path?

I like Chuck's idea of the SELECT FOR UPDATE OF, but I'm almost certain that
cursor will not use an index either.

As you say, native I/O gives you full control over the implementation, so
that's your best fallback plan for this scenario.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Thursday, June 26, 2008 9:42 AM
To: Midrange Systems Technical Discussion
Subject: RE: Table Scan during update - when the key field is being changed

Walden & Chuck,

I created the EVI, but it wasn't used, as the optimizer said the cost was
too high.

However, it appears the problem is that the flag field is numeric and the
statement is actually more
like:

Update mytable
Set flag = 12345
Where flag > 0

If I change it to:
Update mytable
Set flag = 12345
Where flag = -1

Then the original index is indeed used.

I'm going to continue to test some scenarios. My test data had more records
flagged than would
normally be.

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, June 26, 2008 10:25 AM
To: midrange-l@xxxxxxxxxxxx
Subject: 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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are not a
named addressee you are hereby notified that you are not authorized to read,
print, retain, copy or disseminate this communication without the consent of
the sender and that doing so is prohibited and may be unlawful. Please
reply to the message immediately by informing the sender that the message
was misdirected. After replying, please delete and otherwise erase it and
any attachments from your computer system. Your assistance in correcting
this error is appreciated.