Results 1 to 9 of 9

Thread: Order of join condition affercts?

  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Cool Order of join condition affercts?

    I want to know if I use join with few tables, does the order of join ( or join condition) affects query performance?

    Thanks

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    eanita, Not really. Most databases determine the best way to process a query and may restructure what you supply.

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    Quote Originally Posted by SDas View Post
    eanita, Not really. Most databases determine the best way to process a query and may restructure what you supply.
    SDas, Can you please clear me what you said. I am unable to understand the above statement.

    Best Wishes,

    Sandy.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    That means sql optimizer will choose proper join order.

  5. #5
    Join Date
    Apr 2009
    Posts
    86
    Sandy, as rmiao stated, the optimizer will determine what it thinks is the best order to process a query based (mostly) on statistics. If you coded:

    FROM
    TABLE1 A
    INNER JOIN
    TABLE2 B
    ON A.COL1 = B.COL1
    INNER JOIN
    TABLE3 C
    ON B.COL1 = C.COL1

    The optimizer is smart enough to know that if A.COL1 = B.COL1 = C.COL1 then A.COL1 = C.COL1.

    With this information (and the statistics), it may determine the best access path is to start with TABLE3, join it with TABLE1 and then join this with TABLE2 (or it may determine some other order). You can use the display the Execution Plan to determine what the optimizer is doing.

  6. #6
    Join Date
    Jun 2009
    Posts
    3
    Quote Originally Posted by SDas View Post
    Sandy, as rmiao stated, the optimizer will determine what it thinks is the best order to process a query based (mostly) on statistics. If you coded:

    FROM
    TABLE1 A
    INNER JOIN
    TABLE2 B
    ON A.COL1 = B.COL1
    INNER JOIN
    TABLE3 C
    ON B.COL1 = C.COL1

    The optimizer is smart enough to know that if A.COL1 = B.COL1 = C.COL1 then A.COL1 = C.COL1.

    With this information (and the statistics), it may determine the best access path is to start with TABLE3, join it with TABLE1 and then join this with TABLE2 (or it may determine some other order). You can use the display the Execution Plan to determine what the optimizer is doing.
    SDas, Thank you for clear my concepts, but SDas Is it possible to tell the optimizer to use the order the join instead of automatically choosing the join by execution plan itself?

    Best Wishes,

    Sandy.

  7. #7
    Join Date
    Apr 2009
    Posts
    86
    Sandy, The answer is maybe. In most cases you don't want to interfere with the optimizer as it will usually make the best choice. However, once in awhile, the choice made isn't the best. I am not sure of the techniques to influence the optimizer. I have seen someone use INNER HASH JOIN (syntax may not be accurate) to force a certain type of join which may influence the join order. Another way is to add extra or modify existing WHERE clause values.

    For example, if TABLE1 is joining with TABLE3, it is probably because there is an Index involved. If you change the WHERE clause so the item is NOT indexable, it will probably use the other TABLE in the join.

    NOTE: While these techniques can work, it is best not to use them if you can help it. They would really need to be re-evaluated with new releases (or even service packs) as the cause of the 'bad' join may be fixed. If/when this happens, the technique used may no longer get the best access.

  8. #8
    Join Date
    Jun 2009
    Posts
    3
    Quote Originally Posted by SDas View Post
    Sandy, The answer is maybe. In most cases you don't want to interfere with the optimizer as it will usually make the best choice. However, once in awhile, the choice made isn't the best. I am not sure of the techniques to influence the optimizer. I have seen someone use INNER HASH JOIN (syntax may not be accurate) to force a certain type of join which may influence the join order. Another way is to add extra or modify existing WHERE clause values.

    For example, if TABLE1 is joining with TABLE3, it is probably because there is an Index involved. If you change the WHERE clause so the item is NOT indexable, it will probably use the other TABLE in the join.

    NOTE: While these techniques can work, it is best not to use them if you can help it. They would really need to be re-evaluated with new releases (or even service packs) as the cause of the 'bad' join may be fixed. If/when this happens, the technique used may no longer get the best access.
    SDas, I think you are not clear what I am looking for. I just said is the JOIN ORDER Changeable not the JOIN TYPE. yes you have the way to change the Join Type like what you said by JOIN HINTS but My question to you sdasis different than what you said. I am not sure but till now we don't have any option to change the order of join by Forcibly.

    Best Wishes,

    Sandy.
    Last edited by Sandy; 07-01-2009 at 01:49 AM.

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Why do you want to enforce that?

Posting Permissions

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