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

I haven't picked this one apart in detail, but if you want the query to use an index that starts with post.post_id to avoid the sort, them you might try

Put the tables in the order you expect Oracle to visit them (which means POST first and RATING_SUMMARY last - because of the
outer join).

Then put in an ORDERED hint, a FIRST_ROWS hint (because that almost always makes Oracle take the "index (nosort)" option if it is at all possible whatever the cost (in this version of Oracle).

You might also put in an INDEX(post {name of index}) hint where the {name of index} is the index which starts with the post_id.

You will need at least one NOT NULL column in the list of columns that make up the index.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





william milbratz wrote in message ...


>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