Re: Why are optimizer hints required?

There are several strategies you could adopt.

Your underlying problem is that from your perception, you are confident that this index is definitely the one to use. The optimizer, however, cannot work this out for itself, possibly because it is always working with statistics that are rapidly going out of date.

One thought is simply to create a set of index statistics for that index when it is looking its best, and store them (you can create a 'statistics table' using dbms_stats.create_stat_table (check sp.)). Then, add a job to your gather stats job to copy this indexes stats from the stat table back onto the index.

(There are variations and refinements on this theme that might be more appropriate for your site - but you get the general idea).

Oracle 10g has a nice little feature that once you got 'nice' stats on an object, you can lock them so that they don't get overwritten.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Jan 21st 2005






"The House Dawg" <mhousema_at_ix.netcom.com> wrote in message 
news:1110220323.599908.38390_at_z14g2000cwz.googlegroups.com...


> 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

>