Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Where can I get a complete list of all SQL Hints?

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

From: Joel Garry <joel-garry_at_home.com>
Date: 1 Jul 2003 12:45:06 -0700
Message-ID: <91884734.0307011145.21ed8c39@posting.google.com>


"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:<3ef4458c$0$964$cc9e4d1f_at_news.dial.pipex.com>...
> "Joel Garry" <joel-garry_at_home.com> wrote in message
> news:91884734.0306201312.45101570_at_posting.google.com...

>
> > 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.
>
> I'd certainly hazard that the answer is yes. For the following reasons.
>
> 1. Hints are already been used for features and database internals
> example 1. Plan Stability.

Well, that is more of a consequence of the existence of hints than a determinant?

> example 2. DBA_FREE_SPACE which in 9.0.1 (and I guess from 8i in fact) has
> the definition below
>
> select ts.name, fi.file#, f.block#,
> f.length * ts.blocksize, f.length, f.file#
> from sys.ts$ ts, sys.fet$ f, sys.file$ fi
> where ts.ts# = f.ts#
> and f.ts# = fi.ts#
> and f.file# = fi.relfile#
> and ts.bitmapped = 0
> union all
> select /*+ ordered use_nl(f) use_nl(fi) */
> ts.name, fi.file#, f.ktfbfebno,
> f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
> from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
> where ts.ts# = f.ktfbfetsn
> and f.ktfbfetsn = fi.ts#
> and f.ktfbfefno = fi.relfile#
> and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
>

Same as 1, it doesn't strike me as causal.

> 2. Hints are widely used (some might say abused) by Oracle customers.

Well, remember .inp files? :-)

>
> 3. I suspect that determining the optimal execution plan is an example of
> the halting problem. That is it *may* always be possible to determine the
> best execution plan by a method other than trying all of them out and
> picking the fastest (which could take an infinite amount of time), but I
> doubt it.

Agreed!
>
> The first two reasons I give suggest that removing hints will be hard both
> for Oracle and its customers. The third suggestion might explain why the two
> methods used so far (use a fixed set of rules for evaluating possible access
> paths and find as many access paths as possible within a given timeout and
> assign a number to them based on expected IO) essentially try to short
> circuit the problem. If determining optimal execution plans is an
> undecideable problem then we are bound to have some sort of short circuit
> method, for a new one to be adopted over the ones we have now it would have
> to show significant benefits (and almost certainly coexist for a while).

Ah, so this leads one to question whether it should be a standards issue, as the problem is general. Should it be in SQL? Not having followed the standards issues for many years, I have no idea whether that has been addressed. I can certainly understand it if a committee were to simply say it is too hard. And of course, Oracle would have been against it because it isn't hints.

>
> This last leads me at least to speculate that the system statistics
> introduced in 9i (so probably useful in 10ir2) are a way forward that Oracle
> will develop. This and the likelihood of more clever data structures and
> access paths IMO tend to suggest that there will be more hints rather than
> an elimination of them.

Agreed. I'm sure it's going to be a looong time before various db vendors could agree on a syntax for some things they all will wind up doing several ways, some of which may be patented. And even longer before it makes sense not to have a manual override.

jg

--
@home.com is bogus.
Usenet is a mechanism for bringing people into your office whom you
wouldn't have in your living room.
Received on Tue Jul 01 2003 - 14:45:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US