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

eanita, Not really. Most databases determine the best way to process a query and may restructure what you supply.

Quote:

---

Originally Posted by SDas

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.

That means sql optimizer will choose proper join order.

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.

Quote:

---

Originally Posted by SDas

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.

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.

Quote:

---

Originally Posted by SDas

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.

Why do you want to enforce that?