Re: Using Hints for Views

From: Herman de Boer <h.de.boer_at_itcg.nl>
Date: Tue, 20 Aug 2002 07:11:04 GMT
Message-ID: <ajsq4r$puk$1@news1.xs4all.nl>

April wrote:

>SELECT /*+ INDEX(D TBLINVESTMENT_TABLE_ME,B TBLINVESTMENTS_NEW_ME,C
>TBLINVESTMENT_DESCRIPTION_ME,E TBLEMPLOYEES_ME) */
>

Hello April,

in 7.3, there are no global hints available; that feature has been introduced in 8i (according to my memory). With global hints, one can specify "index(a.table idx)", where "a" is the alias for the view in the query (can be an inline view), "table" is some table used in the view, and "idx" is the name of a usable index on "table".

In your situation, I would simply use the rule hint. To my opinion, the CBO is not that bad in 7.3, but it is necessary to use hints more often than in newer versions - and you need to use the RBO now and then.

Please also note that your index hint isn't written correct: each table should have its own index hint (if necessary). Also note that when there is a 'syntax error' in the hint-clause, the remaining hints seem to be ignored.

Kind Regards,

Herman de Boer
sr consultant
IT Consultancy Group bv Received on Tue Aug 20 2002 - 02:11:04 CDT