Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    13

    Unanswered: Which query will use indexes?

    Can anyone look into this query and tell if there is anyway to optimize the query by creating any index because if we see the stats the PROCMS = 42037 & PROCSU = 875752 respectively.


    SELECT FDISC_SITE_DESC, FDISC_INV_CURR_LIT, FDISC_INV_CURR_ID ,FSEUS_PAY_CRN,
    SUM(FDISC_INV_C2C_AMT),
    SUM(FDISC_INV_GLB_AMT),
    SUM(FDISC_INV_TRM_AMT),
    SUM(FDISC_INV_SPEC_AMT)
    FROM FNWC_DISCOUNTS
    ,FNWC_SERV_USAGE
    WHERE FDISC_IRN = :FDISC-IRN
    AND SUBSTR(FDISC_REF_NO,2,9) =
    SUBSTR(FSEUS_REF_NO,2,9)
    AND FDISC_PAY_CRN = FSEUS_PAY_CRN
    GROUP BY FSEUS_PAY_CRN,
    FDISC_SITE_DESC,
    FDISC_INV_CURR_LIT,
    FDISC_INV_CURR_ID

    UNION ALL

    SELECT FDISC_SITE_DESC, FDISC_INV_CURR_LIT,FDISC_INV_CURR_ID , FSEUS_PAY_CRN,
    SUM(FDISC_INV_C2C_AMT),
    SUM(FDISC_INV_GLB_AMT),
    SUM(FDISC_INV_TRM_AMT),
    SUM(FDISC_INV_SPEC_AMT)

    FROM FNWC_DISCOUNTS ,
    FNWC_SERV_USAGE
    WHERE FDISC_IRN = :FDISC-IRN
    AND SUBSTR(FDISC_REF_NO,2,9) = SUBSTR(FSEUS_REF_NO,2,9) AND (FDISC_PAY_CRN = FSEUS_PAY_CRN)
    GROUP BY FSEUS_PAY_CRN,
    DISC_SITE_DESC,
    FDISC_INV_CURR_LIT,
    FDISC_INV_CURR_ID


    We have following type2 indexes define on tables.

    XFDISCX1 F_DISCOUNTS P FDISC_REF_NO

    XFSEUSX1 F_SERV_USAGE P FSEUS_REF_NO
    XFSEUSX2 F_SERV_USAGE D FSEUS_IRN




    Which index it will use ?

    Is that concept of leading columns in the Indexes are not valid in DB2?
    Suppose we have following case
    Table Emp (col1, col2, col3,col4,col5,col6).
    indexs are I1 ON(col1, col3,col4)..

    Say in the Query we have predicates like
    1.where Col1 = a and col2 = b
    2.where col1 = a and col4 = b
    3.where col3= a and col4 = b
    4. where col1 = a and col5 = b and col6 = c
    5 where col2 = a and col3 = b and col4 = c

    can anyone tell me when it will use the index and when it will not?

    Thanks and Regards
    Vivek

  2. #2
    Join Date
    Oct 2004
    Location
    Norway
    Posts
    53
    EXPLAIN ALL SET QUERYNO = n FOR ( YOUR STATEMENT );

    ---

    This will show you the execution plan for the statement and an indication of where to put indexes.

    Normally, columns as join predicates and conditional columns in where blocks should be indexed. IF the DBMS is ment to be used as an (OLAP) system.

    Remember that any new index will slow down inserts (OLTP).

    ---

    Also, if you require a design specification for a statement, you have a schema design problem. The database is an abstract perception of real life, and should be designed thereafter. It should not be necessary to design the SQL statement after the schema, but after the usecase which is reflected in the schema.

    ---

    /pF

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It doesn't matter...because of the substring, you've got a stage 2 predicate, so it's going to have to scan.....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2004
    Location
    Norway
    Posts
    53
    hehe true, I didn't even bother to look at the statement

    ---

    /pF

Posting Permissions

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