Re: Hints proposal

On Monday 16 October 2006 10:36, Brian Hurt wrote:

> ... Therefor, any hints feature *will* be used widely
> and in "inappropriate" circumstances. Protestations that
> this wasn't what the feature was meant for will fall on
> deaf ears.

I don't really care about this topic, as I've used Oracle and never
actually made use of its hint system, but I liked knowing it was there.
But what's better here, asking the optimizer to use what is tested with
explain analyze to be a better plan, or to convolute a query so
horribly it's hardly recognizable, in an effort to "trick" the
optimizer?

Someone made a note earlier that any hints made irrelevant by optimizer
improvements would probably need to be removed, citing that as a
maintenence nightmare. But the same point holds for queries that have
been turned into unmaintainable spaghetti or a series of cursors to
circumvent the optimizer. Personally, I'd rather grep my code for a
couple deprecated key-words than re-check every big query between
upgrades to see if any optimizer improvements have been implemented.

Query planning is a very tough job, and SQL is a very high-level
language, making it doubly difficult to get the intended effect of a
query across to the optimizer. C allows inline assembler for exactly
this reason; sometimes the compiler is wrong about something, or
experience and testing shows a better way is available that no compiler
takes into account. As such a high-level language, SQL is inherently
flawed for performace tuning, relying almost entirely on the optimizer
knowing the best path. Here we have no recourse if the planner is just
plain wrong.

I almost wish the SQL standards committee would force syntax for sending
low-level commands to the optimizer for exactly this reason. C has
the "inline" keyword, so why can't SQL have something similar? I
agree, hints are essentially retarded comments to try and persuade the
optimizer to take a different action... what I'd actually like to see
is some way of directly addressing the query-planner's API and
circumvent SQL entirely for really nasty or otherwise convoluted
result-sets, but of course I know that's rather unreasonable.

C'mon, some of us DBAs have math degrees and know set theory... ;)

--

Shaun Thomas
Database Administrator

Leapfrog Online
807 Greenwood Street
Evanston, IL 60201
Tel. 847-440-8253
Fax. 847-570-5750
www.leapfrogonline.com