Why are optimizer hints required?

From: The House Dawg <mhousema_at_ix.netcom.com>
Date: 7 Mar 2005 10:32:03 -0800
Message-ID: <1110220323.599908.38390@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 Received on Mon Mar 07 2005 - 12:32:03 CST