Re: Oracle Hint Behavior

On Jan 30, 9:28 am, "Dereck L. Dietz" <diet...@xxxxxxxxxxxxx> wrote:
Oracle 10.2.0.3.0
Windows 2003 Server

What is the behavior of hints in Oracle 10g? I knew that prior to 10g
Oracle would treat them as only "suggestions" but I thought in 10g they
would be mostly followed.

We have a query which is doing a full table scan even with hints being used
to try to get it to use an index. I can see why it would choose a full
table scan considering the percentage of the table being returned but I'd
like to be able to explain why the hint is being ignored now.

Thanks.


Even prior to 10g, if a hint was valid, oracle would follow it. I
believe
Jonathan Lewis explained in greater detail on when a hint might not be
followed.

example:
http://jonathanlewis.wordpress.com/2006/11/11/what-is-a-hint/

Anurag
.

Relevant Pages

  • Re: Why Statistics doesnt match reponse time?
    ... > four patches since then that have substantially improved the optimizer. ... Patches would not help him here. ... His hint causes the optimizer to do the ... As for the Oracle 9.2.0.6, the latest version, the optimizer was improved, ...
    (comp.databases.oracle.server)
  • Re: select value within an interval
    ... Any idea why Oracle reads the whole table or does index FFS if I use ... If you are going to hint "fully" hint as recommended by Jonathan Lewis. ... Be sure that the information you are providing to the optimizer is what ...
    (comp.databases.oracle.server)
  • Re: Oracle Hint Behavior
    ... Oracle would treat them as only "suggestions" but I thought in 10g they ... like to be able to explain why the hint is being ignored now. ... optimizer to use a particular index no matter how hard we tried. ... of DEC Rdb right after Oracle acquired Rdb back in '94/'95 timeframe. ...
    (comp.databases.oracle.server)
  • Re: Oracle Hint Behavior
    ... Oracle would treat them as only "suggestions" but I thought in 10g they ... like to be able to explain why the hint is being ignored now. ... There is an interesting way to influence the optimizer without the use ... of DEC Rdb right after Oracle acquired Rdb back in '94/'95 timeframe. ...
    (comp.databases.oracle.server)
  • Re: Append Bypass_recursive_check
    ... particular hint. ... Also the version of Oracle. ... transformation and temp table transformation ... and your are expecting your queries to do ...
    (comp.databases.oracle.server)