<snip>

> SELECT bk.ISBN13, bk.FTI
> FROM Book bk
> WHERE bk.ISBN13 IN (
>
> SELECT bkw.ISBN13
> FROM KeyWord kw
> JOIN BookKeyWord bkw ON bkw.WordRef = kw.WordRef
> WHERE kw.Word = 'WET'
> OR kw.Word = 'WILD'
> GROUP BY bkw.ISBN13
> HAVING COUNT(bkw.ISBN13) = 2)

<snip>

> PLAN SORT (JOIN (KW INDEX (KEYWORD_WORD,KEYWORD_WORD),BKW INDEX
> (BOOKKEYWORD_PK)))
> PLAN (BK NATURAL)

Hi Rob,

The problem here is that the optimiser is doing a natural walk through
the book table, and from each record doing an exists check. As the
number of book records increase, this will get linearly slower.

The only thing (in pure SQL anyway) that I can think of that might
give the optimiser the appropriate hint is:

SELECT bk.ISBN13, bk.FTI
FROM Book bk
WHERE bk.ISBN13 IN (

SELECT bkw.ISBN13 || ''
FROM KeyWord kw
JOIN BookKeyWord bkw ON bkw.WordRef = kw.WordRef
WHERE kw.Word = 'WET'
OR kw.Word = 'WILD'
GROUP BY bkw.ISBN13
HAVING COUNT(bkw.ISBN13) = 2)

(assuming ISBN is a string, use +0 if a numeric or time based type)

Obviously not tested. It just prevents the BOOKKEYWORD_PK index which
should make the better plan more attractive if the optimiser is clever
enough.

Adam