RE: SQL View - Embedded Select vs Join

Elvis,

I see your point. In this particular case the data IS static. The auxiliary tables contain the descriptions of several Codes (e.g. Diagnostic Codes) and they should not change in the course of time (although, of course, there could be new codes added). To give you an idea, the table I have to get descriptions for has twelve different (12) Diagnostic codes!!

Thanks again,

Luis



From: "Elvis Budimlic"

You might be able to define it as DETERMINISTIC, and indeed that may be
truly be the case if your data is static.
My point is that running a query against any non-static data is by default
non-deterministic. I mean, if the data changes, the query will return a
different result, which is the definition of non-deterministic (different
output for the identical input).

I should have elaborated a bit more on that point. As it was I just >assumed
data can change in your database.

Elvis

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


-----Original Message-----
Subject: RE: SQL View - Embedded Select vs Join


Elvis,

Thanks for your comments. All the auxiliary tables (where the descriptions
are located) have the search argument as their primary key, so I hope that
helps in the view's response times.

I find interesting that an UDF containing a sub-select cannot be defined as
deterministic. I would have thought that DB2 would check the input
parameter(s) first and give the results accordingly. Is there any way to
check if an UDF is using a cached result or not?


Thanks again,

Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions

****************************

My 2 cents:

I strongly suggest sticking with an explicit JOIN syntax and not depending
on the query optimizer rewrite of your embedded selects. This will
guarantee your intent and make it easier to maintain long term. I also
suspect that in your bigger test (12 files) you may find that the JOIN
approach performs better (provided join columns are properly indexed).

I don't think this particular scenario would benefit from a UDF approach,
since the result of a sub-select is non-DETERMINISTIC in nature.

Elvis