Re: update field based on substring of numeric field

On 19-Oct-2015 03:13 -0500, satya tiruveedhi wrote:

I am surprised by your statement that performance of two statements
will be different. I am surprised that by adding statement <sic>
A.UMNUM=0 to where clause for update will improve SQL performance.
Quick check on our test LPAR (7.1) confirmed that your analysis is
correct.

Unpaid advise is not always worth [only] what was paid ;-)

Seriously though, I always remain skeptical that any /performance/ tests have been performed accurately; not without great details about the environment and testing methodology having been revealed with the reported results. For example, simply running one request and then the other request, timing each, is quite meaningless when comparing and contrasting how quickly the two requests can complete.

The reason I am surprised is that on most modern databases both
statements perform same way. I checked access plans (explain) on
Oracle and DB2 LUW for similar statements. The access plans for both
statements are same on both databases. However, on "Db2 for i", they
are different. The SQL without repeating predicate "A.UMNUM=0" did
take more time on "DB2 for i" when millions of records are involved.
On Oracle and DB2 LUW both statements performed well (and equally).

I refused to work on the optimizer, specifically because such vacuous concerns were sure to remain perpetually; an optimizer can [and I tend to avoid absolutes, but in this case I will stick with saying] *never* be 100% correct. That is the nature of the beast.

But the rule of writing statements in SQL has always been, that providing more information [in the form of both predicates and various other /hints/] to the optimizer will tend toward the optimizer making the most informed decision; hopefully leading to the more optimal of various possible outcomes in optimization. So by adding the predicate, I speculated not only that a reader might find the desired\intended effect more conspicuous, but so too might the optimizer. Thus I would not be surprised that the optimizer, by receiving such explicit additional information, would be able to navigate the data more efficiently to complete the request.

Yet I am not convinced there was any difference [see performance testing reference above], nor is any reader aware of what was the difference [per conspicuously omitted] in the EXPLAIN.

We had issues with "DB2 for i" (5.4) due to inefficient optimizer. We
were hoping the optimizer on 7.1 will be better. But, it looks like
"DB2 for i" optimizer (7.1) still performs poorly compared to Oracle
and DB2 LUW. The SQL in question is not complicated, it is a simple
one. "DB2 for i" could not handle a simple SQL as well as Oracle/DB2
LUW.

So open defect calls where the DB2 for i SQL fails so horribly; or just remain FD&H in conviction, /knowing/ that everything else is so much better, such that there would be no point in improving what might be denigrated instead, lest those convictions become compromised.