query help
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: query help

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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.

  4. #4
    Join Date
    Jan 2001
    Posts
    318
    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


    Sonali

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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


  6. #6
    Join Date
    Jan 2001
    Posts
    318
    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

    Sonali

  7. #7
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

  8. #8
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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



Posting Permissions

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



Click Here to Expand Forum to Full Width