Re: Why are optimizer hints required?

The House Dawg 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
>

Well , before you apply Jonathan's wonderful hints on hints, is this particular FTS really bad? It may not be; the CBO will leave index FFS and index range scans for what they are worth, and switch to FTS if anywhere between 4 and 25% of the table needs to be read. (Some documentation claims 4%, other 25%, and there will probably be occasions where it's something else, completely).

Remember index and table reads are serialized - it's not something that happens in parallel! So an index read, followed by a table read may be less I/O friendly that a FTS.

-- 
Regards,
Frank van Bortel