Hi All,

Generally speaking (so far) the strategy seems to give very good
results. However, a strange anomaly has cropped up, can anyone throw
some light on this one please?

The following query was expected to use the available c.postcode index
(IDXCONTPOSTCODE) but did not:

SELECT c.contid, c.surname, c.forename, c.dob, c.postcode, c.filed,
c.available, c.email
FROM cont c
JOIN QryCriteria q1 on (q1.userid=409 and q1.CRITERIACOLUMN='POSTCODE' )
WHERE c.postcode STARTING WITH q1.criteriavalue

PLAN JOIN (C NATURAL,Q1 INDEX (IDXQRYCRITERIA_CRITERIACOLUMN))
---------------------------------------------------------

whereas adding 'c.contid>0 and ' to start of WHERE clause forces the
use of *postcode* index!

SELECT c.contid, c.surname, c.forename, c.dob, c.postcode, c.filed,
c.available, c.email
FROM cont c
JOIN QryCriteria q1 on (q1.userid=409 and q1.CRITERIACOLUMN='POSTCODE' )
WHERE c.contid>0 and c.postcode STARTING WITH q1.criteriavalue

PLAN JOIN (Q1 INDEX (IDXQRYCRITERIA_CRITERIACOLUMN),C INDEX
(IDXCONTPOSTCODE,PK_CONT))


Any ideas what causes this anyone?

Tom