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
