Re: Why are optimizer hints required?

On 7 Mar 2005 10:32:03 -0800, "The House Dawg" <mhousema_at_ix.netcom.com> wrote:

>All,
>
>Prior to putting PL/SQL into production appropriate indexes are created
>and verified that they are being used with EXPLAIN PLAN. The DBA's
>calculate statistics every weekend using a 20% sample size on all
>tables in the schema, but invariably some of the PL/SQL degrades into
>doing full tables scans. This has been isolated to a table that has
>around 25 columns. In particular, one column in the table is updated 4
>times as the row goes through various state transitions and the index
>on this column is eventually ignored.
>
>The DBA's wish to take the easy way out and force all software
>developers to use optimizer hints but I'd rather not hardcode index
>names into my source code for several reasons.
>
>Are there ways to ensure that when SQL has been put into production and
>is using indexes that the SQL doesn't eventually degrade into full
>table scans?
>
>TIA,
>
>Matt

Thomas Kyte in his seminar advises against hinting every statement. He also advises to use generic hints only, like the ORDERED hint. Implicitly he advises against forcing the optimizer to use a specific hint.
He warns hints are nothing more and nothing less than what they are: hints! If the optimizer comes up with a better plan ignoring hints, it will use that.

Hth

--
Sybrand Bakker, Senior Oracle DBA