hi all, could smone help me in understand how db2 joins internally. I have read that the order of SQL is as

FROM
WHERE
GROUP BY
HAVING
SELECT

For the query below:

SELECT<some cols>
FROM TMIB_MBR_REIMB TMIB1
,TSLD_BCH_CLM_DET
LEFT OUTER JOIN
TIBP_ISS_BCH_PART
ON SLD_ISS_ID = IBP_IBA_AIS_ID
AND SLD_BCH_ID = IBP_IBA_BCH_ID
AND SLD_PIT_SEQ_NB = IBP_PIT_SEQ_NB
WHERE (MIB_CHK_GID_ID = 0
OR MIB_CHK_GID_ID = 888888888)
AND SLD_ISS_ID = MIB_ISS_ID
AND SLD_BCH_ID = MIB_BCH_ID
AND MIB_PIT_SEQ_NB = SLD_PIT_SEQ_NB
AND MIB_PIT_CLM_SEQ_NB = SLD_PIT_CLM_SEQ_NB

I believe that :
1) DB2 first prepare a result set say R1 by cartesian product of
TMIB_MBR_REIMB TMIB1 and TSLD_BCH_CLM_DET.
2) R1 is then left outer joined with TIBP_ISS_BCH_PART to give R2
3) Now as per sequence the WHERE clause is executed to filter out rows from
R2.

Please confirm if I am correct.

i am not as familiar with DB2 as other database engines, but there is a very good chance that this --is executed first, and the results then inner joined to TMIB_MBR_REIMB TMIB1

You are incorrect. DB2 does not do the same thing for each type of query. What DB2 does do, is examine the statistics of the tables, look at the indexes, look at the predicates, then determine what it thinks is the best access plan. Small changes to any part of this can greatly affect the access plan.

Andy

thanks for putting it so tactfully

did you examine the way that the three tables are joined?

My response was to the original post, not your reply. My experience has shown that DB2 will pick whatever access plan it deems is the fastest bast on the statistics and available indexes. A lot of times, how I expect the data to be read and how DB2 actually reads the data are totally different. One cannot just say that with a particular type of query, DB2 will always use a specific access plan. It is more complicated than that.

Andy

I have not seen a cartesian product being built to do perform a join.

Have you tried an explain plan.


===

In DB2 for LUW SQL Reference Volume 1
The sequence is logical sequence.
If same result could be produced more efficiently with another way,
DB2 optimizer is smart enough to use the way(access path) in actual access.

Example 1: DB2 may produce the result of the following query without accessing the employee table.
Because, empno column was defined NOT NULL.
Then "(number of rows with empno is null) = 0" is apparent without accessing the table.
Example 2: Another popular example is DB2 try to apply conditions in WHERE clause as early as possible.
It may be the time accessing tables in FROM clause,
and it may be before joining with another tables,
if the same result was guaranteed as processed in the logical sequence.

You can see an example of the importance of the understanding of the (logical) processing sequence in
alais name in the group by clause

Another effective use of the logical processing sequence may be designing/uderstanding of complex queries.
It must be great help to follow the seuence step by step and suppose the (logical or virtual) intermidiate result table of each step.

so the fact that the given query mixes old-style comma join with explicit join syntax, that doesn't matter?

i.e.

FROM a , b LEFT OUTER JOIN c

I think semantically it amounts to
which in this case means the same thing, so DB2 can choose any order of processing the joins.

Thanks for your reply. Could you please let me know what would be the logical processing sequence of my posted query. The aim of my question was not on how DB2 optimizes the query. I want to know how different joins would be applied to my posted query to give the Final result as I have to change the query and add more join conditions.

Thanks for your reply. Could you please let me know what would be the logical processing sequence of my posted query. The aim of my question was not on how DB2 optimizes the query. I want to know how different joins would be applied to my posted query to give the Final result as I have to change the query and add more join conditions to it.

n_i already answered your case
which is the query mixes old-style comma join with explicit join syntax and asked by r937.

Your query looks like...
FROM tmib_mbr_reimb tmib1 , tsld_bch_clm_det LEFT OUTER JOIN tibp_iss_bch_part ON ... WHERE ...

Seeing syntax diagrams and descriptions of from-clause, table-reference and joined-table,
I think it is natural to assum the query is equivalent to

FROM tmib_mbr_reimb tmib1 CROSS JOIN (tsld_bch_clm_det LEFT OUTER JOIN tibp_iss_bch_part ON ...) WHERE ...

that means...
1) join tsld_bch_clm_det and tibp_iss_bch_part result R1
2) cross join(Cartesian product) tmib_mbr_reimb tmib1 and R1 result R2
3) apply WHERE condition to R2 and an intermediate result table consists the rows from R2 and the condition is true.