Re: Hints WAS: Index Tuning Features

On Wed, 2006-10-11 at 19:18 -0400, Mark Woodward wrote:
> >
> > Since you're the one who wants hints, that's kind of up to you to define.
> > Write a specification and make a proposal.
> >
>
> What is the point of writing a proposal if there is a threat of "will be
> rejected" if one of the people who would do the rejection doesn't at least
> outline what would be acceptable?

The general theme of other discussions has been that the best approach
is to provide additional information in a general declarative form.
Further details on that have not yet been proposed.

A hint touches a single SQL statement, so decorating 1000s of statements
with exact tips about what to do is both time consuming and eventually
inaccurate. Yet after all that work, the planner still doesn't know why
you thought the hint was the right thing to do and so the 1001st query
will perform poorly.

AFAICS hints are a legacy code compatibility issue, not something truly
desirable in the long run. Once you introduce them you must honour them
across 10+ years of releases and then you remove any chance of improved
optimisations speeding up applications in the future. Support for such
tricks is possibly a different issue from encouraging their use; if we
did support them I would welcome the day when enable_hints = off is the
default and would discourage their general use where possible.

We may be following other products in some ways, so that gives us an
opportunity to learn from both the useful lessons and the mistakes.
Deciding which is which is the hard part, IMHO.

The *right* place, IMHO, for planner information is to decorate the
tables, columns and relationships so that *every* SQL statement can pick
that up. If the world changes, you make one change and all your SQL
benefits. As the analyzers improve, you may be able to just remove those
declarations entirely but generally I imagine the DB designer will for
many years know things that cannot be determined by an analyzer.

Some might say this is a EndUserDeveloper v DBA v InternalsHacker issue
and I might agree, but would side with the DBAs on this.

I'm not aware of any research specifically in that area - though I know
many proposals have been made for various kinds of learning optimizer.
Thats dandy, but you'll still need an infrastructure to support what has
been learned and use it to override the more general analyzer info. So a
manual declarative approach seems like the first step in that direction.

So, I'm interested to hear various possible declarative approaches and
will assist where I can with that. /*+ we might be able to use some
special functions to do this, rather than more SQL */

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com