Query tuning experts: Any hope for tuning query with 5+ joins and a sort?

From: william milbratz <milbratz_at_hotmail.com>
Date: 2 Nov 2002 14:46:24 -0800
Message-ID: <cee3515e.0211021446.56595ca9@posting.google.com>

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 element.element_key = post.msg_board_id AND rating_summary.element_id(+) = post_element.element_id

AND post.staging_code = '50'
AND post.status_code = 'ACTIVE'
AND post.msg_board_id =10100101 

AND root_post.parent_post_id is null
AND root_post.thread_id = thread.thread_id AND post.thread_id = thread.thread_id
ORDER BY post.post_id Received on Sat Nov 02 2002 - 16:46:24 CST