Hi All,

I'm testing the method outlined below and would appreciate any
comments as to whether you see a flaw or not.

It appears to give the correct results and avoids using certain
indexes as many times as there are values (when used as an IN() test
with constants).

Example Usage (Old):
--------------------
SELECT c.contactid, c.surname, c.forename, c.dob
FROM Contact c
WHERE (c.typeid in (4,5,6,7,12,13,14,15)) AND
(c.areaid IN (1475, 225,222)

PLAN:
(C INDEX
(IDX_CONTACT_AREAID,
IDX_CONTACT_AREAID,
IDX_CONTACT_AREAID,
IDX_CONTACT_TYPEID,
IDX_CONTACT_TYPEID,
IDX_CONTACT_TYPEID,
IDX_CONTACT_TYPEID,
IDX_CONTACT_TYPEID,
IDX_CONTACT_TYPEID,
IDX_CONTACT_TYPEID,
IDX_CONTACT_TYPEID))

Example Usage (New):
--------------------

CREATE TABLE QRYCRITERIA
(
CRITERIACOLUMN VARCHAR(50) NOT NULL,
CRITERIAVALUE VARCHAR(255) NOT NULL,
USERID INTEGER NOT NULL
);

CREATE INDEX IDXQRYCRITERIA_CRITERIACOLUMN ON QRYCRITERIA
(CRITERIACOLUMN, USERID);
CREATE INDEX IDXQRYCRITERIA_USERID ON QRYCRITERIA (USERID);

DELETE FROM QRYCRITERIA;
INSERT INTO QRYCRITERIA VALUES ('TYPEID', 4 );
ditto for 5,6,7,12,13,14,15
INSERT INTO QRYCRITERIA VALUES ('AREAID', 1475 );
ditto for 225,222

SELECT c.contactid, c.surname, c.forename, c.dob
FROM Contact c
JOIN QryCriteria q1 on (q1.userid=1) and (q1.CRITERIACOLUMN='TYPEID')
JOIN QryCriteria q2 on (q2.userid=1) and (q2.CRITERIACOLUMN='AREAID')
WHERE (c.typeid in (q1.criteriavalue)) AND
(c.areaid in (q2.criteriavalue))

PLAN:
JOIN
(Q1 INDEX (IDXQRYCRITERIA_CRITERIACOLUMN),
Q2 INDEX (IDXQRYCRITERIA_CRITERIACOLUMN),
C INDEX (IDX_CONTACT_AREAID,IDX_CONTACT_TYPEID))


Thanks,
Tom