Hi All,

The queries below use the new Expression indexes and the good news is
that they, on their own, seem to work well. However:

Example 1: Execute time=7s 140ms
--------------------------------
SELECT co.compname, c.surname, c.forename, c.title, s.siteid, c.clntid
FROM Company co
JOIN Site s ON (co.compid=s.compid)
JOIN Client c ON (s.siteid=c.siteid)
WHERE (UPPER(surname) STARTING WITH 'SMITH')
ORDER BY UPPER(surname)

PLAN SORT (JOIN (S NATURAL, CO INDEX (PK_COMPANY), C INDEX
(IDXCLNTFULLNAME, FK1_CLIENT)))

Example 2: Execute time=62ms, using +0 'trick'
----------------------------------------------
SELECT co.compname, c.surname, c.forename, c.title, s.siteid, c.clntid
FROM Company co
JOIN Site s ON (co.compid=s.compid)
JOIN Client c ON (s.siteid=c.siteid+0)
WHERE (UPPER(surname) STARTING WITH 'SMITH')
ORDER BY UPPER(surname)

PLAN JOIN (C ORDER IDXCLNTFULLNAME INDEX (IDXCLNTFULLNAME), S INDEX
(PK_SITE), CO INDEX (PK_COMPANY))

Can anyone explain why the combining of two client indexes by FB in
the first query i.e. C INDEX (IDXCLNTFULLNAME, FK1_CLIENT)) should
cause such a performance hit please? Also, why it doesn't choose the
PK_SITE first off?

This must be quite a problem as I had expected this to be fixed in 2.1
- Does anyone know if it actually able to be fixed or is it a problem
deeper in the engine?

Tom