I found the following statement in the help for the TIB_Query component
which has me a little concerned: "This biggest gotcha is if you place join
criteria in the WHERE clause. IBO does not like this in the buffered query
and problems could result."

The reason this has me concerned is because I cannot seem to get acceptable
performance using LEFT JOIN with an order by clause instead of using join
criteria in the WHERE clause.

Here is an example to help clarify:

SELECT *
FROM NAME_ADDRESS N
LEFT JOIN BILLING_DATA B
ON B.CO = N.CO
AND B.YR = N.YR
AND B.PARCEL = N.PARCEL
ORDER BY N.CO, N.YR, N.PARCEL

Here is the resulting plan:
PLAN SORT (JOIN (N NATURAL,B INDEX (BILLING_DATA_00)))

SELECT *
FROM NAME_ADDRESS N
, BILLING_DATA B
WHERE B.CO = N.CO
AND B.YR = N.YR
AND B.PARCEL = N.PARCEL
ORDER BY N.CO, N.YR, N.PARCEL

Here is the resulting plan:
PLAN JOIN (N ORDER NAME_ADDRESS_00,B INDEX (BILLING_DATA_00))

Both tables have an index over CO, YR, PARCEL.

The LEFT JOIN example takes forever to execute and just about cripples the
server. The second example executes almost instantly and the impact on the
server is almost nothing. I am not all that familiar with optimizing PLANs
and cannot figure out how to get the LEFT JOIN example to not use NATURAL
??????

I would prefer to use LEFT JOINs for readability, but the majority of the
tables in my database have these same CO, YR, PARCEL fields and LEFT JOINs
are causing me allot of grief. What kind of problems can I expect by using
join criteria in the WHERE clause of a TIB_Query component ?

Chris Landowski
Dynamic Software Solutions