| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Which of these selects is faster?
A copy of this was sent to scott_at_baygate.bayarea.net (Scott Silvey)
(if that email address didn't require changing)
On 15 Apr 1999 19:45:56 GMT, you wrote:
>Frank van Bortel <f.van.bortel_at_vnl.nl> wrote:
>:>>If the where clause has ANDs then it is evaluated from the end of the query
>:>>towards the beginning, but if it has ORs it is evaluated starting closest to
>:>>the FROM clause and working its way downward.
>:>
>:> Thanks, it's helpful to know that things are sufficiently complex
>:> that it seems oracle did not intend for users to predict the order
>:> of evaluation. (That sucks of course, and I'd like to see them fix
>:> it so that you could at least have the option to force a certain
>:> order.)
>:>
>:> How did you come by this information? Trial and error? Oracle tech
>:> support? Actual documentation? Source code?
>:
>:It quite logical, and documented.
>
>Documented? WHERE?!
>
its documented for the procedural language PL/SQL.
In sql -- a non procedural language -- the queries are rewritten, optimized, compiled into a program of sorts. the order of evaluation of predicates is influenced by the optimizer mode (RBO vs CBO), statistics on the tables (have the indexes been analyzed, do we have histograms).
There is *no* promised order/short circuting of predicates in SQL. The non procedural sql will be converted into a query plan and the predicates will be evaulated by the optimizer in the order it feels is best.
>:For the OR's, there's no point in evaluating, after a TRUE condition
>:is found (TRUE or FALSE still is TRUE)- so this way of evaluating will
>:actually speed up things.
>
>Obviously. Check the beginning of this thread and you'll see this is
>the whole point of the thread. The problem is, to exploit this
>optimization fully, you need to know ahead of time what the order
>of evaluation is. Furthermore, you need to know that this convention
>is deterministic and consistent across all versions.
>
its not, never has been, will not be.
>If Oracle has documented the order of evaluation, then they have a
>public contract with their developers and are obligated to guarantee
>that their engine behaves accoringly.
>
only in the plsql engine is this true. predicates in sql are evaluated as the optimizer pleases.
>THIS is what I seek. But few people seem to have a solid idea what
>goes on underneath and up to now nobody has said anything about
>documentation.
>
>:As for the ANDs, I think it's been there since V4, and I _know_
>:it's still there because of V6 backward compatibility.
>
>IT's still there? What do you mean by "IT"?
>
>:I would need to check, but my best guess is the concepts manual is
>:the place to find the documentation.
>
>I would probably have to use Oracle's online documentation. Can you
>provide an exact-word phrase from a subsection heading in the general
>document so I can do a search and perhaps find it?
>
>Thanks,
>
>Scott
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |