How to optimize the query? The Explain plan is given below. Its showing no more spool space.

Teradata Applications

How to optimize the query? The Explain plan is given below. Its showing no more spool space.

Explain SELECT

LDA_RDM_JPMCC_NM_ADDR_MTH_V.CUSTOMER_OBJECT_ID,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ACCOUNT_NUMBER,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.SRC_SYS_CD,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_SRC_FILE_NB,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_ROW_NB,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_FILE_TYPE_CD,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_LD_DT,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_ACTN_CD

FROM

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V as LDA_RDM_JPMCC_NM_ADDR_MTH_V

WHERE

LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID=36585 AND

LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER is not null

and LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER not in (

sel LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER

from JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V

where

LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER='')

AND LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER NOT IN (

SEL TN_TX

FROM LOCATION_D9_W.PH_AD_JPM_SA2

WHERE

LOCATION_D9_W.PH_AD_JPM_SA2.CRE_RUN_ID = 36585 )

QUALIFY ROW_NUMBER() OVER

(partition by LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER

order by LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER desc) = 1

UNION

(

SELECT

LDA_RDM_JPMCC_NM_ADDR_DLY_V.CUSTOMER_OBJECT_ID,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.ACCOUNT_NUMBER,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.SRC_SYS_CD,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.CRE_RUN_ID,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.ETL_SRC_FILE_NB,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.ETL_ROW_NB,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.ETL_FILE_TYPE_CD,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.ETL_LD_DT,

LDA_RDM_JPMCC_NM_ADDR_DLY_V.ETL_ACTN_CD

FROM

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V as LDA_RDM_JPMCC_NM_ADDR_DLY_V

WHERE

LDA_RDM_JPMCC_NM_ADDR_DLY_V.CRE_RUN_ID=36585 AND

LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER is not null

and LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER not in (

sel LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER

from JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V

where

LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER='')

AND LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER NOT IN (

SEL TN_TX

FROM LOCATION_D9_W.PH_AD_JPM_SA2

WHERE

LOCATION_D9_W.PH_AD_JPM_SA2.CRE_RUN_ID = 36585 )

QUALIFY ROW_NUMBER() OVER

(partition by LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER

order by LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER desc) = 1

MINUS

SELECT

LDA_RDM_JPMCC_NM_ADDR_MTH_V.CUSTOMER_OBJECT_ID,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ACCOUNT_NUMBER,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.SRC_SYS_CD,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_SRC_FILE_NB,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_ROW_NB,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_FILE_TYPE_CD,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_LD_DT,

LDA_RDM_JPMCC_NM_ADDR_MTH_V.ETL_ACTN_CD

FROM

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V as LDA_RDM_JPMCC_NM_ADDR_MTH_V

WHERE

LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID=36585 AND

LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER is not null

and LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER not in (

sel LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER

from JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V

where

LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER='')

AND LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER NOT IN (

SEL TN_TX

FROM LOCATION_D9_W.PH_AD_JPM_SA2

WHERE

LOCATION_D9_W.PH_AD_JPM_SA2.CRE_RUN_ID = 36585 )

QUALIFY ROW_NUMBER() OVER

(partition by LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER

order by LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER desc) = 1

)

1) First, we lock JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V for access, we lock

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V for access, and we lock

LOCATION_D9_W.PH_AD_JPM_SA2 for access.

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

1) We do an all-AMPs RETRIEVE step from

LOCATION_D9_W.PH_AD_JPM_SA2 by way of an all-rows scan with a

condition of ("LOCATION_D9_W.PH_AD_JPM_SA2.CRE_RUN_ID = 36585")

into Spool 9 (all_amps), which is redistributed by the hash

code of (LOCATION_D9_W.PH_AD_JPM_SA2.TN_TX) to all AMPs.

Then we do a SORT to order Spool 9 by the sort key in spool

field1 eliminating duplicate rows. The size of Spool 9 is

estimated with low confidence to be 1 row (150 bytes). The

estimated time for this step is 0.03 seconds.

2) We do an all-AMPs SUM step to aggregate from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V by way of an all-rows

scan with a condition of ("(NOT

(JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER IS

NULL )) AND (JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID = 36585)").

Aggregate Intermediate Results are computed globally, then

placed in Spool 5.

3) We do an all-AMPs SUM step to aggregate from Spool 9 by way of an

all-rows scan. Aggregate Intermediate Results are computed

globally, then placed in Spool 10.

4) We execute the following steps in parallel.

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

all-rows scan into Spool 3 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs.

2) We do an all-AMPs RETRIEVE step from Spool 5 by way of an

all-rows scan into Spool 4 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs.

5) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V by way of an all-rows

scan with a condition of ("(NOT

(JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER IS

NULL )) AND (JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID = 36585)")

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

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

Spool 7 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER),

and null value information in Spool 4 and Spool 3. Skip this

retrieve step if null exists. The size of Spool 7 is

estimated with low confidence to be 2,878,926 rows (

175,614,486 bytes). The estimated time for this step is 1.19

seconds.

2) We do an all-AMPs RETRIEVE step from Spool 9 by way of an

all-rows scan into Spool 8 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs. Then we do a SORT

to order Spool 8 by the hash code of (

LOCATION_D9_W.PH_AD_JPM_SA2.TN_TX), and null value

information in Spool 4 and Spool 3. Skip this retrieve step

if null exists. The size of Spool 8 is estimated with low

confidence to be 144 rows (21,600 bytes).

6) We execute the following steps in parallel.

1) 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 an exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = TN_TX"), and null value information in

Spool 4 and Spool 3. Skip this join step if null exists.

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

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

index join confidence to be 2,878,926 rows (175,614,486

bytes). The estimated time for this step is 0.53 seconds.

2) We do an all-AMPs RETRIEVE step from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V by way of an all-rows

scan with a condition of ("(NOT

(JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER IS

NULL )) AND (JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID = 36585)")

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

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

Spool 17 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER),

and null value information in Spool 4 and Spool 3. Skip this

retrieve step if there is no null. The size of Spool 17 is

estimated with low confidence to be 2,878,926 rows (

175,614,486 bytes). The estimated time for this step is 1.19

seconds.

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

all-rows scan into Spool 18 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs. Then we do a SORT

to order Spool 18 by the hash code of (

LOCATION_D9_W.PH_AD_JPM_SA2.TN_TX), and null value

information in Spool 4 and Spool 3. Skip this retrieve step

if there is no null. The size of Spool 18 is estimated with

low confidence to be 144 rows (21,600 bytes). The estimated

time for this step is 0.01 seconds.

7) We execute the following steps in parallel.

1) 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 an exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = TN_TX"), and null value information in

Spool 4 (Last Use) and Spool 3 (Last Use). Skip this join

step if there is no null. The result goes into Spool 16

(all_amps), which is built locally on the AMPs. The size of

Spool 16 is estimated with index join confidence to be

2,878,926 rows (175,614,486 bytes). The estimated time for

this step is 0.53 seconds.

2) We do an all-AMPs RETRIEVE step from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T by way of an all-rows

scan with a condition of (

"JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER =

''") locking for access into Spool 21 (all_amps), which is

redistributed by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER) to

all AMPs. Then we do a SORT to order Spool 21 by the sort

key in spool field1 eliminating duplicate rows. The size of

Spool 21 is estimated with high confidence to be 1,524,638

rows (65,559,434 bytes). The estimated time for this step is

21.54 seconds.

8) We do an all-AMPs SUM step to aggregate from Spool 16 by way of an

all-rows scan. Aggregate Intermediate Results are computed

globally, then placed in Spool 14.

9) We do an all-AMPs SUM step to aggregate from Spool 21 by way of an

all-rows scan. Aggregate Intermediate Results are computed

globally, then placed in Spool 22.

10) We execute the following steps in parallel.

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

all-rows scan into Spool 12 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs.

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

way of an all-rows scan into Spool 13 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs.

11) We execute the following steps in parallel.

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

all-rows scan into Spool 19 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. Then we do a

SORT to order Spool 19 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 13 and Spool 12. Skip this

retrieve step if null exists. The size of Spool 19 is

estimated with index join confidence to be 2,878,926 rows (

175,614,486 bytes). The estimated time for this step is 0.53

seconds.

2) We do an all-AMPs RETRIEVE step from Spool 21 by way of an

all-rows scan into Spool 20 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs. Then we do a SORT

to order Spool 20 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 13 and Spool 12. Skip this

retrieve step if null exists. The size of Spool 20 is

estimated with high confidence to be 219,547,872 rows (

9,440,558,496 bytes).

12) We execute the following steps in parallel.

1) 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 an exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = WORK_PHONE_NUMBER"), and null value

information in Spool 13 and Spool 12. Skip this join step if

null exists. The result goes into Spool 2 (all_amps), which

is built locally on the AMPs. The size of Spool 2 is

estimated with index join confidence to be 2,878,926 rows (

181,372,338 bytes). The estimated time for this step is 0.42

seconds.

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

way of an all-rows scan into Spool 24 (all_amps) (compressed

columns allowed), which is built locally on the AMPs. Then we

do a SORT to order Spool 24 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 13 and Spool 12. Skip this

retrieve step if there is no null. The size of Spool 24 is

estimated with index join confidence to be 2,878,926 rows (

175,614,486 bytes). The estimated time for this step is 0.32

seconds.

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

all-rows scan into Spool 25 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs. Then we do a SORT

to order Spool 25 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 13 and Spool 12. Skip this

retrieve step if there is no null. The size of Spool 25 is

estimated with high confidence to be 219,547,872 rows (

9,440,558,496 bytes). The estimated time for this step is

16.98 seconds.

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

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

all-rows scan. Spool 24 and Spool 25 are joined using an

exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = WORK_PHONE_NUMBER"), and null value

information in Spool 13 (Last Use) and Spool 12 (Last Use). Skip

this join step if there is no null. The result goes into Spool 2

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

2 is estimated with index join confidence to be 2,878,926 rows (

181,372,338 bytes). The estimated time for this step is 0.42

seconds.

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

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

redistributed by hash code to all AMPs. The result rows are put

into Spool 1 (group_amps), which is redistributed by hash code to

all AMPs. The size is estimated with index join confidence to be

941,796 rows (145,978,380 bytes).

15) We execute the following steps in parallel.

1) We do an all-AMPs SUM step to aggregate from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V by way of an all-rows

scan with a condition of (

"(JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V.CRE_RUN_ID = 36585) AND

(NOT (JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER IS

NULL ))"). Aggregate Intermediate Results are computed

globally, then placed in Spool 35.

2) We do an all-AMPs RETRIEVE step from Spool 10 by way of an

all-rows scan into Spool 33 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs.

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

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

allowed), which is duplicated on all AMPs.

17) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V by way of an all-rows

scan with a condition of (

"(JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V.CRE_RUN_ID = 36585) AND

(NOT (JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER IS

NULL ))") into Spool 37 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. Then we do a

SORT to order Spool 37 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.WORK_PHONE_NUMBER), and

null value information in Spool 34 and Spool 33. Skip this

retrieve step if null exists. The size of Spool 37 is

estimated with low confidence to be 359 rows (21,899 bytes).

The estimated time for this step is 0.01 seconds.

2) We do an all-AMPs RETRIEVE step from Spool 9 by way of an

all-rows scan into Spool 38 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs. Then we do a SORT

to order Spool 38 by the hash code of (

LOCATION_D9_W.PH_AD_JPM_SA2.TN_TX), and null value information

in Spool 34 and Spool 33. Skip this retrieve step if null

exists. The size of Spool 38 is estimated with low confidence

to be 144 rows (21,600 bytes).

18) We execute the following steps in parallel.

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

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

way of an all-rows scan. Spool 37 and Spool 38 are joined

using an exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = TN_TX"), and null value information in

Spool 34 and Spool 33. Skip this join step if null exists.

The result goes into Spool 42 (all_amps), which is built

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

index join confidence to be 359 rows (24,771 bytes). The

estimated time for this step is 0.08 seconds.

2) We do an all-AMPs RETRIEVE step from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V by way of an all-rows

scan with a condition of (

"(JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V.CRE_RUN_ID = 36585) AND

(NOT (JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_DLY_V.WORK_PHONE_NUMBER IS

NULL ))") into Spool 43 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. Then we do a

SORT to order Spool 43 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.WORK_PHONE_NUMBER), and

null value information in Spool 34 and Spool 33. Skip this

retrieve step if there is no null. The size of Spool 43 is

estimated with low confidence to be 359 rows (21,899 bytes).

The estimated time for this step is 0.01 seconds.

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

all-rows scan into Spool 44 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs. Then we do a SORT

to order Spool 44 by the hash code of (

LOCATION_D9_W.PH_AD_JPM_SA2.TN_TX), and null value information

in Spool 34 and Spool 33. Skip this retrieve step if there is

no null. The size of Spool 44 is estimated with low

confidence to be 144 rows (21,600 bytes). The estimated time

for this step is 0.01 seconds.

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

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

all-rows scan. Spool 43 and Spool 44 are joined using an

exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = TN_TX"), and null value information in Spool

34 (Last Use) and Spool 33 (Last Use). Skip this join step if

there is no null. The result goes into Spool 42 (all_amps), which

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

with index join confidence to be 359 rows (24,771 bytes). The

estimated time for this step is 0.08 seconds.

20) We do an all-AMPs RETRIEVE step from Spool 42 by way of an

all-rows scan into Spool 45 (all_amps) (compressed columns

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

is estimated with index join confidence to be 51,696 rows (

3,567,024 bytes). The estimated time for this step is 0.08

seconds.

21) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 46 (all_amps) (compressed

columns allowed), which is redistributed by the hash code of (

UniqueId) to all AMPs. Then we do a SORT to order Spool 46 by

row hash. The size of Spool 46 is estimated with index join

confidence to be 359 rows (24,771 bytes). The estimated time

for this step is 0.06 seconds.

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

an all-rows scan, which is joined to

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T by way of an all-rows

scan with no residual conditions locking

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T for access. Spool 45

and JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T are joined using a

product join, with a join condition of ("WORK_PHONE_NUMBER =

''"). The result goes into Spool 47 (all_amps), which is

redistributed by the hash code of (UniqueId) to all AMPs.

Then we do a SORT to order Spool 47 by row hash and the sort

key in spool field1 eliminating duplicate rows. The size of

Spool 47 is estimated with index join confidence to be

1,033,534,434 rows (60,978,531,606 bytes). The estimated time

for this step is 5 minutes and 26 seconds.

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

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

all-rows scan. Spool 46 and Spool 47 are joined using an

exclusion merge join, with a join condition of ("(NOT (UniqueId IS

NULL )) AND (NOT (UniqueId IS NULL ))"), and an additional join

condition of ("(UniqueId = UniqueId) AND (WORK_PHONE_NUMBER =

WORK_PHONE_NUMBER)"). The result goes into Spool 32 (all_amps)

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

The size of Spool 32 is estimated with index join confidence to be

359 rows (22,617 bytes). The estimated time for this step is 0.04

seconds.

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

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

redistributed by hash code to all AMPs. The result rows are put

into Spool 31 (all_amps), which is redistributed by hash code to

all AMPs. Then we do a SORT to order Spool 31 by the sort key in

spool field1 eliminating duplicate rows. The size is estimated

with index join confidence to be 359 rows (55,645 bytes).

24) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from Spool 10 (Last Use) by

way of an all-rows scan into Spool 55 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs.

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

of an all-rows scan into Spool 56 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs.

25) We execute the following steps in parallel.

1) We do an all-AMPs RETRIEVE step from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V by way of an all-rows

scan with a condition of ("(NOT

(JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER IS

NULL )) AND (JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID = 36585)")

locking for access into Spool 59 (all_amps) (compressed

columns allowed), which is built locally on the AMPs. Then we

do a SORT to order Spool 59 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 56 and Spool 55. Skip this

retrieve step if null exists. The size of Spool 59 is

estimated with low confidence to be 2,878,926 rows (

175,614,486 bytes). The estimated time for this step is 1.19

seconds.

2) We do an all-AMPs RETRIEVE step from Spool 9 by way of an

all-rows scan into Spool 60 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs. Then we do a SORT

to order Spool 60 by the hash code of (

LOCATION_D9_W.PH_AD_JPM_SA2.TN_TX), and null value information

in Spool 56 and Spool 55. Skip this retrieve step if null

exists. The size of Spool 60 is estimated with low confidence

to be 144 rows (21,600 bytes).

26) We execute the following steps in parallel.

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

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

way of an all-rows scan. Spool 59 and Spool 60 are joined

using an exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = TN_TX"), and null value information in

Spool 56 and Spool 55. Skip this join step if null exists.

The result goes into Spool 68 (all_amps), which is built

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

index join confidence to be 2,878,926 rows (175,614,486 bytes).

The estimated time for this step is 0.53 seconds.

2) We do an all-AMPs RETRIEVE step from

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V by way of an all-rows

scan with a condition of ("(NOT

(JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.WORK_PHONE_NUMBER IS

NULL )) AND (JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T in view

JPMCC_D9_V.LDA_RDM_JPMCC_NM_ADDR_MTH_V.CRE_RUN_ID = 36585)")

locking for access into Spool 69 (all_amps) (compressed

columns allowed), which is built locally on the AMPs. Then we

do a SORT to order Spool 69 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 56 and Spool 55. Skip this

retrieve step if there is no null. The size of Spool 69 is

estimated with low confidence to be 2,878,926 rows (

175,614,486 bytes). The estimated time for this step is 1.19

seconds.

3) We do an all-AMPs RETRIEVE step from Spool 9 (Last Use) by way

of an all-rows scan into Spool 70 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs. Then we do

a SORT to order Spool 70 by the hash code of (

LOCATION_D9_W.PH_AD_JPM_SA2.TN_TX), and null value information

in Spool 56 and Spool 55. Skip this retrieve step if there is

no null. The size of Spool 70 is estimated with low

confidence to be 144 rows (21,600 bytes). The estimated time

for this step is 0.01 seconds.

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

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

all-rows scan. Spool 69 and Spool 70 are joined using an

exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = TN_TX"), and null value information in Spool

56 (Last Use) and Spool 55 (Last Use). Skip this join step if

there is no null. The result goes into Spool 68 (all_amps), which

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

with index join confidence to be 2,878,926 rows (175,614,486

bytes). The estimated time for this step is 0.53 seconds.

28) We execute the following steps in parallel.

1) We do an all-AMPs SUM step to aggregate from Spool 68 by way

of an all-rows scan. Aggregate Intermediate Results are

computed globally, then placed in Spool 66.

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

way of an all-rows scan into Spool 64 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs.

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

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

allowed), which is duplicated on all AMPs.

30) We execute the following steps in parallel.

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

all-rows scan into Spool 71 (all_amps) (compressed columns

allowed), which is built locally on the AMPs. Then we do a

SORT to order Spool 71 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 65 and Spool 64. Skip this

retrieve step if null exists. The size of Spool 71 is

estimated with index join confidence to be 2,878,926 rows (

175,614,486 bytes). The estimated time for this step is 0.53

seconds.

2) We do an all-AMPs RETRIEVE step from Spool 21 by way of an

all-rows scan into Spool 72 (all_amps) (compressed columns

allowed), which is duplicated on all AMPs. Then we do a SORT

to order Spool 72 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 65 and Spool 64. Skip this

retrieve step if null exists. The size of Spool 72 is

estimated with high confidence to be 219,547,872 rows (

9,440,558,496 bytes).

31) We execute the following steps in parallel.

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

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

way of an all-rows scan. Spool 71 and Spool 72 are joined

using an exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = WORK_PHONE_NUMBER"), and null value

information in Spool 65 and Spool 64. Skip this join step if

null exists. The result goes into Spool 54 (all_amps), which

is built locally on the AMPs. The size of Spool 54 is

estimated with index join confidence to be 2,878,926 rows (

181,372,338 bytes). The estimated time for this step is 0.42

seconds.

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

way of an all-rows scan into Spool 76 (all_amps) (compressed

columns allowed), which is built locally on the AMPs. Then we

do a SORT to order Spool 76 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 65 and Spool 64. Skip this

retrieve step if there is no null. The size of Spool 76 is

estimated with index join confidence to be 2,878,926 rows (

175,614,486 bytes). The estimated time for this step is 0.32

seconds.

3) We do an all-AMPs RETRIEVE step from Spool 21 (Last Use) by

way of an all-rows scan into Spool 77 (all_amps) (compressed

columns allowed), which is duplicated on all AMPs. Then we do

a SORT to order Spool 77 by the hash code of (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_MTH_T.WORK_PHONE_NUMBER), and

null value information in Spool 65 and Spool 64. Skip this

retrieve step if there is no null. The size of Spool 77 is

estimated with high confidence to be 219,547,872 rows (

9,440,558,496 bytes). The estimated time for this step is

16.98 seconds.

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

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

all-rows scan. Spool 76 and Spool 77 are joined using an

exclusion merge join, with a join condition of (

"WORK_PHONE_NUMBER = WORK_PHONE_NUMBER"), and null value

information in Spool 65 (Last Use) and Spool 64 (Last Use). Skip

this join step if there is no null. The result goes into Spool 54

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

54 is estimated with index join confidence to be 2,878,926 rows (

181,372,338 bytes). The estimated time for this step is 0.42

seconds.

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

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

redistributed by hash code to all AMPs. The result rows are put

into Spool 53 (all_amps), which is redistributed by hash code to

all AMPs. Then we do a SORT to order Spool 53 by the sort key in

spool field1 eliminating duplicate rows. The size is estimated

with index join confidence to be 941,796 rows (145,978,380 bytes).

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

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

all-rows scan. Spool 31 and Spool 53 are joined using an

exclusion merge join, with a join condition of ("Field_1 = Field_1").

The result goes into Spool 1 (group_amps), which is built locally

on the AMPs. Then we do a SORT to order Spool 1 by the sort key

in spool field1 (

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.CUSTOMER_OBJECT_ID,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.ACCOUNT_NUMBER,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.WORK_PHONE_NUMBER,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.SRC_SYS_CD,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.CRE_RUN_ID,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.ETL_SRC_FILE_NB,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.ETL_ROW_NB,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.ETL_FILE_TYPE_CD,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.ETL_LD_DT,

JPMCC_D9_T.LDA_RDM_JPMCC_NM_ADDR_DLY_T.ETL_ACTN_CD) eliminating

duplicate rows. The size of Spool 1 is estimated with index join

confidence to be 941,797 rows (145,978,535 bytes). The estimated

time for this step is 0.19 seconds.

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

in processing the request.

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

statement 1.

2 REPLIES
Teradata Employee

Re: How to optimize the query? The Explain plan is given below. Its showing no more spool space.

Whats the skewnes of underlying tables?

N/A

Re: How to optimize the query? The Explain plan is given below. Its showing no more spool space.

1st step: rewrite the NOT IN to NOT EXISTS (rule of thumb: try to avoid NOT IN as much as possible)

And the logic is quite complex, did you check if it could be simplified? The base table is always the same.

Dieter