Expalin help

Tej Patil

Expalin help
Hi All,



I need help with the visual explain, which I did. Below are the results of
visual explain. Does this explain after runstats looks good? OR does this
sql needs more tuning? If yes what does it needs? Thanks in advance.



Before runstats explain



Name Value

-------------- -----------------------

Type SELECT

CPU Cost (ms) 670082066

CPU Cost (su) 2147483647

Cost Category B

Reason TABLE CARDINALITY

Timestamp 2006-01-09 15:26:21.08



After Runstats explain



Name Value

-------------- -----------------------

Type SELECT

CPU Cost (ms) 1

CPU Cost (su) 6

Cost Category A

Reason

Timestamp 2006-01-09 15:39:30.63



Below is the explain statement





SELECT A.SEQ_ID COND_SEQ_ID, A.MKTBGSA_ID, A.PRICE_DATA_ID COND_DATA_ID,
A.PRICE_DATA_TYPE, A.CPI_STATUS, A.DESCRIPTION, A.EFF_DT, A.ORIGIN_TYPE,
B.STATUS, B.CURRENT_MKT_OFFER, B.SUBS_FLAG_IND, B.LIMITATION_TYPE,
B.VIS_TMSTAMP, C.SEQ_ID COND_TYPE_ID, C.TARGET_TYPE, C.TARGET_VALUE,
C.TARGET_VALUE_DESC, C.EFF_DT TARGET_EFF_DT, C.END_DT TARGET_END_DT,
D.PLAN_SEQ_ID, D.PRICE_PLAN_ID , D.DESCRIPTION PRICE_DATA_DESC, D.I_STATUS
PRICE_DATA_STATUS

FROM B3I.TPRICE_DATA A,B3I.TVIS_COND B, B3I.TVIS_COND_TYPE C,
B3I.TPRICE_PLANS D

WHERE A.ORIGIN_TYPE != 'O'

AND A.I_STATUS NOT IN ('I CANCELLED', 'I DEPLOYED')

AND D.PLAN_SEQ_ID = B.OWNER_SEQ_ID

AND B.SEQ_ID = C.COND_SEQ_ID

AND B.SEQ_ID = A.SEQ_ID

AND ((TARGET_TYPE = 'AD' AND TARGET_VALUE = 'A')) OR (TARGET_TYPE = 'AR'
AND TARGET_VALUE = 'NE')

ORDER BY D.PRICE_PLAN_ID, COND_DATA_ID


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm