Re: Help !! Hints and Explain Plan

Sridhar Subramaniam (avion_at_ozemail.com.au) wrote:
: 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.

I don't think this is a correct statement with regard to the last statement.

If there are no stats, and the session and/or init.ora is 'RULE' then it will use rule based and ignore the hint altogether.

Joe

:
: 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;
: >
:
:
:
:
 

-- 
                                   
=------------------------------------------------------------------------=
Joe Nardone               |    
joe_at_access.digex.net      |