Order in from clause

Does the order of table selection in FROM clause affact performance in any ways ?
eg FROM MWEBMATRIX, mwebAuthAttrib , mwebwork....
FROM mwebAuthAttrib , mwebwork, MWEBMATRIX...

Which is a better update statement ?

update mwebtemp set temp_amount2 = (SELECT NVL(SUM(MAT_AMOUNT*AuthAttr_Amount),0)
FROM MWEBMATRIX, mwebAuthAttrib
WHERE MAT_CATEGORY BETWEEN 2000 AND 2999
AND MAT_WORK_ID in (select WORK_ID from mwebwork where WORK_Entity_type>=3
AND Work_Par9 =TEMP_ENT_ID)
and Mat_Date Between AuthAttr_Start_Date and AuthAttr_Finish_Date
and AuthAttr_Category = 152 and Mat_Work_Id = AuthAttr_Work_ID
and Mat_Res_ID = AuthAttr_Res_ID)
WHERE TEMP_TRANCODE=charTRANCODE;


OR


update mwebtemp set temp_amount2 = (SELECT NVL(SUM(MAT_AMOUNT*AuthAttr_Amount),0)
FROM MWEBMATRIX, mwebAuthAttrib , mwebwork
WHERE MAT_CATEGORY BETWEEN 2000 AND 2999
AND MAT_WORK_ID = WORK_ID
and WORK_Entity_type>=3
AND Work_Par9 =TEMP_ENT_ID
and Mat_Date Between AuthAttr_Start_Date and AuthAttr_Finish_Date
and AuthAttr_Category = 152 and Mat_Work_Id = AuthAttr_Work_ID
and Mat_Res_ID = AuthAttr_Res_ID)
WHERE TEMP_TRANCODE=charTRANCODE;

thanks
Sonali

Quote:

---

Originally posted by sonaliak
Does the order of table selection in FROM clause affact performance in any ways ?
eg FROM MWEBMATRIX, mwebAuthAttrib , mwebwork....
FROM mwebAuthAttrib , mwebwork, MWEBMATRIX...

---

No, not in the CBO. (There are some very special cases where the order can effect things, but they are pretty much irrelevant)

However, having said that, I fully advocate ordering your FROM clause in the order you think the optimizer will use the tables. (The WHERE clause as well) This helps in both readability and ait lways keeps you thinking about the optimizer. Remember that writing a SQL statement means doing 2 things: 1) Meeting the requirements and 2) satisfying the CBO.

Quote:

---

Originally posted by sonaliak
Which is a better update statement ?

---

Statement 2.

Simply because you want to generally avoid IN (SELECT) statements. Usually, the optimizer will integrate that into the outer SELECT and things will be fine, but it is more difficult, and *sometimes* the optimizer will not get it right. It is *generally* better to keep everything *at the same level*, or in the same SELECT. This gives the optimizer the best chance of finding an optimal solution.

Having said that, however, there are times when this is a good solution. This is when the grouping changes.

IN ( SELECT DISTINCT ) will cut down the result set before getting to the outer query
IN ( SELECT...GROUP BY) does the same thing. These can be important optimization techniques. But if your sub-select does not contain either of these, then it should probably be integrated into the outer SELECT.

- Chris