My understanding is you did it the best way.
Alternatively, you may word your query as
SELECT i.cname FROM fc_client_info i
WHERE EXISTS ( SELECT * FROM fc_communication c WHERE c.acode = i.acode AND c.contactdate =
'09/06/2001')
ORDER BY lower(i.cname);
instead of
SELECT i.cname FROM fc_client_info i, fc_communication c WHERE i.acode =
c.acode AND c.contactdate = '09/06/2001' ORDER BY lower(cname);
I suppose the correlated subquery using the EXISTS predicate, which
results in an outer loop join, is much slower than your originally
used sort-merge join.
Try, but make sure both tables are well populated, otherwise the
benchmark result is no good.
Christoph
