Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    China
    Posts
    2

    Question the order of query constraints

    I have a table as the following schema:

    corp char(10) <pk>
    id char(10) <pk>
    name char(10)

    this table is designed for multi-corp users, but now all the records in this table belong to a single corp, say 'ACORP'. And my query statement is as follow:

    select name from table where corp='ACORP' and id='001'

    if i change the order of the two constraints to this:

    select name from table where id='001' and corp='ACORP'

    will the time and system resource needed for this query be shortened or not, please tell me why or why not, thanks!
    Last edited by sugarwalk; 10-31-02 at 02:18.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: the order of query constraints

    Theoretically, it should make no difference. In practise, it depends on your DBMS. In Oracle, back when the optimizer was rule-based, the order of predicates made a difference; now with cost-based it should not.
    I can't comment on other DBMSs.

  3. #3
    Join Date
    Oct 2002
    Location
    China
    Posts
    2
    thanks!
    But I remembered somebody told me that the 2nd order(i.e. first use id='001' and then use corp='ACORP') will save more time and resources. His reason is: the DBMS must search twice because there are 2 constraints in that statement, and when the constraints are in this order(id,corp), the DBMS need less resource to cache matched records after the first search. if the order is (corp,id) , then the first search filter out NO records(as i said in the original post, the value of corp column of all records in this table is 'ACORP'), so after the DBMS performed the first search, it looks like nothing happened , and it need much more resources to keep all the records in cache.......
    is opinion right?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Originally posted by sugarwalk
    thanks!
    But I remembered somebody told me that the 2nd order(i.e. first use id='001' and then use corp='ACORP') will save more time and resources. His reason is: the DBMS must search twice because there are 2 constraints in that statement, and when the constraints are in this order(id,corp), the DBMS need less resource to cache matched records after the first search. if the order is (corp,id) , then the first search filter out NO records(as i said in the original post, the value of corp column of all records in this table is 'ACORP'), so after the DBMS performed the first search, it looks like nothing happened , and it need much more resources to keep all the records in cache.......
    is opinion right?
    As I said, it depends on your particular DBMS. In Oracle under the old rule-based optimizer you would actually want top put the most selective constraint LAST, because it started applying the predicates from the bottom up; now you should use the cost-based optimizer, and the order is irrelevant.
    Maybe on your particular DBMS it is better to put the most selective constraint first. But if it is, it is because of the way your particular DBMS works, not a general rule of thumb for all DBMSs. Perhaps you should re-post the question in the forum relating to your DBMS.

Posting Permissions

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