Saving Optimizer Strategies?

I posted this to the wrong list a while back, and only got one response. I'd be interested in a more general set of comments if possible:

Given the various complexities of the optimizer, would there be any way of allowing developers to specify strategy, or give hints. This is allowed in Dec (now Oracle) RDB, and in some fashion in both SQL/Server and Sybase.

The sorts of things that might be desirable are: which indexes to join on and the order of joining the tables.

Digital Equipment Corp used to maintain that the optimizer should know best, and that developers make more mistakes, and that is true. But an experienced DBA, along with details of common complex transactions, can almost always do better than an optimizer.

Sadly, I don't know much about optimizers, but the Rdb one seems to do quite a lot of clever things, and for the most part works. But once a piece of code get sufficiently complex, the chance of 'eccentric' behavior under some circumstances increases. I presume that for the most part these special cases are found in the beta phase, and removed.

But some will still get through, and bugs will also get through. *This* is where 'hand-tuned' queries are most useful. I appreciate that as new features are added to the back end (and as tables grow), fixed query strategies are a liability. But in my view, they do have a place.

For those of you not familiar with Rdb 'Outlines' (the way it allows you to specify stretegies), it is *something* like:

1. Do an 'Explain Select...', and save the output. This is formatted nicely. (Rdb doesn't really have 'explain', but the idea is the same).

2. Edit the output to reflect the query strategy you would like to use.

3. Do a 'Create Outline...' statement using the above strategy details.

The output from step 1 also has a hash value for the input query. After step 3 is run, any query with the same hash value will invoke the outline (or at least consider it, depending on options in the 'create outline' statement).

Sanity checks are also performed at execution time to make sure the Outline makes sense in the query context.

I'm not saying this is the ideal approach, but it demonstrates at least one technique.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: +61-03-5367 7422 | _________ \
Fax: +61-03-5367 7430 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/