Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Query tuning experts: Any hope for tuning query with 5+ joins and a sort?
Greetings,
I'm trying to tune a query that joins several tables and sorts the results. I cannot, however, induce Oracle to use the indexes I have set up, even when the indexes on the 'main' table clearly match the 'order by' clause.
I've tried the various INDEX hints, but Oracle seems to ignore them. Instead, explain plan shows an outer "SORT (ORDER BY) ....".
I've upped my sort_area_size which has helped performance considerably, but I'd really like the database to use the indexes and avoid the overhead of sorts: it's fairly slow with my sample set of 20,000 rows.
My questions:
1) Does the 'hint' mechanism stop working when the number of joins get
past a certain threshold (i.e. "beyond three joins, you're on your
own")
2)What's the best strategy to get a query that uses 5+ joins plus a sort to use the indexes for its sorting.
Here's an example query. This one uses 9 joins.
(I've replaced the selected columns with 'select *' for space considerations here.)
We're running Oracle 8.1.7.
Thanks,
bill m
SELECT *
FROM
post,
msg_board,
msg_board_type,
member ,
element post_element,
element,
rating_summary ,
thread,
post root_post
WHERE post.msg_board_id = msg_board.msg_board_id
AND msg_board.msg_board_type_code = msg_board_type.msg_board_type_code
AND post.member_id = member.member_id
AND post_element.element_type_id = 11 AND post_element.element_key = post.post_id AND element.element_type_id = 4
AND post.staging_code = '50' AND post.status_code = 'ACTIVE' AND post.msg_board_id =10100101
![]() |
![]() |