Hi Stephen,

> This query takes between 11 and 19 seconds.

>SELECT
> B.BOOK_ID,
> B.REFERENCE_NUMBER,
> B.IS_SHORT_LOAN,
> B.TITLE,
> B.DATE_DUE_BACK,
> B.IS_DELETED,
> B.RESERVED_BORROWER_ID,
> B.DATE_RESERVED,
> B.DEWEY_NO_ID,
> A.NAME,
> L.DESCRIPTION,
> D.DEWEY_NO
>FROM
> ((((BOOK B LEFT OUTER JOIN LOCATION L on B.LOCATION_ID = L.LOCATION_ID)
> LEFT OUTER JOIN AUTHOR A on B.AUTHOR_ID = A.AUTHOR_ID)
> LEFT OUTER JOIN DEWEYNO D on B.DEWEY_NO_ID = D.DEWEY_NO_ID)
> LEFT OUTER JOIN PUBLISHER P on B.PUBLISHER_ID = P.PUBLISHER_ID)
>ORDER BY
> B.TITLE,
> B.REFERENCE_NUMBER

>The plan is:
>PLAN SORT (JOIN (JOIN (JOIN (JOIN (B NATURAL,L INDEX (LOCATION0)),A INDEX
>(AUTHOR0)),D INDEX (DEWEYNO0)),P INDEX (PUBLISHER0)))

First TABLE PUBLISHER is nowhere used so that 'should the optimizer' removed
from the query.
I see you used only 1 field from the LEFT OUTER JOINED tables.
Maybe then a subselect works faster for you.
Give this a try :

SELECT
B.BOOK_ID,
B.REFERENCE_NUMBER,
...<snip>...
B.DEWEY_NO_ID,
(SELECT A.NAME FROM AUTHOR A WHERE B.AUTHOR_ID = A.AUTHOR_ID),
(SELECT L.DESCRIPTION FROM LOCATION L WHERE B.LOCATION_ID =
L.LOCATION_ID),
(SELECT D.DEWEY_NO FROM DEWEYNO D WHERE B.DEWEY_NO_ID = D.DEWEY_NO_ID)
FROM
BOOK B
ORDER BY
B.TITLE,
B.REFERENCE_NUMBER

Regards,
Arno Brinkman
ABVisie