Hi Arno,

> Have you tried :
>
> SELECT
> t0.candidateid
> FROM
> candidateAttribute t0
> JOIN CandidateAttribute t1 ON (t1.Candidateid=t0.Candidateid AND

t1.Attributeid=34 AND

> T1.RATING<200)
> JOIN CandidateAttribute t2 ON (t2.Candidateid=t0.Candidateid AND

t2.Attributeid=321 )

> JOIN CandidateAttribute t3 ON (t3.Candidateid=t0.Candidateid AND

t3.Attributeid=3563 )

> JOIN CandidateAttribute t4 ON (t4.Candidateid=t0.Candidateid AND

t4.Attributeid=29 )

> JOIN CandidateAttribute t5 ON ((t5.Candidateid=t0.Candidateid AND

t5.Attributeid = 3916) OR

> (t5.Candidateid=t0.Candidateid AND t5.Attributeid = 1402) OR
> (t5.Candidateid=t0.Candidateid AND t5.Attributeid = 784) OR
> (t5.Candidateid=t0.Candidateid AND t5.Attributeid = 3461) OR
> (t5.Candidateid=t0.Candidateid AND t5.Attributeid = 2184))
> WHERE
> t0.Attributeid =256
>
> Regards,
> Arno Brinkman
> ABVisie

1.7sec! THANK YOU.

Just for the record, while out today it dawned on me that using the IN
as the 'prime controller' left-most stream would be better than my
earlier attempts (after reading Helen's book page 465 'Deciding on a
join order').

Result: 3.6sec

SELECT distinct t0.candidateid
FROM candidateAttribute t0
WHERE t0.Attributeid IN(3916,1402,784,3461,2184) AND
EXISTS
(
SELECT *
FROM CandidateAttribute t1
JOIN CandidateAttribute t2 ON (t2.Candidateid=t1.Candidateid AND
t2.Attributeid=321 )
JOIN CandidateAttribute t3 ON (t3.Candidateid=t1.Candidateid AND
t3.Attributeid=3563 )
JOIN CandidateAttribute t4 ON (t4.Candidateid=t1.Candidateid AND
t4.Attributeid=29 )
JOIN CandidateAttribute t5 ON (t5.Candidateid=t1.Candidateid AND
t5.Attributeid=256)
WHERE (t1.Candidateid=t0.Candidateid AND t1.Attributeid=34 AND
T1.RATING<200)
)

Then I logged into newgroup, saw your suggestion, ran it and it flew
and flew and flew :)

Tom