View previous topic :: View next topic
|
Author |
Message |
nileshyp
New User
Joined: 22 Jun 2005 Posts: 65 Location: Mumbai
|
|
|
|
Hi,
I need help to finetune query which is currently giving cartesian product.
The query is as follows :-
Code: |
SELECT A.PRD_NO
,A.PRD_LNG_NAM
,A.PRD_ADD_STR_NAM
,A.PRD_CTY_NAM
,A.PRD_ST_CD
,A.PRD_ZIP_CD
,A.PRD_PHN_NO
,A.PRD_TYP_CD
,A.PRD_BUS_STC_CD
,A.PRD_TX_NO
,B.PRD_HIS_CHG_TXT
,C.CO_CD
,C.PRD_STA_CD
,C.PRD_APT_DT
,C.PRD_CLS_DT
,C.PRD_SUS_DT
,C.PRD_RNS_DT
,C.PRD_WC_STA_CD
,C.PRD_WC_CLS_DT
,C.PRD_WC_SUS_DT
,D.PFT_CTR_CD
,D.BR_OFC_CD
,E.SRV_OFC_CD
,E.SRV_OFC_NAM
FROM DB2DBA.PH01T19_PRODUCR A, DB2DBA.PH01T17_PRDHIST B,
DB2DBA.PH01T05_COSTAT C,
DB2DBA.PH01T02_BRANCH D,
DB2DBA.PH01T22_SRVOFC E
WHERE A.PRD_NO = B.PRD_NO
AND A.PFT_CTR_CD = B.PFT_CTR_CD
AND B.PFT_CTR_CD = C.PFT_CTR_CD
AND C.PFT_CTR_CD = D.PFT_CTR_CD
AND D.PFT_CTR_CD = E.PFT_CTR_CD
AND C.PRD_APT_DT <= '2000-01-01';
|
The common column names are
PFT_CTR_CD
PRD_NO
in below 3 tables
PH01T19_PRODUCR
PH01T05_COSTAT
PH01T17_PRDHIST
and PFT_CTR_CD column in all 5 tables used in query.
Please help or advise if I need to modify joins in order to get correct results.
Thanks in advance.
Neil |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Are those fields used in where clause part of Unique index ?? |
|
Back to top |
|
|
nileshyp
New User
Joined: 22 Jun 2005 Posts: 65 Location: Mumbai
|
|
|
|
Not sure. but PFT_CTR_CD column is common in all 5 tables. tht's y I used that one in join. |
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
You should have some tools like DB2 Admin tool, Platinum or BMC where you could check the indexes of a table
Or Easier way to sit along DBA and understand the query what it does with use of explain |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Is that how you write joins : Just take some random columns and put those in the where clause ?
Normally tables joined have some kind of relationship, it helps when you know these. |
|
Back to top |
|
|
Naish
New User
Joined: 07 Dec 2006 Posts: 82 Location: UK
|
|
|
|
Or if you don't have any 'Tools', try this in SPUFI:
Code: |
SELECT NAME, CREATOR, TBNAME, TBCREATOR, UNIQUERULE, COLCOUNT,
CLUSTERING, CLUSTERED, DBID, OBID , ISOBID, DBNAME, INDEXSPACE,
FIRSTKEYCARD, FULLKEYCARD, NLEAF, NLEVELS, BPOOL, PGSIZE, ERASERULE,
DSETPASS, CLOSERULE, SPACE, IBMREQD, CLUSTERRATIO, CREATEDBY, IOFACTOR
PREFETCHFACTOR, STATSTIME, INDEXTYPE, FIRSTKEYCARDF, FULLKEYCARDF,
CREATEDTS, ALTEREDTS, PIECESIZE, COPY, COPYLRSN, CLUSTERRATIOF,
SPACEF, REMARKS, PADDED, VERSION, OLDEST_VERSION, CURRENT_VERSION,
RELCREATED, AVGKEYLEN, KEYTARGET_COUNT, UNIQUE_COUNT,
IX_EXTENSION_TYPE, COMPRESS, OWNER, OWNERTYPE, DATAREPEATFACTORF, ENVID
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = 'DB2DBA'
AND TBNAME IN ('PH01T19_PRODUCR', 'PH01T17_PRDHIST', 'PH01T05_COSTAT', 'PH01T02_BRANCH', 'PH01T22_SRVOFC');
|
|
|
Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006 Posts: 1592 Location: Andromeda Galaxy
|
|
|
|
Naish wrote: |
Or if you don't have any 'Tools', try this in SPUFI:
Code: |
SELECT NAME, CREATOR, TBNAME, TBCREATOR, UNIQUERULE, COLCOUNT,
CLUSTERING, CLUSTERED, DBID, OBID , ISOBID, DBNAME, INDEXSPACE,
FIRSTKEYCARD, FULLKEYCARD, NLEAF, NLEVELS, BPOOL, PGSIZE, ERASERULE,
DSETPASS, CLOSERULE, SPACE, IBMREQD, CLUSTERRATIO, CREATEDBY, IOFACTOR
PREFETCHFACTOR, STATSTIME, INDEXTYPE, FIRSTKEYCARDF, FULLKEYCARDF,
CREATEDTS, ALTEREDTS, PIECESIZE, COPY, COPYLRSN, CLUSTERRATIOF,
SPACEF, REMARKS, PADDED, VERSION, OLDEST_VERSION, CURRENT_VERSION,
RELCREATED, AVGKEYLEN, KEYTARGET_COUNT, UNIQUE_COUNT,
IX_EXTENSION_TYPE, COMPRESS, OWNER, OWNERTYPE, DATAREPEATFACTORF, ENVID
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = 'DB2DBA'
AND TBNAME IN ('PH01T19_PRODUCR', 'PH01T17_PRDHIST', 'PH01T05_COSTAT', 'PH01T02_BRANCH', 'PH01T22_SRVOFC');
|
|
Name ,TBNAME and unique rule should suffice |
|
Back to top |
|
|
senthilnathanj
New User
Joined: 31 Jul 2007 Posts: 47 Location: chennai
|
|
|
|
Hi,
Create a plan_table table and execute a query using EXPLAIN statement. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
senthilnathanj wrote: |
Hi,
Create a plan_table table and execute a query using EXPLAIN statement. |
the first thing that should have been done!
again,
a question posed without first gathering facts, by someone who just does not understand the subject. |
|
Back to top |
|
|
|
|