Hi All,

I have two table. table details are

table one(Cust_det) has two fields (Cust_cin and cust_name).
table two(cust_add_det) has cust_cin and cust_address fields.

To join above two table which sql is better on perfomance point of view?
and how conditional where clause works?

select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B where
A.Cust_cin = ? and A.cust_cin=B.cust_cin

or

select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B where
A.cust_cin=B.cust_cin and A.Cust_cin = ?

Hi All,

I have two table in DB2 (ver 8). table details are

table one(Cust_det) has two fields (Cust_cin and cust_name).
table two(cust_add_det) has cust_cin and cust_address fields.

To join above two table which sql is better on perfomance point of view?
and how conditional where clause works?

select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B where
A.Cust_cin = ? and A.cust_cin=B.cust_cin

or

select A.cust_cin,B.cust_address from Cust_det A,cust_add_det B where
A.cust_cin=B.cust_cin and A.Cust_cin = ?

It makes no difference in what order your list the predicates in the WHERE clause. DB2 will figure out which predicate to evalute first (whether to do the join first, or filter the rows first).

Thanks for your rely, Am using db2 version 8. whether DB2 will figure out which predicate to evalute first in version 8?. it seems db2 wont support this future in verision 8.

Also how conditional where clause works? in case absence of figure out which predicate to evalute first by db2?

As a part of Performance tuning we are changing the where clause order. it showing some difference in time(exec time) after modified.. i think db2 ver 8 wont support this? Please correct me if am wrong.

You are wrong. No matter what version you have, DB2 will decide the order of predicate evaluation, regardless of the order or the predicates in the WHERE clause. The only exception might be if you changed the query optimization level (default is 5) but even then I doubt it would make a difference.

I don't know what you mean by conditional WHERE clause. I suspect the SQL statement you posted is not the real one, which may account for the problem.

The primary factors that affect the decision that DB2 makes about which predicate to evaluate first are:

1. Physical condition of the rows and indexes with regard to clustering, freespace, disorganization, etc. This can be changed with a reorg command.

2. The statistics that DB2 has regarding the number of rows and the items contained in number 1 above. This can can be updated with runstats command.

Also, if you have any outer joins, ORDER BY, GROUP BY, etc, these could affect predicate order evaluation.

Actually, DB2 will even apply what is called "transitive closure", by adding the condition so the above query is essentially rewritten into
which is completely symmetric in tables A and B.
Of course, DB2 will decide on the join method and the two table accesses, based on presence of indexes and on statistics (like size of tables), hence the implementation will most often not be symmetric in the two tables.

If you are having performance issues, they will rather be solved by database changes (like adding indexes or running RUNSTATS) rather than rewriting the query.
Unless of course your query contains other ingredients than in the query above, like e.g. scalar functions in the WHERE conditions, in which case a query rewrite might be very necessary...