Re: Which of these selects is faster?

Scott,

    If you are using RULE based optimization the evaluation sequence is last to first. If you are running the RULE based optimizer you can test this for yourself by building a query that will yield different performance times depending on the order of evaluation, setting timing on, and then running the query with the AND/OR clauses in the original order and then in the reversed order.

    If you are not running RULE based optimization and your tables are being analyzed frequently enough there are two courses of action you should pursue.

1. For the average query (the 90% of your queries that will account for 30% of your system activity) just trust Oracle and leave things to the optimizer.
2. For the 10% of your queries that account for 70% of your activity conduct some tests and determine the best answers for your site's queries and data volumes. The reasons for your having to conduct your own tests include:

    o The optimizer can select different strategies for the same

        query depending on results of the last
        analyze estimate/compute statistics run.

    o    The optimizer improves with each release so that
        recommendations that are best for 7.1.4 might be
        substantially different from those for 8.0.5 or 8i

If conducting your own tests is not palatable to you either use RULE based optimization or use HINTS.

regards

Jerry Gitomer

Scott Silvey wrote in message <7f5flk$9rm$1_at_news.bayarea.net>...
>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?!
>
>: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.
>
>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.
>
>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
Received on Fri Apr 16 1999 - 09:04:40 CDT