MVSFORUMS.com

edkir98 · Posted: Thu Sep 18, 2008 6:30 am Post subject: Order of execution of Query

Suppose I have a query as follows

jim haire · Beginner Joined: 30 Dec 2002 Posts: 140 Topics: 40

You could run an Explain on the query which would tell you what was performed first. There is also a free tool called Visual Explain from IBM which you can download which would show the order.

We can't tell from the query which order the statements would be performed in. The optimizer makes choices depending on the number of rows in each table, filter factors, and many other criteria.

arungr · Beginner Joined: 12 Feb 2005 Posts: 16 Topics: 4

I have a similar qustion. All I wanted to know which will get executed first in the above query. "ON" or "WHERE".

arungr · Beginner Joined: 12 Feb 2005 Posts: 16 Topics: 4

I had asked the above question because I do not have access to Plan_Table and hence I wont be able to run "Explain". Any inputs are appreciated.

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

Regarding your access to a PLAN_TABLE:
You need access to a PLAN_TABLE of another userid (table qualifier different from your User-ID), if you want to explain the access path of an existing DB2 package. If you want to know the access path of a single SQL statement, you can create a PLAN-TABLE with your own User-ID as qualifier and then issue an EXPLAIN SQL statement through any interactive SQL facility (SPUFI, BMC catalog manager, etc.). The create statement for the latest version of PLAN_TABLE can be found in the DB2 manuals or derived by you from an existing PLAN_TABLE.

Regarding to the sequence of ON and WHERE: As far as I have understood it, ON is issued first, but only when the "outer-joined table" is accessed by the optimizer during the preparation process of the statement. So some predicates of the WHERE clause, that have nothing to do with your "outer-joined table", can already be evaluated.

regards
Christian