Whel, as I see, there are no way to speed up this query, because you ARE
requesting ALL rows from Books. That is why optimizer is using NATURAL
for Books.

Dufloth.




Stevio wrote:

>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
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>