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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings