select mwebInvoiceItem_Seq.NextVal, 654, Mat_ID, Mat_Res_ID, Work_Description,
Mat_Amount, MatCat_Rate*b.AuthAttr_Amount, Mat_Date, Work_ID,
Mat_Amount* MatCat_Rate*b.AuthAttr_Amount, 1, Res_Name, Mat_Category,
from mwebMatrix, mwebWork, mwebAuthAttrib b, mwebauthattrib a, mwebRes,
where decode(6, 2, Work_Par2, 3, Work_Par3, 4, Work_Par4, 5, Work_Par5, 6,
Work_Par6, 7, Work_Par7, 8, Work_Par8, 9, Work_Par9)=2173
and Mat_Invoiced <> 10
and Work_Int_Ext8 = 2319 and Work_Int_Ext7 = 10
and b.AuthAttr_Category = 153
and (Mat_Date between to_date('2001-03-21', 'yyyy-mm-dd') and to_date('2001-04-28', 'yyyy-mm-dd'))
and (Mat_Date between a.AuthAttr_Start_Date and a.AuthAttr_Finish_Date)
and (Mat_Date between b.AuthAttr_Start_Date and b.AuthAttr_Finish_Date)
and (Mat_Category = 2500 or Mat_Category between 3000 and 3999)
and Res_ID = Mat_Res_ID and Mat_Category = MatCat_ID
and AtInt_Value <>20
and a.AuthAttr_Category = 152;
Analyzing your SQL statement is a bit complex. However, follow the basic rules:
1 Always write known column values first in the where clause
followed by join
2 If you index on date columns, add time stamp
3 Run Explain Plan and TKPROF
4 Set OPTIMIZER_MODE to CHOOSE.
5 Use HINTS where ever possible.
Sorry, I should have been more specific in my question...
Select Matrix.Mat_ID, Matrix.Mat_Res_ID, Work.Work_Description,
Matrix.Mat_Date, Matrix.Mat_Amount* MatrixCategory.MatCat_Rate*Rate.AuthAttr_Amount,
from Matrix, Work, AuthAttrib Rate, Authattrib Cost, Res,
and Rate.AuthAttr_Category = 153
and Rate.AuthAttr_Work_ID = AttribInt.AtInt_Ent_ID
and Cost.AuthAttr_Work_ID = AttribInt.AtInt_Ent_ID
and Cost.AuthAttr_Category = 152
and (Matrix.Mat_Date between Cost.AuthAttr_Start_Date and Cost.AuthAttr_Finish_Date)
and (Matrix.Mat_Date between Rate.AuthAttr_Start_Date and Rate.AuthAttr_Finish_Date)
and Res.Res_ID = Matrix.Mat_Res_ID
and Matrix.Mat_Category = MatrixCategory.MatCat_ID
and AttribInt.AtInt_Ent_ID=Matrix.Mat_Work_ID ;
First, change '---' to a 3-char tab and everything will line up wonderfully :)
My first rule of optimization is 'join everything to everything' as silly as it sounds, it solves the majority of the problems I come across. I don't have time to go into why, but check my logic to make sure I got all the joins right and then let me know if the plan changes. Also fill me in on the other questions I asked, if you would.
Sorry for the delayed reply...
I was out for a week.
Yes, you are absolutely right Chris.. about making it neat to see if you joined all tables with each other. I will do that 1st.
But, why did you say that it should start at mwebAuthattrib.
Where does optimizer start ? 1st table in where clause right, or is there any other logic ?
Here are the total number of rows in each table( this is my test database, but they should have the data in same proportion..)
mWebAuthAttrib where AuthAttr_Category= 153
mWebAuthAttrib where AuthAttr_Category= 152
COUNT(*) FROM MWEBAUTHATTRIB
select count(a.authattr_id) from mWebAuthAttrib a, mwebauthattrib b
where a.AuthAttr_Category= 152 and b.AuthAttr_Category= 153
and a.AuthAttr_Work_ID= b.AuthAttr_Work_ID
and a.AuthAttr_res_ID= b.AuthAttr_res_ID
Originally posted by sonaliak Where does optimizer start ? 1st table in where clause right, or is there any other logic ?
Unless you are still using the RBO, which you should ot be, the order of the WHERE clause is *completely* irrellevant. The CBO uses the statistics available on the tables, columns, and indexes to determine the best path with which to solve the query.
Originally posted by sonaliak And authattr_category is not indexed. Do you think I should index it ?
Not by itself - an index is only useful if it pulls less than 5% of the data - these seem to be evenly distribuyted meaning each value pulls about 25% of the data, so an index would be useless.
However, there are multiple columns in that table, wouldn't a composite index make sense here? I don't see one.
Originally posted by sonaliak Does this help you ??
Well, everything helps, but I didn't get any major revelations from this. Except that the optimizer is most likely going to use a different table order than I was thinking, but that doesn't mean much.
Okay, time to look at the plan....
Uh, okay - this makes absolutely no sense to me. First, minor point, it would be much more readable if you could post a nicely indented plan - this is hard to read. But that's not what I don't get - I don't get why the optimizer is doing what it is doing here.... Okay, let's look at this a little closer...
I just noticed that you reverted again to the old statement - this simply confuses things. I'll have to wait until I have more time to get into this again - I now have to clean up the old statement as well.