Re: RE: update field based on substring of numeric field

SQL optimization / performance is highly dependent on environment.

Are you running you tests on single user LUW / Oracle PC install vs a
shared IBM i system? IBM i is often beat by PCs on a single task as the
IBM i is optimized for multiple users / tasks.

How about hardware equivalency? I've yet to see a IBM i TEST LPAR that
wasn't as close to the bare minimums as it could be.

Unless you're running LUW / Oracle on AIX and thus similarly starving them
for resources, your test likely isn't very fair.

I assume you've got he same set of indexes across platforms. But I'll go
ahead an mention it.

Charles

On Mon, Oct 19, 2015 at 4:13 AM, satya tiruveedhi <satyatiruveedhi@xxxxxxxxx

wrote:

@CRPence

<< But performance-wise, including that predicate could be helpful; >>

I am surprised by your statement that performance of two statements
will be different. I am surprised that by adding statement 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.

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).

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.
--
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.