Carlos wrote:

>PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (E NATURAL,A INDEX
>(RDB$PRIMARY20)),P INDEX (RDB$PRIMARY19)),RH INDEX (RDB$PRIMARY10)),I INDEX
>(RDB$PRIMARY4)),EX INDEX (EXEMPLAR_IDX_VOL_FASC)))

>I can't see any way to optimize it more than that.

Well, it's a good plan (which I normally would be proud of ;o), but not
quite optimal. At first, I thought you were selecting the entire E table,
but if so a somewhat better plan would be something like

PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (E ORDER <someindex>, A INDEX
(RDB$PRIMARY20)),P INDEX (RDB$PRIMARY19)),RH INDEX (RDB$PRIMARY10)),I INDEX
(RDB$PRIMARY4)),EX INDEX (EXEMPLAR_IDX_VOL_FASC)))
i.e have an index for the fields you're ordering by (assuming they are part
of the table E).

However, if you're selecting a subset of E (normally the best option if it
contains more than a few records), you might want an index suitable to your
where clause (which you've already managed brilliantly for all the other
tables).

Still, I suspect your main problem is something completely different and
that my suggested improvements would only be minor, since it is fast if
attempting second time.

One thing about IB that surprises me, is that it always choose to use lots
of JOINS once more than five tables get involved. Can Helen or anyone
explain why it cannot use a simpler plan like
PLAN JOIN (E ORDER <someindex>, A INDEX(RDB$PRIMARY20), P
INDEX(RDB$PRIMARY19), RH INDEX(RDB$PRIMARY10), I INDEX(RDB$PRIMARY4), EX
INDEX(EXEMPLAR_IDX_VOL_FASC))
And does this matter for performance at all?

Set