> This is the plan for the few second query:
>
> Plan:
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEM NATURAL,ITEMINFORMAT
> INDEX (PK_ITEMINFORMAT)),ACTORITEM NATURAL),ACTORS INDEX
> (PK_ACTORS)),FORMATS INDEX (PK_FORMATS)),DISTRIBUTORS INDEX
> (PK_DISTRIBUTORS)),GENRES INDEX (PK_GENRES))
> Adapted plan:
> PLAN JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEM NATURAL,ITEMINFORMAT
> INDEX (PK_ITEMINFORMAT)),ACTORITEM NATURAL),ACTORS INDEX
> (PK_ACTORS)),FORMATS INDEX (PK_FORMATS)),DISTRIBUTORS INDEX
> (PK_DISTRIBUTORS)),GENRES INDEX (PK_GENRES))
>
> and this is the plan for the few minute query:
>
> Plan:
> PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEM
> NATURAL,ITEMINFORMAT INDEX (PK_ITEMINFORMAT)),ACTORITEM
> NATURAL),ACTORS INDEX (PK_ACTORS)),FORMATS INDEX
> (PK_FORMATS)),DISTRIBUTORS INDEX (PK_DISTRIBUTORS)),GENRES INDEX
> (PK_GENRES)))
> Adapted plan:
> PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (ITEM
> NATURAL,ITEMINFORMAT INDEX (PK_ITEMINFORMAT)),ACTORITEM
> NATURAL),ACTORS INDEX (PK_ACTORS)),FORMATS INDEX
> (PK_FORMATS)),DISTRIBUTORS INDEX (PK_DISTRIBUTORS)),GENRES INDEX
> (PK_GENRES)))
>
> Thanks,
> Mark
>

Even in the original query, it seems to not use the index
on "ITEM.TITLE" so my original diagnosis was wrong. Perhaps the
statistics are out of date and so it is not using it? Or perhaps the
index defined is of no help to the sorting?

Adam