Re: Where can I get a complete list of all SQL Hints?

Hans Forbrich <forbrich_at_telusplanet.net> wrote in message news:<3EF31F96.D6D756D5_at_telusplanet.net>...
> Telemachus wrote:
>
> > There is nothing wrong with research; a little work by the poster would have
> > taken him to a useful thread on this NG entitled 'Hash Join Order'
> > which kicked off 27/3/03 which discussed these things at the end and was
> > contributed to by several luminaries (connor and jonathan for two ) .
> >
>
> I agree with your sentiment.

Me too!

>
> All I suggest is covering the request for information with a statement that this
> is for information & research purposes. There are too many people who will try
> stuff they've seen in newsgroups because 'an expert recommended it', and
> undocumented hints are in the 'potentially dangerous for the health of the
> database' category.

Some people even try stuff just because it is posted. I still chuckle that people actually tried an "insert into dual" joke I posted in a sig - although it is laudable that they are curious enough to try such, er, magic to see what it will do. But more relevant to this discussion, it illustrated that even gross distortions of the db may not be entirely obvious as to its cause or effects. And when you get into subtleties like optimizer defaults, there's no way anyone can evaluate their effects properly. Heck, if _Oracle_ could, they wouldn't have to _have_ undoc'd hints.

>
> > My own research shows there is little on the web and less on metalink
> > devoted to explaining undocumented hints.
>
> Undocumented hints are undocumented for a few reasons. As far as I can tell
> these hints drive code that is effectively in beta (or at least under
> development evaluation) and should not be used yet, unless explicity advised by
> a group (Oracle Support) who will take responsibility for their use.
>
> I'm not sure why people expect there to be documentation for something we are
> not supposed to know about <g>

No <g> here. We all suspect that somewhere in Oracle Corp. there is some justification for it, otherwise it wouldn't be there. Developers don't work for free, and development management on large projects (and O is the largest, right?) have to be able to justify their people's hours (although one would hope at the deepest levels someone gets some freedom to hack). I would be very surprised if anything gets to the real world these days just from something locked up in someone's head - although of course I wouldn't say the same about some unix OS's, for example. (sync; sync; sync; <g>) As I think this through, maybe stuff like that _does_ happen and increasing the Oracle major version with a major rewrite is some attempt to fix it (as opposed to marketting BS - of course, I have strange ideas like 7.2 --> 7.3 should have been a major release, and some major releases should have been minor...). But more likely, someone has just made the judgement call that it's cheaper to rewrite than maintain and support. So there must be some doc somewhere, it's just not considered customer-friendly. Also, some of the notes on metalink seem to indicate there is a whole level of internal docs about this stuff.

>
> I'm also don't understand why most people would spend the effort/time to study
> something like this, which can change at any time with the release of patches.
> The Oracle product set is so big, a person can easily spend years in thoroughly
> learning the 'legitimate' areas. (Operative word "most" excludes the Connors
> and Jonathans of our world.)

I would think we would all want to emulate the Connors and Jonathons of the world, I would hope they aren't qualitatively different in their thought processes (not that we are all exactly the same...). But as a practical matter you are right, most people are expected to produce something, and that something isn't usually a book on practical uses of undocumented features.

However, I think the pendulum swinging completely away from a hacker mentality isn't a good thing either (well, except for people selling GUI tools). The trick is being able to know when to hack and when to use proper formal methodologies - and you can't get there without doing both. Yes, there is a danger of people inappropriately using hacks - but I believe that is better than them believing in magic.

I have been wondering if hints are going to continue to be the way of Oracle. Anybody care to hazard a guess? (Or point me towards something that says if I've missed something). The reason I ask is I work with a language that kind of sticks the users with RBO, partly because there is no way to pass through hints. (It generates and executes OCI code, so one could turn on CBO and hope for the best, but one could expect some situations would necessarily require a hint - I've asked the vendor to give the language a mechanism to pass through hints, though I have so little memory left of OCI I don't even know if it can).

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/fri/news/news_1n20irs.html