Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-26-2011
11:33 AM

07-26-2011
11:33 AM

Hi!

I have this qry whic takes up too much of CPU. Most of the stats are multi coln so I do not want to collect those.

Can we chg the qry to prform better?Any help is appreciated. I tried removing the double precision for one coln, and it just took more time to run which I could not find the reason for.

SELECT DISTINCT D1.c55 AS c1 , D1.c56 AS c2 , D1.c54 AS c3 , D1.c57 AS c4 ,

D1.c58 AS c5 , D1.c59 AS c6 , D1.c22 AS c7 , D1.c11 AS c8 , D1.c34 AS c9 ,

D1.c60 AS c10 , D1.c61 AS c11 , D1.c33 AS c12 , D1.c62 AS c13 ,

( COALESCE( CAST( CAST( D1.c40 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c41 AS DOUBLE PRECISION ) + CAST( D1.c40 AS DOUBLE PRECISION ) ,0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

, D1.c63 AS c15 , D1.c64 AS c16 ,

D1.c65 AS c17 , D1.c66 AS c18 , D1.c67 AS c19 , D1.c68 AS c20 ,

D1.c69 AS c21 , D1.c70 AS c22 , D1.c71 AS c23 , D1.c72 AS c24 ,

D1.c53 AS c25 , D1.c42 AS c26 , D1.c43 AS c27 , D1.c44 AS c28 ,

D1.c45 AS c29 , D1.c46 AS c30 , D1.c47 AS c31 , D1.c48 AS c32 ,

1 AS c33 , D1.c39 AS c34 , D1.c37 AS c35 , D1.c35 AS c36 , D1.c38 AS c37 ,

D1.c36 AS c38 , D1.c39 + D1.c38 + D1.c37 + D1.c36 + D1.c35 AS c39 ,

D1.c46 AS c40 , D1.c47 AS c41 , D1.c41 AS c42 , D1.c49 + D1.c50 AS c43 ,

D1.c51 AS c44 , D1.c40 AS c45 , ( COALESCE( CAST( CAST( D1.c52 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c40 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c46 , ( COALESCE( CAST( CAST( D1.c46 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c48 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c47 , D1.c16 + D1.c15 + D1.c14 + D1.c13 + D1.c12 AS c48 ,

( COALESCE( CAST( CAST( D1.c17 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c18 AS DOUBLE PRECISION ) + CAST( D1.c17 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c49 , ( COALESCE( CAST( CAST( D1.c19 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c17 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c50 , ( COALESCE( CAST( CAST( D1.c20 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c21 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c51 , D1.c5 + D1.c4 + D1.c3 + D1.c2 + D1.c1 AS c52 ,

( COALESCE( CAST( CAST( D1.c6 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c7 AS DOUBLE PRECISION ) + CAST( D1.c6 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c53 , ( COALESCE( CAST( CAST( D1.c8 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c6 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c54 , ( COALESCE( CAST( CAST( D1.c9 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c10 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c55 , D1.c27 + D1.c26 + D1.c25 + D1.c24 + D1.c23 AS c56 ,

( COALESCE( CAST( CAST( D1.c28 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c29 AS DOUBLE PRECISION ) + CAST( D1.c28 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c57 , ( COALESCE( CAST( CAST( D1.c30 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c28 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c58 , ( COALESCE( CAST( CAST( D1.c31 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c32 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c59

FROM (

SELECT SUM ( D1.c1 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c1 ,

SUM ( D1.c2 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c2 , SUM ( D1.c3 ) OVER ( partition BY D1.c22 ,

D1.c11 ) AS c3 , SUM ( D1.c4 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c4 ,

SUM ( D1.c5 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c5 , SUM ( D1.c6 ) OVER ( partition BY D1.c22 ,

D1.c11 ) AS c6 , SUM ( D1.c7 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c7 ,

SUM ( D1.c8 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c8 , SUM ( D1.c9 ) OVER ( partition BY D1.c22 ,

D1.c11 ) AS c9 , SUM ( D1.c10 ) OVER ( partition BY D1.c22 ,

D1.c11 ) AS c10 , D1.c11 AS c11 , SUM ( D1.c1 ) OVER ( partition BY D1.c22 ) AS c12 ,

SUM ( D1.c2 ) OVER ( partition BY D1.c22 ) AS c13 , SUM ( D1.c3 ) OVER ( partition BY D1.c22 ) AS c14 ,

SUM ( D1.c4 ) OVER ( partition BY D1.c22 ) AS c15 , SUM ( D1.c5 ) OVER ( partition BY D1.c22 ) AS c16 ,

SUM ( D1.c6 ) OVER ( partition BY D1.c22 ) AS c17 , SUM ( D1.c7 ) OVER ( partition BY D1.c22 ) AS c18 ,

SUM ( D1.c8 ) OVER ( partition BY D1.c22 ) AS c19 , SUM ( D1.c9 ) OVER ( partition BY D1.c22 ) AS c20 ,

SUM ( D1.c10 ) OVER ( partition BY D1.c22 ) AS c21 , D1.c22 AS c22 ,

SUM ( D1.c1 ) OVER ( partition BY D1.c33 , D1.c34 , D1.c22 ,

D1.c11 ) AS c23 , SUM ( D1.c2 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c24 , SUM ( D1.c3 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c25 , SUM ( D1.c4 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c26 , SUM ( D1.c5 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c27 , SUM ( D1.c6 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c28 , SUM ( D1.c7 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c29 , SUM ( D1.c8 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c30 , SUM ( D1.c9 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c31 , SUM ( D1.c10 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c32 , D1.c33 AS c33 , D1.c34 AS c34 ,

SUM ( D1.c1 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,

D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,

D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,

D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c35 , SUM ( D1.c2 ) OVER ( partition BY D1.c33 ,

D1.c54 , D1.c53 , D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 ,

D1.c68 , D1.c34 , D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 ,

D1.c72 , D1.c70 , D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c36 ,

SUM ( D1.c3 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,

D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,

D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,

D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c37 , SUM ( D1.c4 ) OVER ( partition BY D1.c33 ,

D1.c54 , D1.c53 , D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 ,

D1.c68 , D1.c34 , D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 ,

D1.c72 , D1.c70 , D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c38 ,

SUM ( D1.c5 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,

D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,

D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,

D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c39 , D1.c40 AS c40 ,

D1.c41 AS c41 , D1.c42 AS c42 , D1.c43 AS c43 , D1.c44 AS c44 ,

D1.c45 AS c45 , D1.c46 AS c46 , D1.c47 AS c47 , D1.c48 AS c48 ,

D1.c49 AS c49 , D1.c50 AS c50 , D1.c51 AS c51 , D1.c52 AS c52 ,

D1.c53 AS c53 , D1.c54 AS c54 , D1.c55 AS c55 , D1.c56 AS c56 ,

D1.c57 AS c57 , D1.c58 AS c58 , D1.c59 AS c59 , D1.c60 AS c60 ,

D1.c61 AS c61 , D1.c62 AS c62 , D1.c63 AS c63 , D1.c64 AS c64 ,

D1.c65 AS c65 , D1.c66 AS c66 , D1.c67 AS c67 , D1.c68 AS c68 ,

D1.c69 AS c69 , D1.c70 AS c70 , D1.c71 AS c71 , D1.c72 AS c72

FROM (

SELECT COUNT ( CASE WHEN Q.ACTN_CD = '8' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c1

, COUNT ( CASE WHEN Q.ACTN_CD = '13' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c2

, COUNT ( CASE WHEN Q.ACTN_CD = '2' AND Q.STS_CD = 'R' THEN Q.ROW END ) AS c3

, COUNT ( CASE WHEN Q.ACTN_CD = '5' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c4

, COUNT ( CASE WHEN Q.ACTN_CD = '2' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c5

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'F' , 'O' , 'P' ,'S' ) THEN 1 ELSE 0 END ) AS c6

, SUM ( CASE WHEN R.QC_ELGBL = 'Y' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c7

, SUM ( CASE WHEN Q.QC_P_FL = 'P' THEN 1 ELSE 0 END ) AS c8

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' , 'P' ) THEN 1 ELSE 0 END ) AS c9

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'O' , 'P' , 'S' ) THEN 1 ELSE 0 END ) AS c10 ,

CASE WHEN N.F_NM = ‘ABC’ THEN

CASE WHEN J.NM IN ( 'LS' , 'P AND LS' ) THEN 'T - L Prd'

WHEN J.NM IN ( 'CNTRLZD QC PO' ) THEN 'T - C P-Out'

WHEN J.NM IN ( 'CNTRLZD QC PI' ) THEN 'T - C P-In'

WHEN J.NM IN ( 'CNTRBTS PRCSNG ANLYST' ,'PEN PI' ) THEN 'T - Contributions'

WHEN J.NM IN ( 'ENRLMT' , 'ENRLMT SVC REP' ) THEN 'T - ENRLMTs'

WHEN J.NM IN ( 'ENRLMT SC AND MGRS' ) THEN 'T - ENRLMTs MGRS and SCs'

WHEN J.NM IN ( 'NULL' ) THEN 'T - Null'

WHEN J.NM IN ( 'PMT OPS' ) THEN 'T - PMT Ops'

WHEN J.NM IN ( 'SRVOR' , 'SRVOR NON SIP' , 'SRVOR PMTS T' ) THEN 'T - SRVOR'

END ELSE I.F_NM

END AS c11

, N.F_NM AS c22 , Q.OWNR_LGIN AS c33 ,L.F_NM AS c34

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'F' , 'O' , 'P' ,'S' ) THEN 1 ELSE 0 END ) AS c40

, SUM ( CASE WHEN R.QC_ELGBL = 'Y' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c41

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'O' , 'S' ) THEN 1 ELSE 0 END ) AS c42

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' ) THEN 1 ELSE 0 END ) AS c43

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'S' ) THEN 1 ELSE 0 END ) AS c44

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' ) THEN 1 ELSE 0 END ) AS c45

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' , 'P' ) THEN 1 ELSE 0 END ) AS c46

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'S' ) THEN 1 ELSE 0 END ) AS c47

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'O' , 'P' , 'S' ) THEN 1 ELSE 0 END ) AS c48

, SUM ( CASE WHEN Q.QC_P_FL = 'A' THEN 1 ELSE 0 END ) AS c49

, SUM ( CASE WHEN R.QC_ELGBL = 'N' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c50

, SUM ( CASE WHEN Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c51

, SUM ( CASE WHEN Q.QC_P_FL = 'P' THEN 1 ELSE 0 END ) AS c52

, Q.QC_P_FL AS c53 , Q.TRDT AS c54

, O.DAYT AS c55 , O.PER_NM_MNTH AS c56 ,

CASE WHEN I.F_NM IN ( 'P, Jhn' , 'R, Elzbth' ) THEN 'Prmr/WO/NO'

WHEN N.F_NM = ‘ABC’ AND M.NM IN ( 'CNTRLZD QC PO' )

AND L.BDGT_CD IN ( 1283 , 80429 ) OR I.F_NM IN ( 'G, Dnl' , 'P, Mchl' ) THEN 'CQC'

ELSE ‘Prdn’

END AS c57

, H.F_NM AS c58 , K.F_NM AS c59 , L.BDGT_CD AS c60 , I.BDGT_CD AS c61

, P.LONGESC AS c62 , R.QC_ELGBL AS c63 , R.A_03 AS c64 , R.A_04 AS c65

, R.A_05 AS c66 , S.LONGESC AS c67 , W.L4_EMP_F_NM AS c68

, SUBSTR ( A.L4ANC_POSTN_ID ,4 ) AS c69 , A.L4_EMP_F_NM AS c70 , A.L5_EMP_F_NM AS c71

,D.NM AS c72

FROM PSTN A , PSTN B , EMP C ,

LOV D , EMP E , PSTN F ,

PSTN G , EMP H , EMP I ,

LOV J , EMP K , EMP L ,

LOV M , EMP N , DAY O ,

LOV P , PI_PERF_F Q , LOVX R ,

LOV S ,

LOVX T , EMP U ,

PSTN V , PSTN W

WHERE ( Q.QC_FAIL = D.ROW

AND Q.QC_OPRTR = C.ROW

AND E.INTGRTN_ID = G.INTGRTN_ID

AND E.ROW = Q.OPRTR

AND I.PRFL = J.ROW

AND L.PRFL = M.ROW

AND O.ROW = Q.PIT

AND P.ROW = Q.TSK_TYPE

AND P.ROW = R.ROW

AND S.NM = R.A_01

AND S.ROW = Q.DEPT

AND S.ROW = T.ROW

AND U.INTGRTN_ID = V.INTGRTN_ID

AND U.ROW = T.OWNER

AND V.ROW = W.ROW

AND P.TYPE = 'PI_TSK'

AND S.TYPE = 'PIPT'

AND H.F_NM = 'B, Brn'

AND K.DSRC_NUM_ID = 11

AND K.F_NM = 'B, Adrw'

AND A.ROW = B.ROW

AND F.L8ANC_POSTN_ID = H.INTGRTN_ID

AND F.L5ANC_POSTN_ID = I.INTGRTN_ID

AND F.L7ANC_POSTN_ID = K.INTGRTN_ID

AND F.L4ANC_POSTN_ID = L.INTGRTN_ID

AND F.L6ANC_POSTN_ID = N.INTGRTN_ID

AND F.ROW = G.ROW

AND B.INTGRTN_ID = C.INTGRTN_ID

AND ( Q.ACTN_CD = '2'

OR Q.STS_CD = 'P' )

AND ( Q.ACTN_CD = '13'

OR Q.ACTN_CD = '2'

OR P.LONGESC = 'UVW'

OR P.LONGESC = 'XYZ'

OR Q.ACTN_CD = '8' )

AND ( Q.STS_CD = 'P'

OR Q.STS_CD = 'R' )

AND ( Q.ACTN_CD = '13'

OR Q.ACTN_CD = '2'

OR Q.ACTN_CD = '5'

OR Q.ACTN_CD = '8' )

AND O.DAYT BETWEEN tImEsTaMp'2011-07-19 00:00:00'

AND tImEsTaMp'2011-07-25 00:00:00' )

GROUP BY P.LONGESC , S.LONGESC , O.DAYT ,

O.PER_NM_MNTH , H.F_NM , K.F_NM ,

N.F_NM , I.BDGT_CD , L.F_NM ,

L.BDGT_CD , Q.OWNR_LGIN , Q.TRDT ,

Q.QC_P_FL , R.A_03 , R.A_04 ,

R.A_05 , R.QC_ELGBL , W.L4_EMP_F_NM ,

D.NM , A.L4_EMP_F_NM , A.L5_EMP_F_NM ,

c11 , c57 , c69

) D1 ) D1

1) First, we lock LOVX for access, we lock

PI_PERF_F for access, we lock PSTN for

access, we lock EMP for access, we lock

DAY for access, we lock PSTN for access,

and we lock LOV for access.

2) Next, we execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from EMP by

way of an all-rows scan with a condition of (

(EMP.F_NM = 'B Adrw') AND

(EMP.DSRC_NUM_ID = 11)) into Spool 5

(all_amps) (compressed columns allowed), which is duplicated

on all AMPs. The size of Spool 5 is estimated with low

confidence to be 300 rows. The estimated time for this step

is 0.02 seconds.

2) We do an all-AMPs JOIN step from PSTN by way

of a RowHash match scan with no residual conditions, which is

joined to PSTN by way of a RowHash match scan

with no residual conditions. PSTN and

PSTN are joined using a merge join, with a join

condition of (PSTN.ROW =

PSTN.ROW). The result goes into Spool 6

(all_amps) (compressed columns allowed), which is built

locally on the AMPs. The size of Spool 6 is estimated with

low confidence to be 35,698 rows. The estimated time for

this step is 0.03 seconds.

3) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an

all-rows scan, which is joined to Spool 6 (Last Use) by way of an

all-rows scan. Spool 5 and Spool 6 are joined using a single

partition hash join, with a join condition of (L7ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 7 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 7 is estimated with low confidence to be 1,200

rows. The estimated time for this step is 0.01 seconds.

4) We do an all-AMPs RETRIEVE step from EMP by way of

an all-rows scan with a condition of (NOT

(EMP.PRFL IS NULL)) locking for access

into Spool 8 (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The size of Spool 8 is estimated with

high confidence to be 28,512 rows. The estimated time for this

step is 0.02 seconds.

5) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an

all-rows scan, which is joined to Spool 8 (Last Use) by way of an

all-rows scan. Spool 7 and Spool 8 are joined using a single

partition hash join, with a join condition of (L4ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 9 (all_amps)

(compressed columns allowed), which is built locally on the AMPs.

The size of Spool 9 is estimated with low confidence to be 8 rows.

The estimated time for this step is 0.01 seconds.

6) We do an all-AMPs RETRIEVE step from EMP by way of

an all-rows scan with a condition of (

EMP.F_NM = 'Bohaty, Brian') locking for

access into Spool 10 (all_amps) (compressed columns allowed),

which is duplicated on all AMPs. The size of Spool 10 is

estimated with high confidence to be 300 rows. The estimated time

for this step is 0.02 seconds.

7) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an

all-rows scan, which is joined to Spool 10 (Last Use) by way of an

all-rows scan. Spool 9 and Spool 10 are joined using a single

partition hash join, with a join condition of (L8ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 11 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 11 is estimated with low confidence to be 300

rows. The estimated time for this step is 0.01 seconds.

8) We do an all-AMPs RETRIEVE step from EMP by way of

an all-rows scan with a condition of (NOT

(EMP.PRFL IS NULL)) locking for access

into Spool 12 (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The size of Spool 12 is estimated with

high confidence to be 28,512 rows. The estimated time for this

step is 0.02 seconds.

9) We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an

all-rows scan, which is joined to Spool 12 (Last Use) by way of an

all-rows scan. Spool 11 and Spool 12 are joined using a single

partition hash join, with a join condition of (L5ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 13 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

Then we do a SORT to order Spool 13 by row hash. The size of

Spool 13 is estimated with low confidence to be 300 rows. The

estimated time for this step is 0.01 seconds.

10) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an

all-rows scan, which is joined to LOV by way of a

traversal of index # 4 without accessing the base table extracting

row ids only. Spool 13 and LOV are joined using a

nested join, with a join condition of (PRFL =

LOV.ROW). The result goes into Spool 14 (all_amps),

which is built locally on the AMPs. Then we do a SORT to order

Spool 14 by field Id 1. The size of Spool 14 is estimated with

low confidence to be 3 rows. The estimated time for this step is

0.02 seconds.

11) We do an all-AMPs JOIN step from Spool 14 (Last Use) by way of an

all-rows scan, which is joined to LOV by way of an

all-rows scan with no residual conditions. Spool 14 and

LOV are joined using a row id join, with a join

condition of (PRFL = LOV.ROW). The

result goes into Spool 15 (all_amps) (compressed columns allowed),

which is duplicated on all AMPs. Then we do a SORT to order Spool

15 by row hash. The size of Spool 15 is estimated with low

confidence to be 450 rows. The estimated time for this step is

0.02 seconds.

12) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of an

all-rows scan, which is joined to LOV by way of a

traversal of index # 4 without accessing the base table extracting

row ids only locking LOV for access. Spool 15 and

LOV are joined using a nested join, with a join

condition of (PRFL = LOV.ROW). The

result goes into Spool 16 (all_amps), which is built locally on

the AMPs. Then we do a SORT to order Spool 16 by field Id 1. The

size of Spool 16 is estimated with low confidence to be 4 rows.

The estimated time for this step is 0.02 seconds.

13) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of

an all-rows scan, which is joined to LOV by way of

an all-rows scan with no residual conditions locking

LOV for access. Spool 16 and LOV are

joined using a row id join, with a join condition of (

PRFL = LOV.ROW). The result goes

into Spool 17 (all_amps) (compressed columns allowed), which

is duplicated on all AMPs. The size of Spool 17 is estimated

with low confidence to be 600 rows. The estimated time for

this step is 0.02 seconds.

2) We do an all-AMPs RETRIEVE step from EMP by way

of an all-rows scan with no residual conditions locking for

access into Spool 18 (all_amps) (compressed columns allowed),

which is built locally on the AMPs. The size of Spool 18 is

estimated with high confidence to be 34,691 rows. The

estimated time for this step is 0.02 seconds.

14) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of an

all-rows scan, which is joined to Spool 18 (Last Use) by way of an

all-rows scan. Spool 17 and Spool 18 are joined using a single

partition hash join, with a join condition of (L6ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 19 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 19 is estimated with low confidence to be 750

rows. The estimated time for this step is 0.01 seconds.

15) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from EMP by way

of an all-rows scan with no residual conditions locking for

access into Spool 20 (all_amps) (compressed columns allowed),

which is built locally on the AMPs. The size of Spool 20 is

estimated with high confidence to be 34,691 rows. The

estimated time for this step is 0.02 seconds.

2) We do an all-AMPs RETRIEVE step from LOVX by way

of an all-rows scan with a condition of ((NOT

(LOVX.A_01 IS NULL )) AND (NOT

(LOVX.ROW IS NULL ))) into Spool 21

(all_amps) (compressed columns allowed), which is built

locally on the AMPs. The size of Spool 21 is estimated with

high confidence to be 15,037 rows. The estimated time for

this step is 0.00 seconds.

3) We do an all-AMPs RETRIEVE step from LOV by way of

index # 8 TYPE = 'PIPT' with a residual condition of (

LOV.TYPE = 'PIPT') locking for access into

Spool 22 (all_amps) (compressed columns allowed), which is

duplicated on all AMPs. The size of Spool 22 is estimated

with high confidence to be 32,700 rows. The estimated time

for this step is 0.04 seconds.

4) We do an all-AMPs RETRIEVE step from LOV by way of

an all-rows scan with a condition of (LOV.TYPE =

'PI_TSK') locking for access into Spool 23 (all_amps)

(compressed columns allowed), which is redistributed by hash

code to all AMPs. The size of Spool 23 is estimated with high

confidence to be 2,437 rows. The estimated time for this step

is 0.02 seconds.

16) We do an all-AMPs JOIN step from Spool 21 (Last Use) by way of an

all-rows scan, which is joined to Spool 22 (Last Use) by way of an

all-rows scan. Spool 21 and Spool 22 are joined using a single

partition hash join, with a join condition of (NM = A_01).

The result goes into Spool 24 (all_amps) (compressed columns

allowed), which is redistributed by hash code to all AMPs. The

size of Spool 24 is estimated with low confidence to be 14,253

rows. The estimated time for this step is 0.05 seconds.

17) We do an all-AMPs RETRIEVE step from LOVX by way of

an all-rows scan with a condition of ((NOT

(LOVX.OWNER IS NULL )) AND (NOT

(LOVX.ROW IS NULL ))) locking for access into

Spool 25 (all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. The size of Spool 25 is

estimated with high confidence to be 15,037 rows. The estimated

time for this step is 0.05 seconds.

18) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of an

all-rows scan, which is joined to Spool 24 (Last Use) by way of an

all-rows scan. Spool 23 and Spool 24 are joined using a single

partition hash join, with a join condition of (ROW = ROW).

The result goes into Spool 26 (all_amps) (compressed columns

allowed), which is redistributed by hash code to all AMPs. The

size of Spool 26 is estimated with low confidence to be 14,253

rows. The estimated time for this step is 0.05 seconds.

19) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from EMP by way

of an all-rows scan with no residual conditions locking for

access into Spool 27 (all_amps) (compressed columns allowed),

which is redistributed by hash code to all AMPs. The size of

Spool 27 is estimated with high confidence to be 34,691 rows.

The estimated time for this step is 0.09 seconds.

2) We do an all-AMPs JOIN step from PSTN by way of

a RowHash match scan with no residual conditions, which is

joined to PSTN by way of a RowHash match scan

with no residual conditions locking PSTN for

access and PSTN for access. PSTN

and PSTN are joined using a merge join, with a

join condition of (PSTN.ROW =

PSTN.ROW). The result goes into Spool 28

(all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. The size of Spool 28

is estimated with low confidence to be 35,698 rows. The

estimated time for this step is 0.09 seconds.

3) We do an all-AMPs RETRIEVE step from EMP by way

of an all-rows scan with no residual conditions locking for

access into Spool 29 (all_amps) (compressed columns allowed),

which is redistributed by hash code to all AMPs. The size of

Spool 29 is estimated with high confidence to be 34,691 rows.

The estimated time for this step is 0.09 seconds.

4) We do an all-AMPs JOIN step from PSTN by way of

a RowHash match scan with no residual conditions, which is

joined to PSTN by way of a RowHash match scan

with no residual conditions locking PSTN for

access and PSTN for access. PSTN

and PSTN are joined using a merge join, with a

join condition of (PSTN.ROW =

PSTN.ROW). The result goes into Spool 30

(all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. The size of Spool 30

is estimated with low confidence to be 35,698 rows. The

estimated time for this step is 0.09 seconds.

20) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an

all-rows scan, which is joined to Spool 20 (Last Use) by way of an

all-rows scan. Spool 19 and Spool 20 are joined using a single

partition hash join, with a join condition of (INTGRTN_ID =

INTGRTN_ID). The result goes into Spool 31 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 31 is estimated with low confidence to be 900

rows. The estimated time for this step is 0.01 seconds.

21) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from DAY by way of

an all-rows scan with a condition of ((DAY.DAYT

<= TIMESTAMP '2011-07-25 00:00:00') AND (DAY.DAYT

>= TIMESTAMP '2011-07-19 00:00:00')) into Spool 32 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 32 is estimated with high confidence to be

19,500 rows. The estimated time for this step is 0.03 seconds.

2) We do an all-AMPs JOIN step from Spool 31 (Last Use) by way of

an all-rows scan, which is joined to PI_PERF_F by

way of an all-rows scan with a condition of (

((PI_PERF_F.ACTN_CD = '13') OR

((PI_PERF_F.ACTN_CD = '2') OR

((PI_PERF_F.ACTN_CD = '5') OR

(PI_PERF_F.ACTN_CD = '8')))) AND

(((PI_PERF_F.STS_CD = 'P') OR

(PI_PERF_F.STS_CD = 'R')) AND

(((PI_PERF_F.ACTN_CD = '2') OR

(PI_PERF_F.STS_CD = 'P')) AND ((NOT

(PI_PERF_F.DEPT IS NULL )) AND ((NOT

(PI_PERF_F.TSK_TYPE IS NULL )) AND ((NOT

(PI_PERF_F.PIT IS NULL )) AND (NOT

(PI_PERF_F.QC_OPRTR IS NULL )))))))).

Spool 31 and PI_PERF_F are joined using a product

join, with a join condition of (ROW =

PI_PERF_F.OPRTR). The input table

PI_PERF_F will not be cached in memory, but it is

eligible for synchronized scanning. The result goes into

Spool 33 (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The size of Spool 33 is estimated

with low confidence to be 13,627 rows. The estimated time for

this step is 1 minute and 1 second.

22) We do an all-AMPs JOIN step from Spool 25 (Last Use) by way of an

all-rows scan, which is joined to Spool 26 (Last Use) by way of an

all-rows scan. Spool 25 and Spool 26 are joined using a single

partition hash join, with a join condition of (ROW = ROW).

The result goes into Spool 34 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. The size of Spool

34 is estimated with low confidence to be 156,554 rows. The

estimated time for this step is 0.02 seconds.

23) We do an all-AMPs JOIN step from Spool 32 (Last Use) by way of an

all-rows scan, which is joined to Spool 33 (Last Use) by way of an

all-rows scan. Spool 32 and Spool 33 are joined using a single

partition hash join, with a join condition of (ROW =

PIT). The result goes into Spool 35 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs. The size of

Spool 35 is estimated with low confidence to be 121,350 rows. The

estimated time for this step is 0.05 seconds.

24) We do an all-AMPs JOIN step from Spool 27 (Last Use) by way of an

all-rows scan, which is joined to Spool 28 (Last Use) by way of an

all-rows scan. Spool 27 and Spool 28 are joined using a single

partition hash join, with a join condition of (INTGRTN_ID =

INTGRTN_ID). The result goes into Spool 36 (all_amps)

(compressed columns allowed), which is built locally on the AMPs.

The size of Spool 36 is estimated with low confidence to be 34,693

rows. The estimated time for this step is 0.01 seconds.

25) We do an all-AMPs JOIN step from Spool 34 (Last Use) by way of an

all-rows scan, which is joined to Spool 35 (Last Use) by way of an

all-rows scan. Spool 34 and Spool 35 are joined using a single

partition hash join, with a join condition of ((((LONGESC =

'UVW') OR (LONGESC = 'XYZ')) OR ((ACTN_CD =

'13') OR ((ACTN_CD = '2') OR (ACTN_CD = '8')))) AND

((ROW = TSK_TYPE) AND ((ROW = TSK_TYPE) AND

((ROW = DEPT) AND (ROW= DEPT ))))). The result

goes into Spool 37 (all_amps) (compressed columns allowed), which

is duplicated on all AMPs. The size of Spool 37 is estimated with

low confidence to be 91,050 rows. The estimated time for this

step is 0.05 seconds.

26) We do an all-AMPs RETRIEVE step from LOV by way of an

all-rows scan with no residual conditions locking for access into

Spool 38 (all_amps) (compressed columns allowed), which is built

locally on the AMPs. The size of Spool 38 is estimated with high

confidence to be 100,301 rows. The estimated time for this step

is 0.02 seconds.

27) We do an all-AMPs JOIN step from Spool 36 (Last Use) by way of an

all-rows scan, which is joined to Spool 37 (Last Use) by way of an

all-rows scan. Spool 36 and Spool 37 are joined using a single

partition hash join, with a join condition of (ROW =

OWNER). The result goes into Spool 39 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs. The size of

Spool 39 is estimated with low confidence to be 91,200 rows. The

estimated time for this step is 0.05 seconds.

28) We do an all-AMPs JOIN step from Spool 29 (Last Use) by way of an

all-rows scan, which is joined to Spool 30 (Last Use) by way of an

all-rows scan. Spool 29 and Spool 30 are joined using a single

partition hash join, with a join condition of (INTGRTN_ID =

INTGRTN_ID). The result goes into Spool 40 (all_amps)

(compressed columns allowed), which is built locally on the AMPs.

The size of Spool 40 is estimated with low confidence to be 34,693

rows. The estimated time for this step is 0.01 seconds.

29) We do an all-AMPs JOIN step from Spool 38 (Last Use) by way of an

all-rows scan, which is joined to Spool 39 (Last Use) by way of an

all-rows scan. Spool 38 and Spool 39 are joined using a single

partition hash join, with a join condition of (QC_FAIL =

ROW). The result goes into Spool 41 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs. The size of

Spool 41 is estimated with index join confidence to be 91,350 rows.

The estimated time for this step is 0.05 seconds.

30) We do an all-AMPs JOIN step from Spool 40 (Last Use) by way of an

all-rows scan, which is joined to Spool 41 (Last Use) by way of an

all-rows scan. Spool 40 and Spool 41 are joined using a single

partition hash join, with a join condition of (QC_OPRTR

= ROW). The result goes into Spool 4 (all_amps) (compressed

columns allowed), which is built locally on the AMPs. The size of

Spool 4 is estimated with index join confidence to be 610 rows.

The estimated time for this step is 0.02 seconds.

31) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by

way of an all-rows scan, and the grouping identifier in field 1.

Aggregate Intermediate Results are computed globally, then placed

in Spool 42. The size of Spool 42 is estimated with index join

confidence to be 610 rows. The estimated time for this step is

0.02 seconds.

32) We do an all-AMPs RETRIEVE step from Spool 42 (Last Use) by way of

an all-rows scan into Spool 1 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. The size of Spool 1

is estimated with index join confidence to be 610 rows. The

estimated time for this step is 0.01 seconds.

33) We do an all-AMPs STAT FUNCTION step from Spool 1 (Last Use) by

way of an all-rows scan into Spool 47 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 45 (all_amps) (compressed columns allowed), which is

built locally on the AMPs.

34) We do an all-AMPs STAT FUNCTION step from Spool 45 (Last Use) by

way of an all-rows scan into Spool 50 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 49 (all_amps) (compressed columns allowed), which is

built locally on the AMPs.

35) We do an all-AMPs STAT FUNCTION step from Spool 49 (Last Use) by

way of an all-rows scan into Spool 53 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 52 (all_amps) (compressed columns allowed), which is

built locally on the AMPs.

36) We do an all-AMPs STAT FUNCTION step from Spool 52 (Last Use) by

way of an all-rows scan into Spool 56 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 55 (all_amps) (compressed columns allowed), which is

built locally on the AMPs.

37) We do an all-AMPs RETRIEVE step from Spool 55 (Last Use) by way of

an all-rows scan into Spool 2 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. The size of Spool 2

is estimated with no confidence to be 610 rows. The estimated

time for this step is 0.01 seconds.

38) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of

an all-rows scan into Spool 59 (group_amps), which is

redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 59 by the sort key in spool field1 eliminating

duplicate rows. The size of Spool 59 is estimated with no

confidence to be 610 rows. The estimated time for this step is

0.01 seconds.

39) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

-> The contents of Spool 59 are sent back to the user as the result

of statement 1.

Thanks!

I have this qry whic takes up too much of CPU. Most of the stats are multi coln so I do not want to collect those.

Can we chg the qry to prform better?Any help is appreciated. I tried removing the double precision for one coln, and it just took more time to run which I could not find the reason for.

SELECT DISTINCT D1.c55 AS c1 , D1.c56 AS c2 , D1.c54 AS c3 , D1.c57 AS c4 ,

D1.c58 AS c5 , D1.c59 AS c6 , D1.c22 AS c7 , D1.c11 AS c8 , D1.c34 AS c9 ,

D1.c60 AS c10 , D1.c61 AS c11 , D1.c33 AS c12 , D1.c62 AS c13 ,

( COALESCE( CAST( CAST( D1.c40 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c41 AS DOUBLE PRECISION ) + CAST( D1.c40 AS DOUBLE PRECISION ) ,0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

, D1.c63 AS c15 , D1.c64 AS c16 ,

D1.c65 AS c17 , D1.c66 AS c18 , D1.c67 AS c19 , D1.c68 AS c20 ,

D1.c69 AS c21 , D1.c70 AS c22 , D1.c71 AS c23 , D1.c72 AS c24 ,

D1.c53 AS c25 , D1.c42 AS c26 , D1.c43 AS c27 , D1.c44 AS c28 ,

D1.c45 AS c29 , D1.c46 AS c30 , D1.c47 AS c31 , D1.c48 AS c32 ,

1 AS c33 , D1.c39 AS c34 , D1.c37 AS c35 , D1.c35 AS c36 , D1.c38 AS c37 ,

D1.c36 AS c38 , D1.c39 + D1.c38 + D1.c37 + D1.c36 + D1.c35 AS c39 ,

D1.c46 AS c40 , D1.c47 AS c41 , D1.c41 AS c42 , D1.c49 + D1.c50 AS c43 ,

D1.c51 AS c44 , D1.c40 AS c45 , ( COALESCE( CAST( CAST( D1.c52 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c40 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c46 , ( COALESCE( CAST( CAST( D1.c46 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c48 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c47 , D1.c16 + D1.c15 + D1.c14 + D1.c13 + D1.c12 AS c48 ,

( COALESCE( CAST( CAST( D1.c17 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c18 AS DOUBLE PRECISION ) + CAST( D1.c17 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c49 , ( COALESCE( CAST( CAST( D1.c19 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c17 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c50 , ( COALESCE( CAST( CAST( D1.c20 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c21 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c51 , D1.c5 + D1.c4 + D1.c3 + D1.c2 + D1.c1 AS c52 ,

( COALESCE( CAST( CAST( D1.c6 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c7 AS DOUBLE PRECISION ) + CAST( D1.c6 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c53 , ( COALESCE( CAST( CAST( D1.c8 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c6 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c54 , ( COALESCE( CAST( CAST( D1.c9 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c10 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c55 , D1.c27 + D1.c26 + D1.c25 + D1.c24 + D1.c23 AS c56 ,

( COALESCE( CAST( CAST( D1.c28 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c29 AS DOUBLE PRECISION ) + CAST( D1.c28 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c57 , ( COALESCE( CAST( CAST( D1.c30 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c28 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c58 , ( COALESCE( CAST( CAST( D1.c31 AS DOUBLE PRECISION ) / ( NULLIF( CAST( D1.c32 AS DOUBLE PRECISION ) ,

0 ) ) * ( 100.0 ( DOUBLE PRECISION ) ) AS DOUBLE PRECISION ) ,

( 0.0 ( DOUBLE PRECISION ) ) ) ) AS c59

FROM (

SELECT SUM ( D1.c1 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c1 ,

SUM ( D1.c2 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c2 , SUM ( D1.c3 ) OVER ( partition BY D1.c22 ,

D1.c11 ) AS c3 , SUM ( D1.c4 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c4 ,

SUM ( D1.c5 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c5 , SUM ( D1.c6 ) OVER ( partition BY D1.c22 ,

D1.c11 ) AS c6 , SUM ( D1.c7 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c7 ,

SUM ( D1.c8 ) OVER ( partition BY D1.c22 , D1.c11 ) AS c8 , SUM ( D1.c9 ) OVER ( partition BY D1.c22 ,

D1.c11 ) AS c9 , SUM ( D1.c10 ) OVER ( partition BY D1.c22 ,

D1.c11 ) AS c10 , D1.c11 AS c11 , SUM ( D1.c1 ) OVER ( partition BY D1.c22 ) AS c12 ,

SUM ( D1.c2 ) OVER ( partition BY D1.c22 ) AS c13 , SUM ( D1.c3 ) OVER ( partition BY D1.c22 ) AS c14 ,

SUM ( D1.c4 ) OVER ( partition BY D1.c22 ) AS c15 , SUM ( D1.c5 ) OVER ( partition BY D1.c22 ) AS c16 ,

SUM ( D1.c6 ) OVER ( partition BY D1.c22 ) AS c17 , SUM ( D1.c7 ) OVER ( partition BY D1.c22 ) AS c18 ,

SUM ( D1.c8 ) OVER ( partition BY D1.c22 ) AS c19 , SUM ( D1.c9 ) OVER ( partition BY D1.c22 ) AS c20 ,

SUM ( D1.c10 ) OVER ( partition BY D1.c22 ) AS c21 , D1.c22 AS c22 ,

SUM ( D1.c1 ) OVER ( partition BY D1.c33 , D1.c34 , D1.c22 ,

D1.c11 ) AS c23 , SUM ( D1.c2 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c24 , SUM ( D1.c3 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c25 , SUM ( D1.c4 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c26 , SUM ( D1.c5 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c27 , SUM ( D1.c6 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c28 , SUM ( D1.c7 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c29 , SUM ( D1.c8 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c30 , SUM ( D1.c9 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c31 , SUM ( D1.c10 ) OVER ( partition BY D1.c33 ,

D1.c34 , D1.c22 , D1.c11 ) AS c32 , D1.c33 AS c33 , D1.c34 AS c34 ,

SUM ( D1.c1 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,

D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,

D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,

D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c35 , SUM ( D1.c2 ) OVER ( partition BY D1.c33 ,

D1.c54 , D1.c53 , D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 ,

D1.c68 , D1.c34 , D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 ,

D1.c72 , D1.c70 , D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c36 ,

SUM ( D1.c3 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,

D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,

D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,

D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c37 , SUM ( D1.c4 ) OVER ( partition BY D1.c33 ,

D1.c54 , D1.c53 , D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 ,

D1.c68 , D1.c34 , D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 ,

D1.c72 , D1.c70 , D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c38 ,

SUM ( D1.c5 ) OVER ( partition BY D1.c33 , D1.c54 , D1.c53 ,

D1.c55 , D1.c66 , D1.c62 , D1.c63 , D1.c67 , D1.c68 , D1.c34 ,

D1.c22 , D1.c59 , D1.c58 , D1.c61 , D1.c60 , D1.c72 , D1.c70 ,

D1.c69 , D1.c71 , D1.c11 , D1.c57 ) AS c39 , D1.c40 AS c40 ,

D1.c41 AS c41 , D1.c42 AS c42 , D1.c43 AS c43 , D1.c44 AS c44 ,

D1.c45 AS c45 , D1.c46 AS c46 , D1.c47 AS c47 , D1.c48 AS c48 ,

D1.c49 AS c49 , D1.c50 AS c50 , D1.c51 AS c51 , D1.c52 AS c52 ,

D1.c53 AS c53 , D1.c54 AS c54 , D1.c55 AS c55 , D1.c56 AS c56 ,

D1.c57 AS c57 , D1.c58 AS c58 , D1.c59 AS c59 , D1.c60 AS c60 ,

D1.c61 AS c61 , D1.c62 AS c62 , D1.c63 AS c63 , D1.c64 AS c64 ,

D1.c65 AS c65 , D1.c66 AS c66 , D1.c67 AS c67 , D1.c68 AS c68 ,

D1.c69 AS c69 , D1.c70 AS c70 , D1.c71 AS c71 , D1.c72 AS c72

FROM (

SELECT COUNT ( CASE WHEN Q.ACTN_CD = '8' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c1

, COUNT ( CASE WHEN Q.ACTN_CD = '13' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c2

, COUNT ( CASE WHEN Q.ACTN_CD = '2' AND Q.STS_CD = 'R' THEN Q.ROW END ) AS c3

, COUNT ( CASE WHEN Q.ACTN_CD = '5' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c4

, COUNT ( CASE WHEN Q.ACTN_CD = '2' AND Q.STS_CD = 'P' THEN Q.ROW END ) AS c5

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'F' , 'O' , 'P' ,'S' ) THEN 1 ELSE 0 END ) AS c6

, SUM ( CASE WHEN R.QC_ELGBL = 'Y' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c7

, SUM ( CASE WHEN Q.QC_P_FL = 'P' THEN 1 ELSE 0 END ) AS c8

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' , 'P' ) THEN 1 ELSE 0 END ) AS c9

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'O' , 'P' , 'S' ) THEN 1 ELSE 0 END ) AS c10 ,

CASE WHEN N.F_NM = ‘ABC’ THEN

CASE WHEN J.NM IN ( 'LS' , 'P AND LS' ) THEN 'T - L Prd'

WHEN J.NM IN ( 'CNTRLZD QC PO' ) THEN 'T - C P-Out'

WHEN J.NM IN ( 'CNTRLZD QC PI' ) THEN 'T - C P-In'

WHEN J.NM IN ( 'CNTRBTS PRCSNG ANLYST' ,'PEN PI' ) THEN 'T - Contributions'

WHEN J.NM IN ( 'ENRLMT' , 'ENRLMT SVC REP' ) THEN 'T - ENRLMTs'

WHEN J.NM IN ( 'ENRLMT SC AND MGRS' ) THEN 'T - ENRLMTs MGRS and SCs'

WHEN J.NM IN ( 'NULL' ) THEN 'T - Null'

WHEN J.NM IN ( 'PMT OPS' ) THEN 'T - PMT Ops'

WHEN J.NM IN ( 'SRVOR' , 'SRVOR NON SIP' , 'SRVOR PMTS T' ) THEN 'T - SRVOR'

END ELSE I.F_NM

END AS c11

, N.F_NM AS c22 , Q.OWNR_LGIN AS c33 ,L.F_NM AS c34

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'F' , 'O' , 'P' ,'S' ) THEN 1 ELSE 0 END ) AS c40

, SUM ( CASE WHEN R.QC_ELGBL = 'Y' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c41

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' , 'O' , 'S' ) THEN 1 ELSE 0 END ) AS c42

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' ) THEN 1 ELSE 0 END ) AS c43

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'S' ) THEN 1 ELSE 0 END ) AS c44

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'A' ) THEN 1 ELSE 0 END ) AS c45

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'O' , 'P' ) THEN 1 ELSE 0 END ) AS c46

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'S' ) THEN 1 ELSE 0 END ) AS c47

, SUM ( CASE WHEN Q.QC_P_FL IN ( 'F' , 'O' , 'P' , 'S' ) THEN 1 ELSE 0 END ) AS c48

, SUM ( CASE WHEN Q.QC_P_FL = 'A' THEN 1 ELSE 0 END ) AS c49

, SUM ( CASE WHEN R.QC_ELGBL = 'N' AND Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c50

, SUM ( CASE WHEN Q.QC_P_FL IS NULL THEN 1 ELSE 0 END ) AS c51

, SUM ( CASE WHEN Q.QC_P_FL = 'P' THEN 1 ELSE 0 END ) AS c52

, Q.QC_P_FL AS c53 , Q.TRDT AS c54

, O.DAYT AS c55 , O.PER_NM_MNTH AS c56 ,

CASE WHEN I.F_NM IN ( 'P, Jhn' , 'R, Elzbth' ) THEN 'Prmr/WO/NO'

WHEN N.F_NM = ‘ABC’ AND M.NM IN ( 'CNTRLZD QC PO' )

AND L.BDGT_CD IN ( 1283 , 80429 ) OR I.F_NM IN ( 'G, Dnl' , 'P, Mchl' ) THEN 'CQC'

ELSE ‘Prdn’

END AS c57

, H.F_NM AS c58 , K.F_NM AS c59 , L.BDGT_CD AS c60 , I.BDGT_CD AS c61

, P.LONGESC AS c62 , R.QC_ELGBL AS c63 , R.A_03 AS c64 , R.A_04 AS c65

, R.A_05 AS c66 , S.LONGESC AS c67 , W.L4_EMP_F_NM AS c68

, SUBSTR ( A.L4ANC_POSTN_ID ,4 ) AS c69 , A.L4_EMP_F_NM AS c70 , A.L5_EMP_F_NM AS c71

,D.NM AS c72

FROM PSTN A , PSTN B , EMP C ,

LOV D , EMP E , PSTN F ,

PSTN G , EMP H , EMP I ,

LOV J , EMP K , EMP L ,

LOV M , EMP N , DAY O ,

LOV P , PI_PERF_F Q , LOVX R ,

LOV S ,

LOVX T , EMP U ,

PSTN V , PSTN W

WHERE ( Q.QC_FAIL = D.ROW

AND Q.QC_OPRTR = C.ROW

AND E.INTGRTN_ID = G.INTGRTN_ID

AND E.ROW = Q.OPRTR

AND I.PRFL = J.ROW

AND L.PRFL = M.ROW

AND O.ROW = Q.PIT

AND P.ROW = Q.TSK_TYPE

AND P.ROW = R.ROW

AND S.NM = R.A_01

AND S.ROW = Q.DEPT

AND S.ROW = T.ROW

AND U.INTGRTN_ID = V.INTGRTN_ID

AND U.ROW = T.OWNER

AND V.ROW = W.ROW

AND P.TYPE = 'PI_TSK'

AND S.TYPE = 'PIPT'

AND H.F_NM = 'B, Brn'

AND K.DSRC_NUM_ID = 11

AND K.F_NM = 'B, Adrw'

AND A.ROW = B.ROW

AND F.L8ANC_POSTN_ID = H.INTGRTN_ID

AND F.L5ANC_POSTN_ID = I.INTGRTN_ID

AND F.L7ANC_POSTN_ID = K.INTGRTN_ID

AND F.L4ANC_POSTN_ID = L.INTGRTN_ID

AND F.L6ANC_POSTN_ID = N.INTGRTN_ID

AND F.ROW = G.ROW

AND B.INTGRTN_ID = C.INTGRTN_ID

AND ( Q.ACTN_CD = '2'

OR Q.STS_CD = 'P' )

AND ( Q.ACTN_CD = '13'

OR Q.ACTN_CD = '2'

OR P.LONGESC = 'UVW'

OR P.LONGESC = 'XYZ'

OR Q.ACTN_CD = '8' )

AND ( Q.STS_CD = 'P'

OR Q.STS_CD = 'R' )

AND ( Q.ACTN_CD = '13'

OR Q.ACTN_CD = '2'

OR Q.ACTN_CD = '5'

OR Q.ACTN_CD = '8' )

AND O.DAYT BETWEEN tImEsTaMp'2011-07-19 00:00:00'

AND tImEsTaMp'2011-07-25 00:00:00' )

GROUP BY P.LONGESC , S.LONGESC , O.DAYT ,

O.PER_NM_MNTH , H.F_NM , K.F_NM ,

N.F_NM , I.BDGT_CD , L.F_NM ,

L.BDGT_CD , Q.OWNR_LGIN , Q.TRDT ,

Q.QC_P_FL , R.A_03 , R.A_04 ,

R.A_05 , R.QC_ELGBL , W.L4_EMP_F_NM ,

D.NM , A.L4_EMP_F_NM , A.L5_EMP_F_NM ,

c11 , c57 , c69

) D1 ) D1

1) First, we lock LOVX for access, we lock

PI_PERF_F for access, we lock PSTN for

access, we lock EMP for access, we lock

DAY for access, we lock PSTN for access,

and we lock LOV for access.

2) Next, we execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from EMP by

way of an all-rows scan with a condition of (

(EMP.F_NM = 'B Adrw') AND

(EMP.DSRC_NUM_ID = 11)) into Spool 5

(all_amps) (compressed columns allowed), which is duplicated

on all AMPs. The size of Spool 5 is estimated with low

confidence to be 300 rows. The estimated time for this step

is 0.02 seconds.

2) We do an all-AMPs JOIN step from PSTN by way

of a RowHash match scan with no residual conditions, which is

joined to PSTN by way of a RowHash match scan

with no residual conditions. PSTN and

PSTN are joined using a merge join, with a join

condition of (PSTN.ROW =

PSTN.ROW). The result goes into Spool 6

(all_amps) (compressed columns allowed), which is built

locally on the AMPs. The size of Spool 6 is estimated with

low confidence to be 35,698 rows. The estimated time for

this step is 0.03 seconds.

3) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an

all-rows scan, which is joined to Spool 6 (Last Use) by way of an

all-rows scan. Spool 5 and Spool 6 are joined using a single

partition hash join, with a join condition of (L7ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 7 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 7 is estimated with low confidence to be 1,200

rows. The estimated time for this step is 0.01 seconds.

4) We do an all-AMPs RETRIEVE step from EMP by way of

an all-rows scan with a condition of (NOT

(EMP.PRFL IS NULL)) locking for access

into Spool 8 (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The size of Spool 8 is estimated with

high confidence to be 28,512 rows. The estimated time for this

step is 0.02 seconds.

5) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an

all-rows scan, which is joined to Spool 8 (Last Use) by way of an

all-rows scan. Spool 7 and Spool 8 are joined using a single

partition hash join, with a join condition of (L4ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 9 (all_amps)

(compressed columns allowed), which is built locally on the AMPs.

The size of Spool 9 is estimated with low confidence to be 8 rows.

The estimated time for this step is 0.01 seconds.

6) We do an all-AMPs RETRIEVE step from EMP by way of

an all-rows scan with a condition of (

EMP.F_NM = 'Bohaty, Brian') locking for

access into Spool 10 (all_amps) (compressed columns allowed),

which is duplicated on all AMPs. The size of Spool 10 is

estimated with high confidence to be 300 rows. The estimated time

for this step is 0.02 seconds.

7) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an

all-rows scan, which is joined to Spool 10 (Last Use) by way of an

all-rows scan. Spool 9 and Spool 10 are joined using a single

partition hash join, with a join condition of (L8ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 11 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 11 is estimated with low confidence to be 300

rows. The estimated time for this step is 0.01 seconds.

8) We do an all-AMPs RETRIEVE step from EMP by way of

an all-rows scan with a condition of (NOT

(EMP.PRFL IS NULL)) locking for access

into Spool 12 (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The size of Spool 12 is estimated with

high confidence to be 28,512 rows. The estimated time for this

step is 0.02 seconds.

9) We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an

all-rows scan, which is joined to Spool 12 (Last Use) by way of an

all-rows scan. Spool 11 and Spool 12 are joined using a single

partition hash join, with a join condition of (L5ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 13 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

Then we do a SORT to order Spool 13 by row hash. The size of

Spool 13 is estimated with low confidence to be 300 rows. The

estimated time for this step is 0.01 seconds.

10) We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of an

all-rows scan, which is joined to LOV by way of a

traversal of index # 4 without accessing the base table extracting

row ids only. Spool 13 and LOV are joined using a

nested join, with a join condition of (PRFL =

LOV.ROW). The result goes into Spool 14 (all_amps),

which is built locally on the AMPs. Then we do a SORT to order

Spool 14 by field Id 1. The size of Spool 14 is estimated with

low confidence to be 3 rows. The estimated time for this step is

0.02 seconds.

11) We do an all-AMPs JOIN step from Spool 14 (Last Use) by way of an

all-rows scan, which is joined to LOV by way of an

all-rows scan with no residual conditions. Spool 14 and

LOV are joined using a row id join, with a join

condition of (PRFL = LOV.ROW). The

result goes into Spool 15 (all_amps) (compressed columns allowed),

which is duplicated on all AMPs. Then we do a SORT to order Spool

15 by row hash. The size of Spool 15 is estimated with low

confidence to be 450 rows. The estimated time for this step is

0.02 seconds.

12) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of an

all-rows scan, which is joined to LOV by way of a

traversal of index # 4 without accessing the base table extracting

row ids only locking LOV for access. Spool 15 and

LOV are joined using a nested join, with a join

condition of (PRFL = LOV.ROW). The

result goes into Spool 16 (all_amps), which is built locally on

the AMPs. Then we do a SORT to order Spool 16 by field Id 1. The

size of Spool 16 is estimated with low confidence to be 4 rows.

The estimated time for this step is 0.02 seconds.

13) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of

an all-rows scan, which is joined to LOV by way of

an all-rows scan with no residual conditions locking

LOV for access. Spool 16 and LOV are

joined using a row id join, with a join condition of (

PRFL = LOV.ROW). The result goes

into Spool 17 (all_amps) (compressed columns allowed), which

is duplicated on all AMPs. The size of Spool 17 is estimated

with low confidence to be 600 rows. The estimated time for

this step is 0.02 seconds.

2) We do an all-AMPs RETRIEVE step from EMP by way

of an all-rows scan with no residual conditions locking for

access into Spool 18 (all_amps) (compressed columns allowed),

which is built locally on the AMPs. The size of Spool 18 is

estimated with high confidence to be 34,691 rows. The

estimated time for this step is 0.02 seconds.

14) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of an

all-rows scan, which is joined to Spool 18 (Last Use) by way of an

all-rows scan. Spool 17 and Spool 18 are joined using a single

partition hash join, with a join condition of (L6ANC_POSTN_ID =

INTGRTN_ID). The result goes into Spool 19 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 19 is estimated with low confidence to be 750

rows. The estimated time for this step is 0.01 seconds.

15) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from EMP by way

of an all-rows scan with no residual conditions locking for

access into Spool 20 (all_amps) (compressed columns allowed),

which is built locally on the AMPs. The size of Spool 20 is

estimated with high confidence to be 34,691 rows. The

estimated time for this step is 0.02 seconds.

2) We do an all-AMPs RETRIEVE step from LOVX by way

of an all-rows scan with a condition of ((NOT

(LOVX.A_01 IS NULL )) AND (NOT

(LOVX.ROW IS NULL ))) into Spool 21

(all_amps) (compressed columns allowed), which is built

locally on the AMPs. The size of Spool 21 is estimated with

high confidence to be 15,037 rows. The estimated time for

this step is 0.00 seconds.

3) We do an all-AMPs RETRIEVE step from LOV by way of

index # 8 TYPE = 'PIPT' with a residual condition of (

LOV.TYPE = 'PIPT') locking for access into

Spool 22 (all_amps) (compressed columns allowed), which is

duplicated on all AMPs. The size of Spool 22 is estimated

with high confidence to be 32,700 rows. The estimated time

for this step is 0.04 seconds.

4) We do an all-AMPs RETRIEVE step from LOV by way of

an all-rows scan with a condition of (LOV.TYPE =

'PI_TSK') locking for access into Spool 23 (all_amps)

(compressed columns allowed), which is redistributed by hash

code to all AMPs. The size of Spool 23 is estimated with high

confidence to be 2,437 rows. The estimated time for this step

is 0.02 seconds.

16) We do an all-AMPs JOIN step from Spool 21 (Last Use) by way of an

all-rows scan, which is joined to Spool 22 (Last Use) by way of an

all-rows scan. Spool 21 and Spool 22 are joined using a single

partition hash join, with a join condition of (NM = A_01).

The result goes into Spool 24 (all_amps) (compressed columns

allowed), which is redistributed by hash code to all AMPs. The

size of Spool 24 is estimated with low confidence to be 14,253

rows. The estimated time for this step is 0.05 seconds.

17) We do an all-AMPs RETRIEVE step from LOVX by way of

an all-rows scan with a condition of ((NOT

(LOVX.OWNER IS NULL )) AND (NOT

(LOVX.ROW IS NULL ))) locking for access into

Spool 25 (all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. The size of Spool 25 is

estimated with high confidence to be 15,037 rows. The estimated

time for this step is 0.05 seconds.

18) We do an all-AMPs JOIN step from Spool 23 (Last Use) by way of an

all-rows scan, which is joined to Spool 24 (Last Use) by way of an

all-rows scan. Spool 23 and Spool 24 are joined using a single

partition hash join, with a join condition of (ROW = ROW).

The result goes into Spool 26 (all_amps) (compressed columns

allowed), which is redistributed by hash code to all AMPs. The

size of Spool 26 is estimated with low confidence to be 14,253

rows. The estimated time for this step is 0.05 seconds.

19) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from EMP by way

of an all-rows scan with no residual conditions locking for

access into Spool 27 (all_amps) (compressed columns allowed),

which is redistributed by hash code to all AMPs. The size of

Spool 27 is estimated with high confidence to be 34,691 rows.

The estimated time for this step is 0.09 seconds.

2) We do an all-AMPs JOIN step from PSTN by way of

a RowHash match scan with no residual conditions, which is

joined to PSTN by way of a RowHash match scan

with no residual conditions locking PSTN for

access and PSTN for access. PSTN

and PSTN are joined using a merge join, with a

join condition of (PSTN.ROW =

PSTN.ROW). The result goes into Spool 28

(all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. The size of Spool 28

is estimated with low confidence to be 35,698 rows. The

estimated time for this step is 0.09 seconds.

3) We do an all-AMPs RETRIEVE step from EMP by way

of an all-rows scan with no residual conditions locking for

access into Spool 29 (all_amps) (compressed columns allowed),

which is redistributed by hash code to all AMPs. The size of

Spool 29 is estimated with high confidence to be 34,691 rows.

The estimated time for this step is 0.09 seconds.

4) We do an all-AMPs JOIN step from PSTN by way of

a RowHash match scan with no residual conditions, which is

joined to PSTN by way of a RowHash match scan

with no residual conditions locking PSTN for

access and PSTN for access. PSTN

and PSTN are joined using a merge join, with a

join condition of (PSTN.ROW =

PSTN.ROW). The result goes into Spool 30

(all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. The size of Spool 30

is estimated with low confidence to be 35,698 rows. The

estimated time for this step is 0.09 seconds.

20) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way of an

all-rows scan, which is joined to Spool 20 (Last Use) by way of an

all-rows scan. Spool 19 and Spool 20 are joined using a single

partition hash join, with a join condition of (INTGRTN_ID =

INTGRTN_ID). The result goes into Spool 31 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 31 is estimated with low confidence to be 900

rows. The estimated time for this step is 0.01 seconds.

21) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from DAY by way of

an all-rows scan with a condition of ((DAY.DAYT

<= TIMESTAMP '2011-07-25 00:00:00') AND (DAY.DAYT

>= TIMESTAMP '2011-07-19 00:00:00')) into Spool 32 (all_amps)

(compressed columns allowed), which is duplicated on all AMPs.

The size of Spool 32 is estimated with high confidence to be

19,500 rows. The estimated time for this step is 0.03 seconds.

2) We do an all-AMPs JOIN step from Spool 31 (Last Use) by way of

an all-rows scan, which is joined to PI_PERF_F by

way of an all-rows scan with a condition of (

((PI_PERF_F.ACTN_CD = '13') OR

((PI_PERF_F.ACTN_CD = '2') OR

((PI_PERF_F.ACTN_CD = '5') OR

(PI_PERF_F.ACTN_CD = '8')))) AND

(((PI_PERF_F.STS_CD = 'P') OR

(PI_PERF_F.STS_CD = 'R')) AND

(((PI_PERF_F.ACTN_CD = '2') OR

(PI_PERF_F.STS_CD = 'P')) AND ((NOT

(PI_PERF_F.DEPT IS NULL )) AND ((NOT

(PI_PERF_F.TSK_TYPE IS NULL )) AND ((NOT

(PI_PERF_F.PIT IS NULL )) AND (NOT

(PI_PERF_F.QC_OPRTR IS NULL )))))))).

Spool 31 and PI_PERF_F are joined using a product

join, with a join condition of (ROW =

PI_PERF_F.OPRTR). The input table

PI_PERF_F will not be cached in memory, but it is

eligible for synchronized scanning. The result goes into

Spool 33 (all_amps) (compressed columns allowed), which is

built locally on the AMPs. The size of Spool 33 is estimated

with low confidence to be 13,627 rows. The estimated time for

this step is 1 minute and 1 second.

22) We do an all-AMPs JOIN step from Spool 25 (Last Use) by way of an

all-rows scan, which is joined to Spool 26 (Last Use) by way of an

all-rows scan. Spool 25 and Spool 26 are joined using a single

partition hash join, with a join condition of (ROW = ROW).

The result goes into Spool 34 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. The size of Spool

34 is estimated with low confidence to be 156,554 rows. The

estimated time for this step is 0.02 seconds.

23) We do an all-AMPs JOIN step from Spool 32 (Last Use) by way of an

all-rows scan, which is joined to Spool 33 (Last Use) by way of an

all-rows scan. Spool 32 and Spool 33 are joined using a single

partition hash join, with a join condition of (ROW =

PIT). The result goes into Spool 35 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs. The size of

Spool 35 is estimated with low confidence to be 121,350 rows. The

estimated time for this step is 0.05 seconds.

24) We do an all-AMPs JOIN step from Spool 27 (Last Use) by way of an

all-rows scan, which is joined to Spool 28 (Last Use) by way of an

all-rows scan. Spool 27 and Spool 28 are joined using a single

partition hash join, with a join condition of (INTGRTN_ID =

INTGRTN_ID). The result goes into Spool 36 (all_amps)

(compressed columns allowed), which is built locally on the AMPs.

The size of Spool 36 is estimated with low confidence to be 34,693

rows. The estimated time for this step is 0.01 seconds.

25) We do an all-AMPs JOIN step from Spool 34 (Last Use) by way of an

all-rows scan, which is joined to Spool 35 (Last Use) by way of an

all-rows scan. Spool 34 and Spool 35 are joined using a single

partition hash join, with a join condition of ((((LONGESC =

'UVW') OR (LONGESC = 'XYZ')) OR ((ACTN_CD =

'13') OR ((ACTN_CD = '2') OR (ACTN_CD = '8')))) AND

((ROW = TSK_TYPE) AND ((ROW = TSK_TYPE) AND

((ROW = DEPT) AND (ROW= DEPT ))))). The result

goes into Spool 37 (all_amps) (compressed columns allowed), which

is duplicated on all AMPs. The size of Spool 37 is estimated with

low confidence to be 91,050 rows. The estimated time for this

step is 0.05 seconds.

26) We do an all-AMPs RETRIEVE step from LOV by way of an

all-rows scan with no residual conditions locking for access into

Spool 38 (all_amps) (compressed columns allowed), which is built

locally on the AMPs. The size of Spool 38 is estimated with high

confidence to be 100,301 rows. The estimated time for this step

is 0.02 seconds.

27) We do an all-AMPs JOIN step from Spool 36 (Last Use) by way of an

all-rows scan, which is joined to Spool 37 (Last Use) by way of an

all-rows scan. Spool 36 and Spool 37 are joined using a single

partition hash join, with a join condition of (ROW =

OWNER). The result goes into Spool 39 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs. The size of

Spool 39 is estimated with low confidence to be 91,200 rows. The

estimated time for this step is 0.05 seconds.

28) We do an all-AMPs JOIN step from Spool 29 (Last Use) by way of an

all-rows scan, which is joined to Spool 30 (Last Use) by way of an

all-rows scan. Spool 29 and Spool 30 are joined using a single

partition hash join, with a join condition of (INTGRTN_ID =

INTGRTN_ID). The result goes into Spool 40 (all_amps)

(compressed columns allowed), which is built locally on the AMPs.

The size of Spool 40 is estimated with low confidence to be 34,693

rows. The estimated time for this step is 0.01 seconds.

29) We do an all-AMPs JOIN step from Spool 38 (Last Use) by way of an

all-rows scan, which is joined to Spool 39 (Last Use) by way of an

all-rows scan. Spool 38 and Spool 39 are joined using a single

partition hash join, with a join condition of (QC_FAIL =

ROW). The result goes into Spool 41 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs. The size of

Spool 41 is estimated with index join confidence to be 91,350 rows.

The estimated time for this step is 0.05 seconds.

30) We do an all-AMPs JOIN step from Spool 40 (Last Use) by way of an

all-rows scan, which is joined to Spool 41 (Last Use) by way of an

all-rows scan. Spool 40 and Spool 41 are joined using a single

partition hash join, with a join condition of (QC_OPRTR

= ROW). The result goes into Spool 4 (all_amps) (compressed

columns allowed), which is built locally on the AMPs. The size of

Spool 4 is estimated with index join confidence to be 610 rows.

The estimated time for this step is 0.02 seconds.

31) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by

way of an all-rows scan, and the grouping identifier in field 1.

Aggregate Intermediate Results are computed globally, then placed

in Spool 42. The size of Spool 42 is estimated with index join

confidence to be 610 rows. The estimated time for this step is

0.02 seconds.

32) We do an all-AMPs RETRIEVE step from Spool 42 (Last Use) by way of

an all-rows scan into Spool 1 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. The size of Spool 1

is estimated with index join confidence to be 610 rows. The

estimated time for this step is 0.01 seconds.

33) We do an all-AMPs STAT FUNCTION step from Spool 1 (Last Use) by

way of an all-rows scan into Spool 47 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 45 (all_amps) (compressed columns allowed), which is

built locally on the AMPs.

34) We do an all-AMPs STAT FUNCTION step from Spool 45 (Last Use) by

way of an all-rows scan into Spool 50 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 49 (all_amps) (compressed columns allowed), which is

built locally on the AMPs.

35) We do an all-AMPs STAT FUNCTION step from Spool 49 (Last Use) by

way of an all-rows scan into Spool 53 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 52 (all_amps) (compressed columns allowed), which is

built locally on the AMPs.

36) We do an all-AMPs STAT FUNCTION step from Spool 52 (Last Use) by

way of an all-rows scan into Spool 56 (Last Use), which is assumed

to be redistributed by value to all AMPs. The result rows are put

into Spool 55 (all_amps) (compressed columns allowed), which is

built locally on the AMPs.

37) We do an all-AMPs RETRIEVE step from Spool 55 (Last Use) by way of

an all-rows scan into Spool 2 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. The size of Spool 2

is estimated with no confidence to be 610 rows. The estimated

time for this step is 0.01 seconds.

38) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of

an all-rows scan into Spool 59 (group_amps), which is

redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 59 by the sort key in spool field1 eliminating

duplicate rows. The size of Spool 59 is estimated with no

confidence to be 610 rows. The estimated time for this step is

0.01 seconds.

39) Finally, we send out an END TRANSACTION step to all AMPs involved

in processing the request.

-> The contents of Spool 59 are sent back to the user as the result

of statement 1.

Thanks!

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-28-2011
12:12 PM

07-28-2011
12:12 PM

Dieter, I need ur suggestions on this one too

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-11-2011
02:07 PM

08-11-2011
02:07 PM

Dieter, need your help please!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

09-19-2011
04:18 AM

09-19-2011
04:18 AM

One way is to put subqueries to pull the required fields data from the table in hte FROM clause instead of joining the entire table. You have 23 tables being joined and there might be some tabls where you are pulling only few fields out of say 100 columns present. Put a subquery to these kind of data pull. It will surely improve performance of the query.

It would be more better if you split the BIG query into few smaller ones. Instead of performing in one SQL query, try achieving it in splitting the query into 2 or 3 SQL's.