Seeing we are on a roll and you all like a challenge :-) I have another slow
query for you.

This query takes between 11 and 19 seconds.

Book table has 33043 records
Author table has 16325 records
Location table has 6 records
Deweyno table has 6226 records
Publisher table has 4685 records

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)))

The outer joins are needed so that, for example, if a book has no author
(and therefore no author_id) it is still listed. This query should list all
books.

author id missing from 546 books
location id missing from 9 books
dewey no id missing from 111 books
publisher id missing from 1158 books

Stephen