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

milbratz_at_hotmail.com (william milbratz) wrote in news:cee3515e.0211021446.56595ca9_at_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

You don't state which optimizer mode (RULE or COST) the instance is using. If CBO, have ALL the tables been recently ANALYZE TABLE {table_name} ESTIMATE STATISTICS;

Try enabling SQL TRACE & run the output thru tkprof to see the real explain plan & to see where it is spending all its time.

"AND root_post.parent_post_id is null" will require a FTS of root_post

HTH & YMMV HAND! Received on Sat Nov 02 2002 - 17:38:46 CST