Re: Help !! Hints and Explain Plan

Sridhar Subramaniam (avion_at_ozemail.com.au) wrote:
: Joe,
: Not true. You want to try this out using Explain plan and it would prove
: the point.

It was my understanding that you only enabled cost-based on a per-statement basis with the ALL_ROWS or FIRST_ROWS hints (one or the other) if there are no statistics and the instance is set to Rule-based.

Joe

:
: --
: Cheers
:
: Sridhar Subramaniam
: Avion Consulting Services
: Sydney - Australia
:
: Disclaimer : All opinions are truly and just mine.
:
: joe_at_access1.digex.net (Joe Nardone) wrote:
: >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 |
:
:
 

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