Oracle Hint Behavior

From: "Dereck L. Dietz" <dietzdl@xxxxxxxxxxxxx>
Date: Wed, 30 Jan 2008 09:28:45 -0500

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.

.

Follow-Ups:
- Re: Oracle Hint Behavior
  - From: Anurag Varma
- Re: Oracle Hint Behavior
  - From: Mark D Powell

Prev by Date: Erwin: setting a property for all tables
Next by Date: Re: What is unique_id in Oracle and what's the use of it
Previous by thread: Erwin: setting a property for all tables
Next by thread: Re: Oracle Hint Behavior
Index(es):
- Date
- Thread

Relevant Pages

Re: Hints in Practice
... and do not conform to any standard. ... Thus you don't use array processing, don't use exception handling, don't use PL/SQL packages, and heaven-forbid don't use any type of index other ... then embrace Oracle. ... If they are confident about the optimizer, why are hints necessary? ...
(comp.databases.oracle.server)
Re: Hints in Practice
... Are there any other Oracle features do what RAC does? ... hints if you want to force Oracle's CBO to use a specific plan. ... Those cases would be when the optimizer is not doing its job. ...
(comp.databases.oracle.server)
Re: Help with sql query speed. Explain plan included
... Oracle 10.2.0.3 running on Windows Vista. ... 10.2.0.3, at least on this platform, seems to ignore ... Speaking of hints you just reminded me that in the current issue of ...
(comp.databases.oracle.server)
Re: obvious bugs with 10.2.0.2 and aix5L
... consultants might be over looking that could cause high cpu usage with ... Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text - ... erp doesn't have any hints that I have ever seen in the sql that is ... I have seen some recursive sql that have hints but that is ...
(comp.databases.oracle.server)
Re: Hints in Practice
... The analogy holds. ... Are there any other Oracle features do what RAC does? ... hints if you want to force Oracle's CBO to use a specific plan. ... Those cases would be when the optimizer is not doing its job. ...
(comp.databases.oracle.server)