Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!kibo.news.demon.net!news.demon.co.uk!demon!not-for-mail From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server Subject: Re: Query tuning experts: Any hope for tuning query with 5+ joins and a sort? Date: Mon, 11 Nov 2002 13:13:45 -0000 Lines: 50 Message-ID: References: NNTP-Posting-Host: jlcomp.demon.co.uk X-Trace: news.demon.co.uk 1037020781 22563 158.152.75.41 (11 Nov 2002 13:19:41 GMT) X-Complaints-To: abuse@demon.net NNTP-Posting-Date: Mon, 11 Nov 2002 13:19:41 +0000 (UTC) X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3 X-Newsreader: Microsoft Outlook Express 4.72.3110.5 Xref: newsfeed1.easynews.com comp.databases.oracle.server:166554 X-Received-Date: Mon, 11 Nov 2002 06:19:27 MST (news.easynews.com) Sorry about the late reply. I've been lecturing in the US, and haven't had much spare time for the newsgroup. Remember that the optimizer is continually evolving, and it takes time for new features to spread through all the code areas. Depending on version, you will find that there are several places where special optimisation features don't work with new index options (viz function based, reverse key, descending column) indexes. This is just one such example - it does work with 9.2 though. -- 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 ... >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. >