Oracle-L: SQL statement with hints or without hints

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 12 Apr 2002 06:03:28 -0800
Message-ID: <F001.0044322A.20020412060328@fatcity.com>

Hi all,

We are running a kind of hybrid mode, mainly RBO with some tables analyzed
(mainly for intermedia). Last week we had an interesting situation, when
tuning a huge SQL, we created an index and the query worked fine, but later in the day developers complained that their queries are running slower.

We finally took out the new index and things were fine after that, but this brought out few important issues,

Developers do not put hints in their SQL statements.
They implicitly reply on a set of indexes that have worked for them.
Due to reasons of 1 and 2, no new indexes can be created because it will make their queries run slower.
As an effect of 3, new queries that can't make use of these set indexes will always be slower. Some of these queries can't be rewritten to make use of the existing indexes due to business logic involved.

My question is, do you, in your organization recommend putting hints in the SQLs all the time, some times or not at all ?? It doesn't really matter if you use RBO or CBO.

When the developers questioned me, I told them 'Oracle optimizer is not an exact science, especially in a mixed RBO/CBO mode, so it is bound to make some wrong choices and that is precisely why Oracle calls these things as "hints", so we tell the optimizer to do the right thing."

I am in a good mood to write a short note explaining developers why they
(must) use hints in their SQL statements.

Your input is greatly appreciated
TIA
Raj

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).



text/plain attachment: ESPN_Disclaimer.txt

Received on Fri Apr 12 2002 - 09:03:28 CDT