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

Thanks guys for the input. And Mr. Jonathan Lewis, you get the gold star as the 'ORDERED' hint effected the quantum difference. (3 minutes vs. 1 second). A big woo-hoo!

Some follow up issues :
1) The /*+ ORDERED FIRST_ROWS */ hints work fine when I'm querying on
'normal' (i.e. btree indexes, nothing fancy...)..but when I used a
function based index, Oracle doesn't take the hint..i.e. and does its own sorting. In addition, I've tried using the 'INDEX' and the
'INDEX_ASC' hint. No avail.

i.e. same query as above..

   ORDER BY upper(post.subject)

2) Likewise I couldn't get Oracle to 'take the hint' if the query sorts on the non-primary table, even though though the other column is indexed correctly:

   ORDER BY member.last_name

Does this cohere with your individual and collective experiences and understandings? Any other steps to take for function based indexes or
'non "main table"' sorts?

thanks again.

bill m Received on Mon Nov 04 2002 - 11:12:52 CST