Re: Help !! Hints and Explain Plan

Joe,
Not true. You want to try this out using Explain plan and it would prove the point.

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