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

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