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

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

It doesn't matter...because of the substring, you've got a stage 2 predicate, so it's going to have to scan.....

hehe true, I didn't even bother to look at the statement

---

/pF