Is there a way to influence the order in which the DB2 Optimizer will join tables?

Penny Treptow

Is there a way to influence the order in which the DB2 Optimizer will join tables?
The following five way join query runs 'forever'. However, if I remove
the PS_UAG_DIVISION_CD, which
contains only 85 rows and is used to get the most current division title
to avoid more
than one output line for the same division, the four way join query runs
in about 2 minutes. I'd like to 'influence' the Optimizer
to join the PS_UAG_DIVISION_CD last.

The original query is:

SELECT E.UA_DIVISION_CD
,V.DESCR
,E.DEPTID
,E.DESCR AS DEPTNAME
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR AS JOBTITLE
,COUNT(*) AS EMPLCNTMON
FROM DBP3PRD.PS_UAE_EMPL_NS_VW A,
DBP3PRD.PS_JOBCODE_TBL B,
DBP3PRD.PS_JOB C,
DBP3PRD.PS_DEPT_TBL E,
DBP3PRD.PS_UAG_DIVISION_CD V
WHERE A.EMPLID = C.EMPLID
AND C.EMPL_STATUS = 'A'
AND C.EMPL_RCD# = 0
AND C.EFFDT = (SELECT MAX(F.EFFDT)
FROM DBP3PRD.PS_JOB F
WHERE C.EMPLID = F.EMPLID
AND C.EMPL_RCD# = F.EMPL_RCD#
AND F.EFFDT <= '2001-12-01')
AND C.EFFSEQ = (SELECT MAX(G.EFFSEQ)
FROM DBP3PRD.PS_JOB G
WHERE C.EMPLID = G.EMPLID
AND C.EMPL_RCD# = G.EMPL_RCD#
AND C.EFFDT = G.EFFDT)
AND B.SETID = 'UALID'
AND B.JOBCODE = C.JOBCODE
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM DBP3PRD.PS_JOBCODE_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.JOBCODE = B_ED.JOBCODE
AND B_ED.EFFDT <= C.EFFDT)
AND C.DEPTID = E.DEPTID
AND C.SETID_DEPT = E.SETID
AND E.EFFDT = (SELECT MAX(I.EFFDT)
FROM DBP3PRD.PS_DEPT_TBL I
WHERE C.DEPTID = I.DEPTID
AND C.SETID_DEPT = I.SETID
AND I.EFFDT <= C.EFFDT)
-** DIVISION TITLE. USE MOST CURRENT DIVISION TITLE
-** TO AVOID MORE THAN ONE LINE OUTPUT FOR SAME
DIVISION.
AND V.UA_DIVISION_CD = E.UA_DIVISION_CD
AND V.SETID = E.SETID
AND V.EFFDT = (SELECT MAX(X.EFFDT)
FROM DBP3PRD.PS_UAG_DIVISION_CD X
WHERE X.UA_DIVISION_CD = V.UA_DIVISION_CD
AND X.SETID = V.SETID
AND X.EFFDT <= CURRENT DATE)
GROUP BY E.UA_DIVISION_CD
,V.DESCR
,E.DEPTID
,E.DESCR
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR
;

The "bad" access path is:

|A = Query No
B = QBLOCKNO
C = PLANNO
D = Query Block Type
E = Access Method
F = Creator
G = Table
H = Table NO
I = Corr Name
J = Access Type
K = Match Cols
A B C D E F G
H I J K
20901 1 1| SELECT 0 DBP3PRD PS_UAG_DIVISION_CD 5 V R 0
1 2| SELECT 1 DBP3PRD PS_DEPT_TBL 4 E
R 0
1 3| SELECT 1 DBP3PRD PS_JOB
3 C I 1
1 4| SELECT 1 DBP3PRD PS_EMPLOYEES 1
I 1
1 5| SELECT 1 DBP3PRD PS_JOBCODE_TBL 2 B
I 2
1 6| SELECT 3
0 0
2 1| CORSUB 0 DBP3PRD PS_JOB
6 F I1 3
3 1| CORSUB 0 DBP3PRD PS_JOB
7 G I1 3
4 1| CORSUB 0 DBP3PRD PS_JOBCODE_TBL 8 B_ED I1
3
5 1| CORSUB 0 DBP3PRD PS_DEPT_TBL 9 I
I1 3
6 1| CORSUB 0 DBP3PRD PS_UAG_DIVISION_CD 10 X I
3

The revised query is:

SELECT E.UA_DIVISION_CD
,E.DEPTID
,E.DESCR AS DEPTNAME
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR AS JOBTITLE
,COUNT(*) AS EMPLCNTMON
FROM DBP3PRD.PS_UAE_EMPL_NS_VW A,
DBP3PRD.PS_JOBCODE_TBL B,
DBP3PRD.PS_JOB C,
DBP3PRD.PS_DEPT_TBL E
WHERE A.EMPLID = C.EMPLID
AND C.EMPL_STATUS = 'A'
AND C.EMPL_RCD# = 0
AND C.EFFDT = (SELECT MAX(F.EFFDT)
FROM DBP3PRD.PS_JOB F
WHERE C.EMPLID = F.EMPLID
AND C.EMPL_RCD# = F.EMPL_RCD#
AND F.EFFDT <= '2001-12-01')
AND C.EFFSEQ = (SELECT MAX(G.EFFSEQ)
FROM DBP3PRD.PS_JOB G
WHERE C.EMPLID = G.EMPLID
AND C.EMPL_RCD# = G.EMPL_RCD#
AND C.EFFDT = G.EFFDT)
AND B.SETID = 'UALID'
AND B.JOBCODE = C.JOBCODE
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM DBP3PRD.PS_JOBCODE_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.JOBCODE = B_ED.JOBCODE
AND B_ED.EFFDT <= C.EFFDT)
AND C.DEPTID = E.DEPTID
AND C.SETID_DEPT = E.SETID
AND E.EFFDT = (SELECT MAX(I.EFFDT)
FROM DBP3PRD.PS_DEPT_TBL I
WHERE C.DEPTID = I.DEPTID
AND C.SETID_DEPT = I.SETID
AND I.EFFDT <= C.EFFDT)
GROUP BY E.UA_DIVISION_CD
,E.DEPTID
,E.DESCR
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR

The "good" access path is:

A = Query No
B = QBLOCKNO
C = PLANNO
D = Query Block Type
E = Access Method
F = Creator
G = Table
H = Table NO
I = Corr Name
J = Access Type
K = Match Cols
A B C D E F G
H I J K
122003 1 1| SELECT 0 DBP3PRD PS_JOB 3
C R 0
1 2| SELECT 1 DBP3PRD PS_DEPT_TBL 4 E
I 2
1 3| SELECT 1 DBP3PRD PS_EMPLOYEES 1
I 1
1 4| SELECT 1 DBP3PRD PS_JOBCODE_TBL 2 B
I 2
1 5| SELECT 3 0
0
2 1| CORSUB 0 DBP3PRD PS_JOB
5 F I1 3
3 1| CORSUB 0 DBP3PRD PS_JOB
6 G I1 3
4 1| CORSUB 0 DBP3PRD PS_JOBCODE_TBL 7 B_ED
I1 3
5 1| CORSUB 0 DBP3PRD PS_DEPT_TBL 8 I
I1 3



Jeremy Schleicher

Re: Is there a way to influence the order in which the DB2 Optimizer will join tables?
(in response to Penny Treptow)
THe best ways that I know how is to run a visual explain and try to create
indexes accordingly. Try running your statement in the index adviser and
see what that tells you.



Jeremy Schleicher
System DBA
May Department Stores Company



From: Penny Treptow <[login to unmask email]>@RYCI.COM on 12/24/2001 09:09 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: Is there a way to influence the order in which the DB2 Optimizer
will join tables?


The following five way join query runs 'forever'. However, if I remove
the PS_UAG_DIVISION_CD, which
contains only 85 rows and is used to get the most current division title
to avoid more
than one output line for the same division, the four way join query runs
in about 2 minutes. I'd like to 'influence' the Optimizer
to join the PS_UAG_DIVISION_CD last.

The original query is:

SELECT E.UA_DIVISION_CD
,V.DESCR
,E.DEPTID
,E.DESCR AS DEPTNAME
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR AS JOBTITLE
,COUNT(*) AS EMPLCNTMON
FROM DBP3PRD.PS_UAE_EMPL_NS_VW A,
DBP3PRD.PS_JOBCODE_TBL B,
DBP3PRD.PS_JOB C,
DBP3PRD.PS_DEPT_TBL E,
DBP3PRD.PS_UAG_DIVISION_CD V
WHERE A.EMPLID = C.EMPLID
AND C.EMPL_STATUS = 'A'
AND C.EMPL_RCD# = 0
AND C.EFFDT = (SELECT MAX(F.EFFDT)
FROM DBP3PRD.PS_JOB F
WHERE C.EMPLID = F.EMPLID
AND C.EMPL_RCD# = F.EMPL_RCD#
AND F.EFFDT <= '2001-12-01')
AND C.EFFSEQ = (SELECT MAX(G.EFFSEQ)
FROM DBP3PRD.PS_JOB G
WHERE C.EMPLID = G.EMPLID
AND C.EMPL_RCD# = G.EMPL_RCD#
AND C.EFFDT = G.EFFDT)
AND B.SETID = 'UALID'
AND B.JOBCODE = C.JOBCODE
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM DBP3PRD.PS_JOBCODE_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.JOBCODE = B_ED.JOBCODE
AND B_ED.EFFDT <= C.EFFDT)
AND C.DEPTID = E.DEPTID
AND C.SETID_DEPT = E.SETID
AND E.EFFDT = (SELECT MAX(I.EFFDT)
FROM DBP3PRD.PS_DEPT_TBL I
WHERE C.DEPTID = I.DEPTID
AND C.SETID_DEPT = I.SETID
AND I.EFFDT <= C.EFFDT)
-** DIVISION TITLE. USE MOST CURRENT DIVISION TITLE
-** TO AVOID MORE THAN ONE LINE OUTPUT FOR SAME
DIVISION.
AND V.UA_DIVISION_CD = E.UA_DIVISION_CD
AND V.SETID = E.SETID
AND V.EFFDT = (SELECT MAX(X.EFFDT)
FROM DBP3PRD.PS_UAG_DIVISION_CD X
WHERE X.UA_DIVISION_CD = V.UA_DIVISION_CD
AND X.SETID = V.SETID
AND X.EFFDT <= CURRENT DATE)
GROUP BY E.UA_DIVISION_CD
,V.DESCR
,E.DEPTID
,E.DESCR
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR
;

The "bad" access path is:

|A = Query No
B = QBLOCKNO
C = PLANNO
D = Query Block Type
E = Access Method
F = Creator
G = Table
H = Table NO
I = Corr Name
J = Access Type
K = Match Cols
A B C D E F G
H I J K
20901 1 1| SELECT 0 DBP3PRD PS_UAG_DIVISION_CD 5 V R 0
1 2| SELECT 1 DBP3PRD PS_DEPT_TBL 4 E
R 0
1 3| SELECT 1 DBP3PRD PS_JOB
3 C I 1
1 4| SELECT 1 DBP3PRD PS_EMPLOYEES 1
I 1
1 5| SELECT 1 DBP3PRD PS_JOBCODE_TBL 2 B
I 2
1 6| SELECT 3
0 0
2 1| CORSUB 0 DBP3PRD PS_JOB
6 F I1 3
3 1| CORSUB 0 DBP3PRD PS_JOB
7 G I1 3
4 1| CORSUB 0 DBP3PRD PS_JOBCODE_TBL 8 B_ED I1
3
5 1| CORSUB 0 DBP3PRD PS_DEPT_TBL 9 I
I1 3
6 1| CORSUB 0 DBP3PRD PS_UAG_DIVISION_CD 10 X I
3

The revised query is:

SELECT E.UA_DIVISION_CD
,E.DEPTID
,E.DESCR AS DEPTNAME
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR AS JOBTITLE
,COUNT(*) AS EMPLCNTMON
FROM DBP3PRD.PS_UAE_EMPL_NS_VW A,
DBP3PRD.PS_JOBCODE_TBL B,
DBP3PRD.PS_JOB C,
DBP3PRD.PS_DEPT_TBL E
WHERE A.EMPLID = C.EMPLID
AND C.EMPL_STATUS = 'A'
AND C.EMPL_RCD# = 0
AND C.EFFDT = (SELECT MAX(F.EFFDT)
FROM DBP3PRD.PS_JOB F
WHERE C.EMPLID = F.EMPLID
AND C.EMPL_RCD# = F.EMPL_RCD#
AND F.EFFDT <= '2001-12-01')
AND C.EFFSEQ = (SELECT MAX(G.EFFSEQ)
FROM DBP3PRD.PS_JOB G
WHERE C.EMPLID = G.EMPLID
AND C.EMPL_RCD# = G.EMPL_RCD#
AND C.EFFDT = G.EFFDT)
AND B.SETID = 'UALID'
AND B.JOBCODE = C.JOBCODE
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM DBP3PRD.PS_JOBCODE_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.JOBCODE = B_ED.JOBCODE
AND B_ED.EFFDT <= C.EFFDT)
AND C.DEPTID = E.DEPTID
AND C.SETID_DEPT = E.SETID
AND E.EFFDT = (SELECT MAX(I.EFFDT)
FROM DBP3PRD.PS_DEPT_TBL I
WHERE C.DEPTID = I.DEPTID
AND C.SETID_DEPT = I.SETID
AND I.EFFDT <= C.EFFDT)
GROUP BY E.UA_DIVISION_CD
,E.DEPTID
,E.DESCR
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR

The "good" access path is:

A = Query No
B = QBLOCKNO
C = PLANNO
D = Query Block Type
E = Access Method
F = Creator
G = Table
H = Table NO
I = Corr Name
J = Access Type
K = Match Cols
A B C D E F G
H I J K
122003 1 1| SELECT 0 DBP3PRD PS_JOB 3
C R 0
1 2| SELECT 1 DBP3PRD PS_DEPT_TBL 4 E
I 2
1 3| SELECT 1 DBP3PRD PS_EMPLOYEES 1
I 1
1 4| SELECT 1 DBP3PRD PS_JOBCODE_TBL 2 B
I 2
1 5| SELECT 3 0
0
2 1| CORSUB 0 DBP3PRD PS_JOB
5 F I1 3
3 1| CORSUB 0 DBP3PRD PS_JOB
6 G I1 3
4 1| CORSUB 0 DBP3PRD PS_JOBCODE_TBL 7 B_ED
I1 3
5 1| CORSUB 0 DBP3PRD PS_DEPT_TBL 8 I
I1 3








Isaac Yassin

Re: Is there a way to influence the order in which the DB2 Optimizer will
(in response to Jeremy Schleicher)
Hi,
Try to use NTE with outer join in which PS_UAG_DIVISION_CD is the second table(right) and the other 4 are the first(left).
Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Penny Treptow" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, December 24, 2001 5:09 PM
Subject: Is there a way to influence the order in which the DB2 Optimizer will join tables?


> The following five way join query runs 'forever'. However, if I remove
> the PS_UAG_DIVISION_CD, which
> contains only 85 rows and is used to get the most current division title
> to avoid more
> than one output line for the same division, the four way join query runs
> in about 2 minutes. I'd like to 'influence' the Optimizer
> to join the PS_UAG_DIVISION_CD last.
>
> The original query is:
>
> SELECT E.UA_DIVISION_CD
> ,V.DESCR
> ,E.DEPTID
> ,E.DESCR AS DEPTNAME
> ,B.UA_OPS_CD
> ,B.JOBCODE
> ,B.DESCR AS JOBTITLE
> ,COUNT(*) AS EMPLCNTMON
> FROM DBP3PRD.PS_UAE_EMPL_NS_VW A,
> DBP3PRD.PS_JOBCODE_TBL B,
> DBP3PRD.PS_JOB C,
> DBP3PRD.PS_DEPT_TBL E,
> DBP3PRD.PS_UAG_DIVISION_CD V
> WHERE A.EMPLID = C.EMPLID
> AND C.EMPL_STATUS = 'A'
> AND C.EMPL_RCD# = 0
> AND C.EFFDT = (SELECT MAX(F.EFFDT)
> FROM DBP3PRD.PS_JOB F
> WHERE C.EMPLID = F.EMPLID
> AND C.EMPL_RCD# = F.EMPL_RCD#
> AND F.EFFDT <= '2001-12-01')
> AND C.EFFSEQ = (SELECT MAX(G.EFFSEQ)
> FROM DBP3PRD.PS_JOB G
> WHERE C.EMPLID = G.EMPLID
> AND C.EMPL_RCD# = G.EMPL_RCD#
> AND C.EFFDT = G.EFFDT)
> AND B.SETID = 'UALID'
> AND B.JOBCODE = C.JOBCODE
> AND B.EFFDT =
> (SELECT MAX(B_ED.EFFDT) FROM DBP3PRD.PS_JOBCODE_TBL B_ED
> WHERE B.SETID = B_ED.SETID
> AND B.JOBCODE = B_ED.JOBCODE
> AND B_ED.EFFDT <= C.EFFDT)
> AND C.DEPTID = E.DEPTID
> AND C.SETID_DEPT = E.SETID
> AND E.EFFDT = (SELECT MAX(I.EFFDT)
> FROM DBP3PRD.PS_DEPT_TBL I
> WHERE C.DEPTID = I.DEPTID
> AND C.SETID_DEPT = I.SETID
> AND I.EFFDT <= C.EFFDT)
> -** DIVISION TITLE. USE MOST CURRENT DIVISION TITLE
> -** TO AVOID MORE THAN ONE LINE OUTPUT FOR SAME
> DIVISION.
> AND V.UA_DIVISION_CD = E.UA_DIVISION_CD
> AND V.SETID = E.SETID
> AND V.EFFDT = (SELECT MAX(X.EFFDT)
> FROM DBP3PRD.PS_UAG_DIVISION_CD X
> WHERE X.UA_DIVISION_CD = V.UA_DIVISION_CD
> AND X.SETID = V.SETID
> AND X.EFFDT <= CURRENT DATE)
> GROUP BY E.UA_DIVISION_CD
> ,V.DESCR
> ,E.DEPTID
> ,E.DESCR
> ,B.UA_OPS_CD
> ,B.JOBCODE
> ,B.DESCR
> ;
>
> The "bad" access path is:
>
> |A = Query No
> B = QBLOCKNO
> C = PLANNO
> D = Query Block Type
> E = Access Method
> F = Creator
> G = Table
> H = Table NO
> I = Corr Name
> J = Access Type
> K = Match Cols
> A B C D E F G
> H I J K
> 20901 1 1| SELECT 0 DBP3PRD PS_UAG_DIVISION_CD 5 V R 0
> 1 2| SELECT 1 DBP3PRD PS_DEPT_TBL 4 E
> R 0
> 1 3| SELECT 1 DBP3PRD PS_JOB
> 3 C I 1
> 1 4| SELECT 1 DBP3PRD PS_EMPLOYEES 1
> I 1
> 1 5| SELECT 1 DBP3PRD PS_JOBCODE_TBL 2 B
> I 2
> 1 6| SELECT 3
> 0 0
> 2 1| CORSUB 0 DBP3PRD PS_JOB
> 6 F I1 3
> 3 1| CORSUB 0 DBP3PRD PS_JOB
> 7 G I1 3
> 4 1| CORSUB 0 DBP3PRD PS_JOBCODE_TBL 8 B_ED I1
> 3
> 5 1| CORSUB 0 DBP3PRD PS_DEPT_TBL 9 I
> I1 3
> 6 1| CORSUB 0 DBP3PRD PS_UAG_DIVISION_CD 10 X I
> 3
>
> The revised query is:
>
> SELECT E.UA_DIVISION_CD
> ,E.DEPTID
> ,E.DESCR AS DEPTNAME
> ,B.UA_OPS_CD
> ,B.JOBCODE
> ,B.DESCR AS JOBTITLE
> ,COUNT(*) AS EMPLCNTMON
> FROM DBP3PRD.PS_UAE_EMPL_NS_VW A,
> DBP3PRD.PS_JOBCODE_TBL B,
> DBP3PRD.PS_JOB C,
> DBP3PRD.PS_DEPT_TBL E
> WHERE A.EMPLID = C.EMPLID
> AND C.EMPL_STATUS = 'A'
> AND C.EMPL_RCD# = 0
> AND C.EFFDT = (SELECT MAX(F.EFFDT)
> FROM DBP3PRD.PS_JOB F
> WHERE C.EMPLID = F.EMPLID
> AND C.EMPL_RCD# = F.EMPL_RCD#
> AND F.EFFDT <= '2001-12-01')
> AND C.EFFSEQ = (SELECT MAX(G.EFFSEQ)
> FROM DBP3PRD.PS_JOB G
> WHERE C.EMPLID = G.EMPLID
> AND C.EMPL_RCD# = G.EMPL_RCD#
> AND C.EFFDT = G.EFFDT)
> AND B.SETID = 'UALID'
> AND B.JOBCODE = C.JOBCODE
> AND B.EFFDT =
> (SELECT MAX(B_ED.EFFDT) FROM DBP3PRD.PS_JOBCODE_TBL B_ED
> WHERE B.SETID = B_ED.SETID
> AND B.JOBCODE = B_ED.JOBCODE
> AND B_ED.EFFDT <= C.EFFDT)
> AND C.DEPTID = E.DEPTID
> AND C.SETID_DEPT = E.SETID
> AND E.EFFDT = (SELECT MAX(I.EFFDT)
> FROM DBP3PRD.PS_DEPT_TBL I
> WHERE C.DEPTID = I.DEPTID
> AND C.SETID_DEPT = I.SETID
> AND I.EFFDT <= C.EFFDT)
> GROUP BY E.UA_DIVISION_CD
> ,E.DEPTID
> ,E.DESCR
> ,B.UA_OPS_CD
> ,B.JOBCODE
> ,B.DESCR
>
> The "good" access path is:
>
> A = Query No
> B = QBLOCKNO
> C = PLANNO
> D = Query Block Type
> E = Access Method
> F = Creator
> G = Table
> H = Table NO
> I = Corr Name
> J = Access Type
> K = Match Cols
> A B C D E F G
> H I J K
> 122003 1 1| SELECT 0 DBP3PRD PS_JOB 3
> C R 0
> 1 2| SELECT 1 DBP3PRD PS_DEPT_TBL 4 E
> I 2
> 1 3| SELECT 1 DBP3PRD PS_EMPLOYEES 1
> I 1
> 1 4| SELECT 1 DBP3PRD PS_JOBCODE_TBL 2 B
> I 2
> 1 5| SELECT 3 0
> 0
> 2 1| CORSUB 0 DBP3PRD PS_JOB
> 5 F I1 3
> 3 1| CORSUB 0 DBP3PRD PS_JOB
> 6 G I1 3
> 4 1| CORSUB 0 DBP3PRD PS_JOBCODE_TBL 7 B_ED
> I1 3
> 5 1| CORSUB 0 DBP3PRD PS_DEPT_TBL 8 I
> I1 3
>
>
> DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can
>



Terry Purcell

Re: Is there a way to influence the order in which the DB2 Optimizer will join tables?
(in response to Isaac Yassin)
Penny,

Rather than looking for any optimizer tricks, check out the statistics held
on these tables.

The optimizer favors accessing single row tables first. So if the stats say
these tables are empty or contain one row, then this could be the cause.

Checkout also the indexes on the PS_DEPT_TBL. There are join predicates
between PS_UAG_DIVISION_CD and PS_DEPT_TBL, but the access path is a
tablespace scan and nested loop join. This is an indication that DB2 thinks
there are very few rows.

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Penny Treptow
Sent: Monday, December 24, 2001 9:09 AM
To: [login to unmask email]
Subject: Is there a way to influence the order in which the DB2
Optimizer will join tables?


The following five way join query runs 'forever'. However, if I remove
the PS_UAG_DIVISION_CD, which
contains only 85 rows and is used to get the most current division title
to avoid more
than one output line for the same division, the four way join query runs
in about 2 minutes. I'd like to 'influence' the Optimizer
to join the PS_UAG_DIVISION_CD last.

The original query is:

SELECT E.UA_DIVISION_CD
,V.DESCR
,E.DEPTID
,E.DESCR AS DEPTNAME
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR AS JOBTITLE
,COUNT(*) AS EMPLCNTMON
FROM DBP3PRD.PS_UAE_EMPL_NS_VW A,
DBP3PRD.PS_JOBCODE_TBL B,
DBP3PRD.PS_JOB C,
DBP3PRD.PS_DEPT_TBL E,
DBP3PRD.PS_UAG_DIVISION_CD V
WHERE A.EMPLID = C.EMPLID
AND C.EMPL_STATUS = 'A'
AND C.EMPL_RCD# = 0
AND C.EFFDT = (SELECT MAX(F.EFFDT)
FROM DBP3PRD.PS_JOB F
WHERE C.EMPLID = F.EMPLID
AND C.EMPL_RCD# = F.EMPL_RCD#
AND F.EFFDT <= '2001-12-01')
AND C.EFFSEQ = (SELECT MAX(G.EFFSEQ)
FROM DBP3PRD.PS_JOB G
WHERE C.EMPLID = G.EMPLID
AND C.EMPL_RCD# = G.EMPL_RCD#
AND C.EFFDT = G.EFFDT)
AND B.SETID = 'UALID'
AND B.JOBCODE = C.JOBCODE
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM DBP3PRD.PS_JOBCODE_TBL B_ED
WHERE B.SETID = B_ED.SETID
AND B.JOBCODE = B_ED.JOBCODE
AND B_ED.EFFDT <= C.EFFDT)
AND C.DEPTID = E.DEPTID
AND C.SETID_DEPT = E.SETID
AND E.EFFDT = (SELECT MAX(I.EFFDT)
FROM DBP3PRD.PS_DEPT_TBL I
WHERE C.DEPTID = I.DEPTID
AND C.SETID_DEPT = I.SETID
AND I.EFFDT <= C.EFFDT)
-** DIVISION TITLE. USE MOST CURRENT DIVISION TITLE
-** TO AVOID MORE THAN ONE LINE OUTPUT FOR SAME
DIVISION.
AND V.UA_DIVISION_CD = E.UA_DIVISION_CD
AND V.SETID = E.SETID
AND V.EFFDT = (SELECT MAX(X.EFFDT)
FROM DBP3PRD.PS_UAG_DIVISION_CD X
WHERE X.UA_DIVISION_CD = V.UA_DIVISION_CD
AND X.SETID = V.SETID
AND X.EFFDT <= CURRENT DATE)
GROUP BY E.UA_DIVISION_CD
,V.DESCR
,E.DEPTID
,E.DESCR
,B.UA_OPS_CD
,B.JOBCODE
,B.DESCR
;

The "bad" access path is:

|A = Query No
B = QBLOCKNO
C = PLANNO
D = Query Block Type
E = Access Method
F = Creator
G = Table
H = Table NO
I = Corr Name
J = Access Type
K = Match Cols
A B C D E F G H I
J K
20901 1 1| SELECT 0 DBP3PRD PS_UAG_DIVISION_CD 5 V R 0
1 2| SELECT 1 DBP3PRD PS_DEPT_TBL 4 E R
0
1 3| SELECT 1 DBP3PRD PS_JOB 3 C I 1
1 4| SELECT 1 DBP3PRD PS_EMPLOYEES 1 I 1
1 5| SELECT 1 DBP3PRD PS_JOBCODE_TBL 2 B I 2
1 6| SELECT 3 0 0
2 1| CORSUB 0 DBP3PRD PS_JOB 6 F I1 3
3 1| CORSUB 0 DBP3PRD PS_JOB 7 G I1 3
4 1| CORSUB 0 DBP3PRD PS_JOBCODE_TBL 8 B_ED I1 3
5 1| CORSUB 0 DBP3PRD PS_DEPT_TBL 9 I I1 3
6 1| CORSUB 0 DBP3PRD PS_UAG_DIVISION_CD 10 X I 3