Results 1 to 3 of 3

Thread: reduce time

  1. #1
    Join Date
    Feb 2004

    Cool Unanswered: reduce time

    Hi all

    if the select statment has complicated where cluse, is the order of conditions reduce the time of response?

    for ex. : there is two conditions (a and b),
    a :reduce the search area to 50%,
    b :reduce the search area to 10%

    is it better to but the condition a before b ?

    thanx in advance

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    I'd say that it is irrelevant in which order you write conditions in the WHERE clause, as the optimizer determines the order in which Oracle joins tables based on the join conditions, indexes on the tables, and, in the case of the cost-based optimization approach, statistics for the tables.

    However: do you really have such an example? Did you try to run the same query twice, having the only difference order of the WHERE conditiions? Did you gather statistics? What did TKPROF say?

    P.S. I've just found this article; it says that there is the ordered_predicates hint which is specified in the WHERE clause of a query and is used to specify the order in which Boolean predicates should be evaluated. But, in another article, I read that this hint will be (is, actually) deprecated in Oracle 10g.
    Last edited by Littlefoot; 11-20-05 at 04:49.

  3. #3
    Join Date
    Mar 2002
    Reading, UK
    I'd go with everything Littlefoot said. Never try and second guess the optimizer, try it out and see what happens. This also means you have to try it out on your production server as Oracle can alter the execution plan according to the speed and configuration of your server.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts