query help

[sonaliak]

Can someone help me modify this query ?

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,
Mat_Approved, a.AuthAttr_Amount
from mwebMatrix, mwebWork, mwebAuthAttrib b, mwebauthattrib a, mwebRes,
mwebMatrixCategory, mwebAttribInt
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 Work_ID=Mat_Work_ID
and Mat_Invoiced <> 10
and Work_Int_Ext8 = 2319 and Work_Int_Ext7 = 10
and b.AuthAttr_Work_ID=Work_ID
and b.AuthAttr_Res_ID=Mat_Res_ID
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_Ent_ID=Work_ID
and AtInt_Ent_ID=b.AuthAttr_Work_ID
and AtInt_Ent_ID=a.AuthAttr_Work_ID
and AtInt_Ent_ID=Mat_Work_ID
and AtInt_Category=369
and AtInt_Value <>20
and a.AuthAttr_Work_ID=Work_ID
and a.AuthAttr_Res_ID=Mat_Res_ID
and a.AuthAttr_Category = 152;

thanks

[chrisrlong]

Unfortunately, I can't even start without knowing exactly which columns come from which tables. Please prefix every column with a table name or, preferably, a meaningful table alias.

Also, what is the problem? Why are we modifying the query? For speed? If so, I would also need to know the tables sizes and indexes as well as any other information you can provied.

Thanks,

- Chris

[tamilselvan]

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.

[sonaliak]

Sorry, I should have been more specific in my question...

Select Matrix.Mat_ID, Matrix.Mat_Res_ID, Work.Work_Description,
Matrix.Mat_Amount, MatrixCategory.MatCat_Rate*Rate.AuthAttr_Amount,
Matrix.Mat_Date, Matrix.Mat_Amount* MatrixCategory.MatCat_Rate*Rate.AuthAttr_Amount,
Res.Res_Name, Cost.AuthAttr_Amount
from Matrix, Work, AuthAttrib Rate, Authattrib Cost, Res,
MatrixCategory, AttribInt
where Work.Work_ID=Matrix.Mat_Work_ID
and Rate.AuthAttr_Work_ID=Work.Work_ID
and Rate.AuthAttr_Res_ID=Matrix.Mat_Res_ID
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_Work_ID=Work_ID
and Cost.AuthAttr_Res_ID=Mat_Res_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=Work.Work_ID
and AttribInt.AtInt_Ent_ID=Matrix.Mat_Work_ID ;

Here is the execution plan..
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1 Bytes=159)
1 0 SEQUENCE OF 'MWEBINVOICEITEM_SEQ'
2 1 NESTED LOOPS (Cost=40 Card=1 Bytes=159)
3 2 NESTED LOOPS (Cost=38 Card=1 Bytes=134)
4 3 NESTED LOOPS (Cost=37 Card=1 Bytes=116)
5 4 NESTED LOOPS (Cost=36 Card=1 Bytes=112)
6 5 NESTED LOOPS (Cost=17 Card=1 Bytes=85)
7 6 NESTED LOOPS (Cost=15 Card=1 Bytes=60)
8 7 TABLE ACCESS (FULL) OF 'MWEBWORK' (Cost=13 C
ard=1 Bytes=51)

9 7 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBATTRIB
INT' (Cost=2 Card=1185 Bytes=10665)

10 9 INDEX (RANGE SCAN) OF 'IX_ATINT_ENT' (NON-
UNIQUE) (Cost=1 Card=1185)

11 6 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBAUTHATTR
IB' (Cost=2 Card=3208 Bytes=80200)

12 11 INDEX (RANGE SCAN) OF 'IX_AUTHAT_WORK' (NON-
UNIQUE) (Cost=1 Card=3208)

13 5 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBMATRIX' (C
ost=19 Card=1408 Bytes=38016)

14 13 INDEX (RANGE SCAN) OF 'IX_MAT_RES' (NON-UNIQUE
) (Cost=1 Card=1408)

15 4 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBMATRIXCATEGO
RY' (Cost=1 Card=173 Bytes=692)

16 15 INDEX (UNIQUE SCAN) OF 'SYS_C006638' (UNIQUE)
17 3 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBRES' (Cost=1 C
ard=1646 Bytes=29628)

18 17 INDEX (UNIQUE SCAN) OF 'SYS_C006666' (UNIQUE)
19 2 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBAUTHATTRIB' (Cos
t=2 Card=3208 Bytes=80200)

20 19 INDEX (RANGE SCAN) OF 'IX_AUTHAT_RES' (NON-UNIQUE) (
Cost=1 Card=3208)



I took out some where clauses from the query just to make it simple.

This query times out if there is large data.

The problem is the select statement has the columns selected from all 7 joined tables... Also the self join Authattrib table for cost and rate values.


The structure of Authattrib table is something like this..
Authattrib_id Authattrib_category Authattrib_amount col4 col5
1 153 rate_amount 1 1
2 152 cost_amount 1 1

The rest of the values for these 2 rows are same.
I could not come up with any other faster way to pull rate and cost other than doing a self join.

Can some one help to make this query better... performance.

Thanks

[chrisrlong]

Okay, this ought to be interesting :)

This is going to take more than one pass, methinks, but let's see how it goes.

First, why doesn't it start the plan at mWebAuthattrib? Is there not an index that starts with AuthAttr_Category? Is this not selective enough? If not, what % of the table does each represent.

On that thought - what are the average numeric relations between the tables? M:W 1:8, etc.

The fact that the main column is called work_id everwhere but AI has me a litle concerned about the design, but that's a separate issue.

Another thought - this query looks significantly different than the first one. Why? If you change the WHERE clause at all, the plan can change.

Oops, gotta run, but try this:

SELECT
---M.Mat_ID,
---M.Mat_Res_ID,
---W.Work_Description,
---M.Mat_Amount,
---MC.MatCat_Rate---*---AAR.AuthAttr_Amount,
---M.Mat_Date,
---M.Mat_Amount---*---MC.MatCat_Rate---*---AAR.AuthAttr_Amount,
---R.Res_Name,
---AAC.AuthAttr_Amount
FROM
---mWebMatrix------------M---,
---mWebWork---------------W---,
---mWebAuthAttrib---------AAR,
---mWebAuthAttrib---------AAC,
---mWebRes---------------R---,
---mWebMatrixCategory---MC---,
---mWebAttribInt---------AI
WHERE
---AAR.AuthAttr_Category---=---------153 ------------------------AND

---AAC.AuthAttr_Category---=---------152------------------------AND
---AAC.AuthAttr_Work_ID------=---------AAR.AuthAttr_Work_ID---------AND
---AAC.AuthAttr_Res_ID------=---------AAR.AuthAttr_Res_ID---------AND

---M.Mat_Work_ID------------=---------AAR.Mat_Work_ID------------AND
---M.Mat_Work_ID------------=---------AAC.Mat_Work_ID------------AND
---M.Mat_Res_ID ------------=---------AAR.AuthAttr_Res_ID---------AND
---M.Mat_Res_ID ------------=---------AAC.AuthAttr_Res_ID---------AND
---M.Mat_Date---------------BETWEEN---AAR.AuthAttr_Start_Date------AND
---------------------------AND------AAR.AuthAttr_Finish_Date---AND
---M.Mat_Date---------------BETWEEN---AAC.AuthAttr_Start_Date------AND
---------------------------AND------AAC.AuthAttr_Finish_Date---AND

---W.Work_ID---------------=---------AAR.AuthAttr_Work_ID---------AND
---W.Work_ID---------------=---------AAC.AuthAttr_Work_ID---------AND
---W.Work_ID---------------=---------M.Mat_Work_ID---------------AND

---AI.AtInt_Ent_ID---------=---------AAR.AuthAttr_Work_ID---------AND
---AI.AtInt_Ent_ID---------=---------AAC.AuthAttr_Work_ID---------AND
---AI.AtInt_Ent_ID---------=---------M.Mat_Work_ID---------------AND
---AI.AtInt_Ent_ID---------=---------W.Work_ID------------------AND

---R.Res_ID------------------=---------AAR.AuthAttr_Res_ID---------AND
---R.Res_ID------------------=---------AAC.AuthAttr_Res_ID---------AND
---R.Res_ID------------------=---------M.Mat_Res_ID---------------AND

---M.Mat_Category------------=---------MC.MatCat_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.

Thanks

- Chris

[sonaliak]

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..)

mwebwork
2196

mwebmatrix
57732


mWebAuthAttrib where AuthAttr_Category= 153
3410

mWebAuthAttrib where AuthAttr_Category= 152
3409

COUNT(*) FROM MWEBAUTHATTRIB
12830

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
3411

SQLWKS> select distinct authattr_category from MWEBAUTHATTRIB
2>
AUTHATTR_C
----------
137
152
153
567
4 rows selected.

And authattr_category is not indexed. Do you think I should index it ?

mwebres
1646

mWebMatrixCategory
173


mWebAttribInt
2538

Does this help you ??
I will post the information on the indexes too..

Thanks again
Sonali

[sonaliak]

Indexes information:

COLUMN_POSITION||''||TABLE_NAME||'.'||COLUMN_NAME
--------------------------------------------------------------------------------
1 MWEBATTRIBINT.ATINT_ENT_ID
1 MWEBATTRIBINT.ATINT_ID
1 MWEBAUTHATTRIB.AUTHATTR_WORK_ID
1 MWEBAUTHATTRIB.AUTHATTR_RES_ID
1 MWEBAUTHATTRIB.AUTHATTR_AUTH_ID
1 MWEBAUTHATTRIB.AUTHATTR_ID
1 MWEBMATRIX.MAT_RES_ID
1 MWEBMATRIX.MAT_ID
1 MWEBMATRIX.MAT_CATEGORY
2 MWEBMATRIX.MAT_WORK_ID
3 MWEBMATRIX.MAT_RES_ID
4 MWEBMATRIX.MAT_DATE
1 MWEBMATRIXCATEGORY.MATCAT_ID
1 MWEBRES.RES_LEVEL
1 MWEBRES.RES_ENTITY_TYPE
1 MWEBRES.RES_SEC_ID
1 MWEBRES.RES_ID
1 MWEBWORK.WORK_ENTITY_TYPE
1 MWEBWORK.WORK_LEVEL
1 MWEBWORK.WORK_SEC_ID
1 MWEBWORK.WORK_ID

[sonaliak]

Yes, you are right, some of the joins were missing.. I edited this script very carefully to add all joins.. But for a surprise the plan did not change.

SQL> Select 866,
2 M.Mat_ID,
3 M.Mat_Res_ID,
4 W.Work_Description,
5 M.Mat_Amount,
6 MC.MatCat_Rate*AAR.AuthAttr_Amount,
7 M.Mat_Date,
8 W.Work_ID,
9 M.Mat_Amount* MC.MatCat_Rate*AAR.AuthAttr_Amount,
10 R.Res_Name,
11 M.Mat_Category,
12 M.Mat_Approved,
13 AAC.AuthAttr_Amount
14 From
15 mwebMatrix M,
16 mwebWork W,
17 mwebAuthAttrib AAR,
18 mwebauthattrib AAC,
19 mwebRes R,
20 mwebMatrixCategory MC,
21 mwebAttribInt AI
22 where decode(6, 2, Work_Par2, 3, Work_Par3, 4, Work_Par4, 5, Work_Par5, 6,
23 Work_Par6, 7, Work_Par7, 8, Work_Par8, 9, Work_Par9)=912
24 and AAR.AuthAttr_Category = 153
25 and AAC.AuthAttr_Category = 152
26 and AAR.AuthAttr_Work_ID = AAC.AuthAttr_Work_ID
27 and AAR.AuthAttr_Res_ID = AAC.AuthAttr_Res_ID
28 and AAR.AuthAttr_Work_ID = W.Work_ID
29 AND AAR.AuthAttr_Res_ID = R.Res_ID
30 and AAR.AuthAttr_Work_ID = M.Mat_Work_ID
31 and AAR.AuthAttr_Res_ID = M.Mat_Res_ID
32 and AAC.AuthAttr_Work_ID = W.Work_ID
33 AND AAC.AuthAttr_Res_ID = R.Res_ID
34 and AAC.AuthAttr_Work_ID = M.Mat_Work_ID
35 and AAC.AuthAttr_Res_ID = M.Mat_Res_ID
36 and (M.Mat_Date BETWEEN AAC.AuthAttr_Start_Date and AAC.AuthAttr_Finish_Date)
37 and (M.Mat_Date BETWEEN AAR.AuthAttr_Start_Date and AAR.AuthAttr_Finish_Date)
38 and AI.AtInt_Ent_ID = AAR.AuthAttr_Work_ID
39 and AI.AtInt_Ent_ID = AAC.AuthAttr_Work_ID
40 and AI.AtInt_Ent_ID = M.Mat_Work_ID
41 and AI.AtInt_Ent_ID = W.Work_ID
42 and AI.AtInt_Category = 369
43 and AI.AtInt_Value <> 20
44 and W.Work_ID = M.Mat_Work_ID
45 and W.Work_Int_Ext7 = 10
46 and W.Work_Int_Ext8 = 852
47 and R.Res_ID = M.Mat_Res_ID
48 and M.Mat_Category = MC.MatCat_ID
49 and M.Mat_Invoiced <> 10
50 and (Mat_Date between to_date('2001-04-21' , 'yyyy-mm-dd')
51 and to_date('2001-05-04' , 'yyyy-mm-dd'))
52 and (M.Mat_Category = 2500 or M.Mat_Category between 3000 and 3999);

127 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=1 Bytes=159)
1 0 NESTED LOOPS (Cost=40 Card=1 Bytes=159)
2 1 NESTED LOOPS (Cost=38 Card=1 Bytes=134)
3 2 NESTED LOOPS (Cost=37 Card=1 Bytes=116)
4 3 NESTED LOOPS (Cost=36 Card=1 Bytes=112)
5 4 NESTED LOOPS (Cost=17 Card=1 Bytes=85)
6 5 NESTED LOOPS (Cost=15 Card=1 Bytes=60)
7 6 TABLE ACCESS (FULL) OF 'MWEBWORK' (Cost=13 Car
d=1 Bytes=51)

8 6 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBATTRIBIN
T' (Cost=2 Card=1185 Bytes=10665)

9 8 INDEX (RANGE SCAN) OF 'IX_ATINT_ENT' (NON-UN
IQUE) (Cost=1 Card=1185)

10 5 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBAUTHATTRIB
' (Cost=2 Card=3208 Bytes=80200)

11 10 INDEX (RANGE SCAN) OF 'IX_AUTHAT_WORK' (NON-UN
IQUE) (Cost=1 Card=3208)

12 4 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBMATRIX' (Cos
t=19 Card=531 Bytes=14337)

13 12 INDEX (RANGE SCAN) OF 'IX_MAT_RES' (NON-UNIQUE)
(Cost=1 Card=531)

14 3 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBMATRIXCATEGORY
' (Cost=1 Card=173 Bytes=692)

15 14 INDEX (UNIQUE SCAN) OF 'SYS_C006638' (UNIQUE)
16 2 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBRES' (Cost=1 Car
d=1646 Bytes=29628)

17 16 INDEX (UNIQUE SCAN) OF 'SYS_C006666' (UNIQUE)
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBAUTHATTRIB' (Cost=
2 Card=3208 Bytes=80200)

19 18 INDEX (RANGE SCAN) OF 'IX_AUTHAT_RES' (NON-UNIQUE) (Co
st=1 Card=3208)





Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
144401 consistent gets
0 physical reads
0 redo size
20593 bytes sent via SQL*Net to client
3347 bytes received via SQL*Net from client
12 SQL*Net roundtrips to/from client
22 sorts (memory)
0 sorts (disk)
127 rows processed

SQL>

thanks Chris for all your help
Sonali

[chrisrlong]

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.

- Chris