Re: Help !! Hints and Explain Plan

I don't think you can nest hints. Try giving just the /*+ index ( table_alias ) */. Oracle would just ignore what it can't make sense out of the hints - for eg if you give /*+ xxxttt */ Oracle will just ignore this and determine the execution plan the way it would normally do without the optimizer hint. Also, you don't 've to tell the optimizer that use COST and a particular index. The moment Oracle sees your optimizer hint, it will use COST-based optimization regardless of :

* the init.ora optimizer_mode value
* the current session optimizer_goal value
* whether or not statistics have been gathered on the underlying objects.

Try the sql as
SELECT /*+ INDEX(AIS.ACCOUNT_TYPE CODE) */ ..

-- 
Cheers

Sridhar Subramaniam
Avion Consulting Services
Sydney - Australia

Disclaimer : All opinions are truly and just mine.
and it woul definitely use the hint you 've provided.

raj_at_cs.clemson.edu (Rajasekaran Rangarajan) wrote:


>Here's the entire query

>SELECT   /*+ COST */ /*+ INDEX(AIS.ACCOUNT_TYPE CODE) */


>*

>FROM

>AIS.ACCOUNT_TYPE, AIS.DIVISION,

>AIS.EXPENDITURE_CLASS, AIS.FUNDING_SOURCE, AIS.MAJOR_FUND_GROUP, 

>AIS.PROJECT_NUM

>BER, AIS.UNIT_NUMBER, AIS.BUD_CTR96,AIS.TMP_EXP

>WHERE

>AIS.TMP_EXP.MAJOR_FUND_GROUP=AIS.MAJOR_FUND_GROUP.CODE

>AND AIS.TMP_EXP.ACCOUNT_TYPE=AIS.ACCOUNT_TYPE.CODE

>AND AIS.TMP_EXP.EXPENDITURE_CLASS=AIS.EXPENDITURE_CLASS.CODE

>AND AIS.TMP_EXP.UNIT_NUMBER=AIS.UNIT_NUMBER.CODE

>AND AIS.TMP_EXP.MAJOR_FUND_GROUP=AIS.FUNDING_SOURCE.MAJOR_FUND_GROUP

>AND AIS.TMP_EXP.FUNDING_SOURCE=AIS.FUNDING_SOURCE.CODE

>AND AIS.TMP_EXP.MAJOR_FUND_GROUP=AIS.PROJECT_NUMBER.MAJOR_FUND_GROUP

>AND AIS.TMP_EXP.PROJECT_NUMBER=AIS.PROJECT_NUMBER.CODE

>AND AIS.TMP_EXP.DIVISION_NUMBER=AIS.DIVISION.CODE

>AND ais.bud_ctr96.new_bud_ctr='AFLS'

>AND AIS.BUD_CTR96.DEPT96=AIS.TMP_EXP.UNIT_NUMBER;

>