TUNING THE join

Database
Enthusiast

TUNING THE join

Hi ,

I have a query like below. It is costing more . pls suggest how can i tune this.

 

SELECT 'UNKNOWN' , 'UNKNOWN' , 0 , - 999 , 

C.BR_PARTY_KEY ,

 'UNKNOWN' ,SUM ( A.EX_QTY ) , 

SUM ( CAST( A.LIST_PRICE_AMT AS DOUBLE PRECISION ) ) , 

SUM ( CAST( A.NET_PRICE_AMT AS DOUBLE PRECISION ) ) , 

SUM ( CAST( A.COST_AMT AS DOUBLE PRECISION ) ) ,

 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'UNKNOWN' ,

  'N' , 'N' , 'N' 

FROM ORDDB.BKGS A,

REFDB.CUST_PARTY C

where A.CUST_KEY = C.CUST_KEY 

AND a.ser_flag = 'N' 

GROUP BY C.BR_PARTY_KEY )

 

Index: 

 

REFDB.CUST_PARTY                              ORDB.BKGS

-------------------                              -----------

CUST_KEY - UPI                                   PR_KEY,SALES_TERR_KEY,CUST_KEY  - NUPI

BR_PARTY_KEY - NUSI                           CUST_KEY   -  NUSI

 

EXPLAIN:

 

1) First, we lock REFDB.CUST_PARTY in view

     REFDB.CUST_PARTY for access, and we lock

     ORDDB.BKGS in view ORDVWDB.BKGS

     for access. 

  2) Next, we do an all-AMPs RETRIEVE step from

     ORDDB.BKGS in view ORDVWDB.BKGS by

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

     "ORDDB.BKGS in view

     ORDVWDB.BKGS.SER_FLAG = 'N'") into Spool 6

     (all_amps) (compressed columns allowed) fanned out into 6 hash

     join partitions, which is built locally on the AMPs.  The size of

     Spool 6 is estimated with high confidence to be 161,152,199 rows (

     7,896,457,751 bytes).  The estimated time for this step is 2.66

     seconds. 

  3) We do an all-AMPs RETRIEVE step from

     REFDB.CUST_PARTY in view

     REFVWDB.CUST_PARTY by way of an all-rows

     scan with no residual conditions into Spool 7 (all_amps)

     (compressed columns allowed) fanned out into 6 hash join

     partitions, which is duplicated on all AMPs.  The result spool

     file will not be cached in memory.  The size of Spool 7 is

     estimated with high confidence to be 22,900,755,150 rows (

     389,312,837,550 bytes).  The estimated time for this step is 3

     minutes and 24 seconds. 

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

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

     all-rows scan.  Spool 6 and Spool 7 are joined using a hash join

     of 6 partitions, with a join condition of ("CUST_KEY =

     CUST_KEY").  The result goes into Spool 5 (all_amps)

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

     The size of Spool 5 is estimated with low confidence to be

     161,152,199 rows (8,218,762,149 bytes).  The estimated time for

     this step is 34.67 seconds. 

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

     way of an all-rows scan , grouping by field1 (

     REFDB.CUST_PARTY.CUST_KEY). 

     Aggregate Intermediate Results are computed globally, then placed

     in Spool 8.  The size of Spool 8 is estimated with low confidence

     to be 8,105,693 rows (429,601,729 bytes).  The estimated time for

     this step is 13.26 seconds. 

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

     an all-rows scan into Spool 3 (group_amps), which is built locally

     on the AMPs.  The size of Spool 3 is estimated with low confidence

     to be 8,105,693 rows (972,683,160 bytes).  The estimated time for

     this step is 0.10 seconds. 

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

     in processing the request.

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

     statement 1.  The total estimated time is 4 minutes and 15 seconds. 

Thanks in Advance

Harshitha.

4 REPLIES
Junior Contributor

Re: TUNING THE join

Hi Harshitha,

the NUSI on CUST_KEY will not be used for the join, only a NUPI on CUST_KEY, but this will probably skew your table and create other problems.

You might change that single aggregate to two steps:

SELECT 'UNKNOWN' , 'UNKNOWN' , 0 , - 999 , 
C.BR_PARTY_KEY ,
'UNKNOWN' ,SUM ( A.EX_QTY ) ,
SUM ( CAST( A.LIST_PRICE_AMT AS DOUBLE PRECISION ) ) ,
SUM ( CAST( A.NET_PRICE_AMT AS DOUBLE PRECISION ) ) ,
SUM ( CAST( A.COST_AMT AS DOUBLE PRECISION ) ) ,
'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'UNKNOWN' ,
'N' , 'N' , 'N'
FROM
(
SELECT
CUST_KEY,
SUM ( A.EX_QTY ) AS EX_QTY,
SUM ( CAST( A.LIST_PRICE_AMT AS DOUBLE PRECISION ) ) AS LIST_PRICE_AMT,
SUM ( CAST( A.NET_PRICE_AMT AS DOUBLE PRECISION ) ) AS NET_PRICE_AMT,
SUM ( CAST( A.COST_AMT AS DOUBLE PRECISION ) ) AS COST_AMT
FROM ORDDB.BKGS A
WHERE a.ser_flag = 'N'
GROUP BY 1
) A,
REFDB.CUST_PARTY C
WHERE A.CUST_KEY = C.CUST_KEY
GROUP BY C.BR_PARTY_KEY

Thus you greatly reduce the number of rows before the join.

And i'd suggest changing the DOUBLE PRECISION to a DECIMAL, as DOUBLE is the same as FLOAT, limited to 15-16 significant digits, wheras DEC is up to 38 digits.

Dieter

Enthusiast

Re: TUNING THE join

Hi Dieter,

First of all thank you very much for the reply. It worked fine. But i am getting 

REFDB.A.EX_QTY does not exists. i ran the query like below

SELECT 'UNKNOWN' , 'UNKNOWN' , 0 , - 999 ,

C.BR_PARTY_KEY ,

 'UNKNOWN' ,

/* SUM ( A.EX_QTY ) ,

SUM ( CAST( A.LIST_PRICE_AMT AS DOUBLE PRECISION ) ) ,

SUM ( CAST( A.NET_PRICE_AMT AS DOUBLE PRECISION ) ) ,

SUM ( CAST( A.COST_AMT AS DOUBLE PRECISION ) ) , */

 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'N' , 'UNKNOWN' ,

  'N' , 'N' , 'N'

FROM

 (

   SELECT

     CUST_KEY,

     SUM ( A.EX_QTY ) AS EX_QTY,

     SUM ( CAST( A.LIST_PRICE_AMT AS DOUBLE PRECISION ) ) AS LIST_PRICE_AMT,

     SUM ( CAST( A.NET_PRICE_AMT AS DOUBLE PRECISION ) ) AS NET_PRICE_AMT,

     SUM ( CAST( A.COST_AMT AS DOUBLE PRECISION ) ) AS COST_AMT

   FROM ORDDB.BKGS A

   WHERE a.ser_flag = 'N'

   GROUP BY 1

 ) A,

REFDB.CUST_PARTY C

WHERE A.CUST_KEY = C.CUST_KEY

GROUP BY C.BR_PARTY_KEY

 

80% of the Cost is reduced.

 

Thanks once again,

Harshitha.

Enthusiast

Re: TUNING THE join

SELECT

W_INT_ORG_D.INTEGRATION_ID BU_ID,

W_POSITION_D.OU_ID DIVISION_ID,

MIN(W_POSITION_D.INTEGRATION_ID) POSITION_ID,

(CASE WHEN SUBSTR( trim(cast(( current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))), POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)))),3) >= 12 THEN CAST(SUBSTR(TRIM(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))),0,POSITION(' ' in trim( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ))) AS DECIMAL(10))+1 ELSE CAST(SUBSTR(TRIM( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ),0,POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))))) AS DECIMAL(10)) END) LAST_TXN_BEFORE_DAYS, MIN(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' or W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_VEH_ORDER_DT,

Max(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then

CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_VEH_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE ='Sales Order' OR W_ORDER_D.ORDER_TYPE ='Sales Order-Direct Billing'

then W_ORDER_D.ORDER_NUM end ) NO_OF_VEH_ORDERS,

MIN(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_VEH_INV_DT,

Max(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_VEH_INV_DT1,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing'

then W_INVOICE_F.INVC_NUM end ) NO_OF_VEH_INVOICED,

min(case when W_ORDER_D.ORDER_TYPE ='OTC Sales' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_OTC_INV_DT ,

max(case when W_ORDER_D.ORDER_TYPE ='OTC Sales' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_OTC_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'OTC Sales' then W_INVOICE_F.INVC_NUM end ) NO_OF_OTC_INVOICES,

min(case when W_ORDER_D.ORDER_TYPE ='Service Order' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_SERV_INV_DT,

MAX(case when W_ORDER_D.ORDER_TYPE ='Service Order' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_SERV_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Service Order'

then W_INVOICE_F.INVC_NUM end ) NO_OF_SERV_INVOICES,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Service Order' then W_ORDER_D.ORDER_NUM end ) NO_OF_JOB_CARDS ,

MAX(CAST(CAST( ( a.OPEN_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) ) LAST_OPTY_DT ,

MIN(CAST(CAST( (a.OPEN_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) ) FIRST_OPTY_DT ,

Max(case when W_ORDER_D.ORDER_TYPE ='Sales Order' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_JC_CREATED_DT,

Min(case when W_ORDER_D.ORDER_TYPE ='Sales Order'then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_JC_CREATED_DT

FROM W_ORDER_D

LEFT JOIN W_INT_ORG_D on

W_ORDER_D.VIS_PR_BU_ID = W_INT_ORG_D.INTEGRATION_ID

LEFT JOIN W_INVOICE_F ON

W_ORDER_D.ROW_WID = W_INVOICE_F.ORDER_WID

LEFT JOIN W_POSITION_D ON

W_ORDER_D.VIS_PR_POS_ID = W_POSITION_D.INTEGRATION_ID

LEFT JOIN W_OPTY_D a ON

W_ORDER_D.VIS_PR_BU_ID = a.VIS_PR_BU_ID

WHERE W_ORDER_D.ORDER_TYPE IN ('Sales Order-Direct Billing', 'Sales Order', 'Service Order', 'OTC Sales')

AND W_ORDER_D.STATUS_CD NOT IN ('CANCELLED','Cancel','Cancelled')

AND W_INT_ORG_D.NAME <> 'Unspecified'

AND w_invoice_f.X_INVC_DT IS NOT NULL

AND COALESCE(W_INVOICE_F.INVC_TYPE_CD_WID,11515) = 11515

AND COALESCE(W_INVOICE_F.STATUS_CD_WID,16778 ) = 16778

GROUP BY ROLLUP(W_POSITION_D.OU_ID), W_INT_ORG_D.INTEGRATION_ID,

(CASE WHEN SUBSTR( trim(cast(( current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))), POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)))),3) >= 12 THEN CAST(SUBSTR(TRIM(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))),0,POSITION(' ' in trim( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ))) AS DECIMAL(10))+1 ELSE CAST(SUBSTR(TRIM( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ),0,POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))))) AS DECIMAL(10)) END)

Explain plan :-

Explain SELECT

W_INT_ORG_D.INTEGRATION_ID BU_ID,

W_POSITION_D.OU_ID DIVISION_ID,

MIN(W_POSITION_D.INTEGRATION_ID) POSITION_ID,

(CASE WHEN SUBSTR( trim(cast(( current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))), POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)))),3) >= 12 THEN CAST(SUBSTR(TRIM(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))),0,POSITION(' ' in trim( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ))) AS DECIMAL(10))+1 ELSE CAST(SUBSTR(TRIM( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ),0,POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))))) AS DECIMAL(10)) END) LAST_TXN_BEFORE_DAYS, MIN(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' or W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_VEH_ORDER_DT,

Max(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then

CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_VEH_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE ='Sales Order' OR W_ORDER_D.ORDER_TYPE ='Sales Order-Direct Billing'

then W_ORDER_D.ORDER_NUM end ) NO_OF_VEH_ORDERS,

MIN(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_VEH_INV_DT,

Max(case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_VEH_INV_DT1,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Sales Order' OR W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing'

then W_INVOICE_F.INVC_NUM end ) NO_OF_VEH_INVOICED,

min(case when W_ORDER_D.ORDER_TYPE ='OTC Sales' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_OTC_INV_DT ,

max(case when W_ORDER_D.ORDER_TYPE ='OTC Sales' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_OTC_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'OTC Sales' then W_INVOICE_F.INVC_NUM end ) NO_OF_OTC_INVOICES,

min(case when W_ORDER_D.ORDER_TYPE ='Service Order' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_SERV_INV_DT,

MAX(case when W_ORDER_D.ORDER_TYPE ='Service Order' then CAST(CAST( ( w_invoice_f.X_INVC_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_SERV_INV_DT,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Service Order'

then W_INVOICE_F.INVC_NUM end ) NO_OF_SERV_INVOICES,

COUNT(DISTINCT case when W_ORDER_D.ORDER_TYPE = 'Service Order' then W_ORDER_D.ORDER_NUM end ) NO_OF_JOB_CARDS ,

MAX(CAST(CAST( ( a.OPEN_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) ) LAST_OPTY_DT ,

MIN(CAST(CAST( (a.OPEN_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) ) FIRST_OPTY_DT ,

Max(case when W_ORDER_D.ORDER_TYPE ='Sales Order' then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) LAST_JC_CREATED_DT,

Min(case when W_ORDER_D.ORDER_TYPE ='Sales Order'then CAST(CAST( ( W_ORDER_D.ORDER_DT ) AS DATE FORMAT 'DD-MMM-YY' ) AS CHAR(10)) end) FIRST_JC_CREATED_DT

FROM W_ORDER_D

LEFT JOIN W_INT_ORG_D on

W_ORDER_D.VIS_PR_BU_ID = W_INT_ORG_D.INTEGRATION_ID

LEFT JOIN W_INVOICE_F ON

W_ORDER_D.ROW_WID = W_INVOICE_F.ORDER_WID

LEFT JOIN W_POSITION_D ON

W_ORDER_D.VIS_PR_POS_ID = W_POSITION_D.INTEGRATION_ID

LEFT JOIN W_OPTY_D a ON

W_ORDER_D.VIS_PR_BU_ID = a.VIS_PR_BU_ID

WHERE W_ORDER_D.ORDER_TYPE IN ('Sales Order-Direct Billing', 'Sales Order', 'Service Order', 'OTC Sales')

AND W_ORDER_D.STATUS_CD NOT IN ('CANCELLED','Cancel','Cancelled')

AND W_INT_ORG_D.NAME <> 'Unspecified'

AND w_invoice_f.X_INVC_DT IS NOT NULL

AND COALESCE(W_INVOICE_F.INVC_TYPE_CD_WID,11515) = 11515

AND COALESCE(W_INVOICE_F.STATUS_CD_WID,16778 ) = 16778

GROUP BY ROLLUP(W_POSITION_D.OU_ID), W_INT_ORG_D.INTEGRATION_ID,

(CASE WHEN SUBSTR( trim(cast(( current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))), POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)))),3) >= 12 THEN CAST(SUBSTR(TRIM(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))),0,POSITION(' ' in trim( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ))) AS DECIMAL(10))+1 ELSE CAST(SUBSTR(TRIM( cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50)) ),0,POSITION(' ' in trim(cast((current_timestamp - X_INVC_DT day(4) to second ) as varchar(50))))) AS DECIMAL(10)) END)

1) First, we lock a distinct OLAP_DEV."pseudo table" for read on a

RowHash to prevent global deadlock for OLAP_DEV.W_POSITION_D.

2) Next, we lock a distinct OLAP_DEV."pseudo table" for read on a

RowHash to prevent global deadlock for OLAP_DEV.W_INT_ORG_D.

3) We lock a distinct OLAP_DEV."pseudo table" for read on a RowHash

to prevent global deadlock for OLAP_DEV.W_INVOICE_F.

4) We lock a distinct OLAP_DEV."pseudo table" for read on a RowHash

to prevent global deadlock for OLAP_DEV.W_ORDER_D.

5) We lock a distinct OLAP_DEV."pseudo table" for read on a RowHash

to prevent global deadlock for OLAP_DEV.a.

6) We lock OLAP_DEV.W_POSITION_D for read, we lock

OLAP_DEV.W_INT_ORG_D for read, we lock OLAP_DEV.W_INVOICE_F for

read, we lock OLAP_DEV.W_ORDER_D for read, and we lock OLAP_DEV.a

for read.

7) We do an all-AMPs RETRIEVE step from OLAP_DEV.W_INT_ORG_D by way

of an all-rows scan with a condition of (

"(OLAP_DEV.W_INT_ORG_D.NAME > 'Unspecified') OR

(OLAP_DEV.W_INT_ORG_D.NAME < 'Unspecified')") into Spool 4

(all_amps), which is duplicated on all AMPs. The size of Spool 4

is estimated with high confidence to be 66,336 rows (2,719,776

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

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

all-rows scan, which is joined to OLAP_DEV.W_ORDER_D by way of an

all-rows scan with a condition of (

"((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order') OR

((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'OTC Sales') OR

((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct Billing') OR

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Service Order')))) AND

(((OLAP_DEV.W_ORDER_D.STATUS_CD > 'Cancel') AND

(OLAP_DEV.W_ORDER_D.STATUS_CD < 'CANCELLED')) OR

((OLAP_DEV.W_ORDER_D.STATUS_CD < 'Cancel') OR

(OLAP_DEV.W_ORDER_D.STATUS_CD > 'CANCELLED')))"). Spool 4 and

OLAP_DEV.W_ORDER_D are joined using a single partition hash_ join,

with a join condition of ("OLAP_DEV.W_ORDER_D.VIS_PR_BU_ID =

INTEGRATION_ID"). The input table OLAP_DEV.W_ORDER_D will not be

cached in memory. The result goes into Spool 5 (all_amps), which

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

by the hash code of (OLAP_DEV.W_ORDER_D.ROW_WID). The result

spool file will not be cached in memory. The size of Spool 5 is

estimated with no confidence to be 42,985,970 rows (4,169,639,090

bytes). The estimated time for this step is 8 minutes and 56

seconds.

9) We do an all-AMPs RETRIEVE step from OLAP_DEV.W_INVOICE_F by way

of an all-rows scan with a condition of ("((( CASE WHEN (NOT

(OLAP_DEV.W_INVOICE_F.INVC_TYPE_CD_WID IS NULL )) THEN

(OLAP_DEV.W_INVOICE_F.INVC_TYPE_CD_WID) ELSE (11515.) END ))=

11515.) AND (((( CASE WHEN (NOT

(OLAP_DEV.W_INVOICE_F.STATUS_CD_WID IS NULL )) THEN

(OLAP_DEV.W_INVOICE_F.STATUS_CD_WID) ELSE (16778.) END ))= 16778.

) AND (NOT (OLAP_DEV.w_invoice_f.X_INVC_DT IS NULL )))") into Spool

6 (all_amps), which is redistributed by the hash code of (

OLAP_DEV.W_INVOICE_F.ORDER_WID) to all AMPs. Then we do a SORT to

order Spool 6 by row hash. The size of Spool 6 is estimated with

no confidence to be 28,877,608 rows (1,415,002,792 bytes). The

estimated time for this step is 10 minutes.

10) We execute the following steps in parallel.

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

a RowHash match scan, which is joined to Spool 6 (Last Use) by

way of a RowHash match scan. Spool 5 and Spool 6 are joined

using a merge join, with a join condition of ("ROW_WID =

ORDER_WID"). The result goes into Spool 7 (all_amps), which

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

Spool 7 by the hash code of (OLAP_DEV.W_ORDER_D.VIS_PR_POS_ID).

The size of Spool 7 is estimated with no confidence to be

28,877,608 rows (3,378,680,136 bytes). The estimated time for

this step is 1 minute and 14 seconds.

2) We do an all-AMPs RETRIEVE step from OLAP_DEV.W_POSITION_D by

way of an all-rows scan with no residual conditions into Spool

8 (all_amps), which is duplicated on all AMPs. Then we do a

SORT to order Spool 8 by the hash code of (

OLAP_DEV.W_POSITION_D.INTEGRATION_ID). The size of Spool 8 is

estimated with high confidence to be 5,090,136 rows (

305,408,160 bytes). The estimated time for this step is 5.42

seconds.

11) We execute the following steps in parallel.

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

a RowHash match scan, which is joined to Spool 8 (Last Use) by

way of a RowHash match scan. Spool 7 and Spool 8 are

left outer joined using a merge join, with condition(s) used

for non-matching on left table ("NOT (VIS_PR_POS_ID IS NULL)"),

with a join condition of ("VIS_PR_POS_ID = INTEGRATION_ID").

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

locally on the AMPs. Then we do a SORT to order Spool 9 by

the hash code of (OLAP_DEV.W_ORDER_D.VIS_PR_BU_ID). The

result spool file will not be cached in memory. The size of

Spool 9 is estimated with no confidence to be 57,543,127 rows

(9,034,270,939 bytes). The estimated time for this step is 3

minutes and 24 seconds.

2) We do an all-AMPs RETRIEVE step from OLAP_DEV.a by way of an

all-rows scan with no residual conditions into Spool 10

(all_amps), which is duplicated on all AMPs. Then we do a

SORT to order Spool 10 by the hash code of (

OLAP_DEV.a.VIS_PR_BU_ID). The result spool file will not be

cached in memory. The size of Spool 10 is estimated with high

confidence to be 379,153,128 rows (11,374,593,840 bytes). The

estimated time for this step is 6 minutes and 49 seconds.

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

RowHash match scan, which is joined to Spool 10 (Last Use) by way

of a RowHash match scan. Spool 9 and Spool 10 are left outer

joined using a merge join, with condition(s) used for non-matching

on left table ("NOT (VIS_PR_BU_ID IS NULL)"), with a join

condition of ("VIS_PR_BU_ID = VIS_PR_BU_ID"). The result goes

into Spool 3 (all_amps), which is built locally on the AMPs. The

result spool file will not be cached in memory. The size of Spool

3 is estimated with no confidence to be 390,030,054,956 rows (

63,184,868,902,872 bytes). The estimated time for this step is 93

hours and 16 minutes.

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

all-rows scan , grouping by field1 ( OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END) ,( CASE WHEN (OLAP_DEV.W_ORDER_D.ORDER_TYPE

= 'Service Order') THEN (OLAP_DEV.W_ORDER_D.ORDER_NUM) ELSE (NULL)

END) ,( CASE WHEN (OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Service

Order') THEN (OLAP_DEV.W_ORDER_D.ORDER_NUM) ELSE (NULL) END)).

Aggregate Intermediate Results are computed globally, then placed

in Spool 18. The aggregate spool file will not be cached in

memory. The size of Spool 18 is estimated with no confidence to

be 292,522,541,217 rows (126,954,782,888,178 bytes). The

estimated time for this step is 178 hours and 48 minutes.

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

all-rows scan , grouping by field1 ( OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 20. The aggregate spool

file will not be cached in memory. The size of Spool 20 is

estimated with no confidence to be 92,732,042,467 rows (

39,040,189,878,607 bytes). The estimated time for this step is

157 hours and 56 minutes.

15) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 15 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 15 is estimated with no

confidence to be 92,732,042,467 rows (22,255,690,192,080

bytes). The estimated time for this step is 45 hours and 52

minutes.

2) We do an all-AMPs SUM step to aggregate from Spool 18 (Last

Use) by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Service Order') THEN

(OLAP_DEV.W_ORDER_D.ORDER_NUM) ELSE (NULL) END)). Aggregate

Intermediate Results are computed globally, then placed in

Spool 22. The aggregate spool file will not be cached in

memory. The size of Spool 22 is estimated with no confidence

to be 219,391,905,913 rows (77,006,558,975,463 bytes). The

estimated time for this step is 121 hours and 16 minutes.

16) We do an all-AMPs SUM step to aggregate from Spool 22 (Last Use)

by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 24. The size of Spool 24

is estimated with no confidence to be 10,193,463 rows (

2,905,136,955 bytes). The estimated time for this step is 66

hours and 56 minutes.

17) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 15 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 15 is estimated with no

confidence to be 92,742,235,930 rows (22,258,136,623,200

bytes). The estimated time for this step is 39 hours.

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

an all-rows scan , grouping by field1 (

OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Service Order') THEN

(OLAP_DEV.W_INVOICE_F.INVC_NUM) ELSE (NULL )END) ,( CASE WHEN

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Service Order') THEN

(OLAP_DEV.W_INVOICE_F.INVC_NUM) ELSE (NULL) END)). Aggregate

Intermediate Results are computed globally, then placed in

Spool 28. The aggregate spool file will not be cached in

memory. The size of Spool 28 is estimated with no confidence

to be 292,522,541,217 rows (119,349,196,816,536 bytes). The

estimated time for this step is 1,259 hours and 25 minutes.

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

all-rows scan , grouping by field1 ( OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 30. The aggregate spool

file will not be cached in memory. The size of Spool 30 is

estimated with no confidence to be 92,732,042,467 rows (

27,263,220,485,298 bytes). The estimated time for this step is

101 hours and 55 minutes.

19) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 14 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 14 is estimated with no

confidence to be 92,732,042,467 rows (10,015,060,586,436

bytes). The estimated time for this step is 18 hours and 54

minutes.

2) We do an all-AMPs SUM step to aggregate from Spool 28 (Last

Use) by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Service Order') THEN

(OLAP_DEV.W_INVOICE_F.INVC_NUM) ELSE (NULL) END)). Aggregate

Intermediate Results are computed globally, then placed in

Spool 32. The aggregate spool file will not be cached in

memory. The size of Spool 32 is estimated with no confidence

to be 66,441,954,862 rows (16,211,836,986,328 bytes). The

estimated time for this step is 653 hours and 56 minutes.

20) We do an all-AMPs SUM step to aggregate from Spool 32 (Last Use)

by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 34. The size of Spool 34

is estimated with no confidence to be 10,193,463 rows (

1,600,373,691 bytes). The estimated time for this step is 14

hours and 42 minutes.

21) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 14 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 14 is estimated with no

confidence to be 92,742,235,930 rows (10,016,161,480,440

bytes). The estimated time for this step is 11 hours and 58

minutes.

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

an all-rows scan , grouping by field1 (

OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'OTC Sales') THEN

(OLAP_DEV.W_INVOICE_F.INVC_NUM) ELSE (NULL) END) ,( CASE WHEN

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'OTC Sales') THEN

(OLAP_DEV.W_INVOICE_F.INVC_NUM) ELSE (NULL) END)). Aggregate

Intermediate Results are computed globally, then placed in

Spool 38. The aggregate spool file will not be cached in

memory. The size of Spool 38 is estimated with no confidence

to be 292,522,541,217 rows (119,349,196,816,536 bytes). The

estimated time for this step is 1,259 hours and 25 minutes.

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

all-rows scan , grouping by field1 ( OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 40. The aggregate spool

file will not be cached in memory. The size of Spool 40 is

estimated with no confidence to be 92,732,042,467 rows (

27,263,220,485,298 bytes). The estimated time for this step is

101 hours and 55 minutes.

23) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 13 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 13 is estimated with no

confidence to be 92,732,042,467 rows (10,015,060,586,436

bytes). The estimated time for this step is 18 hours and 54

minutes.

2) We do an all-AMPs SUM step to aggregate from Spool 38 (Last

Use) by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'OTC Sales') THEN

(OLAP_DEV.W_INVOICE_F.INVC_NUM) ELSE (NULL) END)). Aggregate

Intermediate Results are computed globally, then placed in

Spool 42. The aggregate spool file will not be cached in

memory. The size of Spool 42 is estimated with no confidence

to be 66,441,954,862 rows (16,211,836,986,328 bytes). The

estimated time for this step is 653 hours and 56 minutes.

24) We do an all-AMPs SUM step to aggregate from Spool 42 (Last Use)

by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 44. The size of Spool 44

is estimated with no confidence to be 10,193,463 rows (

1,600,373,691 bytes). The estimated time for this step is 14

hours and 42 minutes.

25) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 13 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 13 is estimated with no

confidence to be 92,742,235,930 rows (10,016,161,480,440

bytes). The estimated time for this step is 11 hours and 58

minutes.

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

an all-rows scan , grouping by field1 (

OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order') OR

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct

Billing')) THEN (OLAP_DEV.W_INVOICE_F.INVC_NUM) ELSE (NULL)

END) ,( CASE WHEN ((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales

Order') OR (OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales

Order-Direct Billing')) THEN (OLAP_DEV.W_INVOICE_F.INVC_NUM)

ELSE (NULL) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 48. The aggregate

spool file will not be cached in memory. The size of Spool 48

is estimated with no confidence to be 292,522,541,217 rows (

119,349,196,816,536 bytes). The estimated time for this step

is 1,259 hours and 25 minutes.

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

all-rows scan , grouping by field1 ( OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 50. The aggregate spool

file will not be cached in memory. The size of Spool 50 is

estimated with no confidence to be 92,732,042,467 rows (

27,263,220,485,298 bytes). The estimated time for this step is

101 hours and 55 minutes.

27) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 12 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 12 is estimated with no

confidence to be 92,732,042,467 rows (10,015,060,586,436

bytes). The estimated time for this step is 18 hours and 54

minutes.

2) We do an all-AMPs SUM step to aggregate from Spool 48 (Last

Use) by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order') OR

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct

Billing')) THEN (OLAP_DEV.W_INVOICE_F.INVC_NUM) ELSE (NULL)

END)). Aggregate Intermediate Results are computed globally,

then placed in Spool 52. The aggregate spool file will not be

cached in memory. The size of Spool 52 is estimated with no

confidence to be 66,441,954,862 rows (16,211,836,986,328

bytes). The estimated time for this step is 653 hours and 56

minutes.

28) We do an all-AMPs SUM step to aggregate from Spool 52 (Last Use)

by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 54. The size of Spool 54

is estimated with no confidence to be 10,193,463 rows (

1,600,373,691 bytes). The estimated time for this step is 14

hours and 42 minutes.

29) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 12 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 12 is estimated with no

confidence to be 92,742,235,930 rows (10,016,161,480,440

bytes). The estimated time for this step is 11 hours and 58

minutes.

2) We do an all-AMPs SUM step to aggregate from Spool 3 (Last

Use) by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order') OR

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct

Billing')) THEN (OLAP_DEV.W_ORDER_D.ORDER_NUM) ELSE (NULL) END)

,( CASE WHEN ((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order')

OR (OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct

Billing')) THEN (OLAP_DEV.W_ORDER_D.ORDER_NUM) ELSE (NULL) END)).

Aggregate Intermediate Results are computed globally, then

placed in Spool 58. The aggregate spool file will not be

cached in memory. The size of Spool 58 is estimated with no

confidence to be 292,522,541,217 rows (105,893,159,920,554

bytes). The estimated time for this step is 53 hours and 26

minutes.

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

all-rows scan , grouping by field1 ( OLAP_DEV.W_POSITION_D.OU_ID

,OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 60. The aggregate spool

file will not be cached in memory. The size of Spool 60 is

estimated with no confidence to be 92,732,042,467 rows (

26,706,828,230,496 bytes). The estimated time for this step is 90

hours and 52 minutes.

31) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 11 (all_amps), which is

built locally on the AMPs. The result spool file will not be

cached in memory. The size of Spool 11 is estimated with no

confidence to be 92,732,042,467 rows (10,015,060,586,436

bytes). The estimated time for this step is 18 hours and 54

minutes.

2) We do an all-AMPs SUM step to aggregate from Spool 58 (Last

Use) by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END) ,( CASE WHEN

((OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order') OR

(OLAP_DEV.W_ORDER_D.ORDER_TYPE = 'Sales Order-Direct

Billing')) THEN (OLAP_DEV.W_ORDER_D.ORDER_NUM) ELSE (NULL) END)).

Aggregate Intermediate Results are computed globally, then

placed in Spool 62. The aggregate spool file will not be

cached in memory. The size of Spool 62 is estimated with no

confidence to be 219,391,905,913 rows (47,827,435,489,034

bytes). The estimated time for this step is 90 hours and 25

minutes.

32) We do an all-AMPs SUM step to aggregate from Spool 62 (Last Use)

by way of an all-rows scan , grouping by field1 (

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID ,( CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT '-9.99999999999999E-999'))>=

1.20000000000000E 001) THEN ((TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4)TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0), FORMAT

'----------9.')) END)). Aggregate Intermediate Results are

computed globally, then placed in Spool 64. The size of Spool 64

is estimated with no confidence to be 10,193,463 rows (

1,539,212,913 bytes). The estimated time for this step is 43

hours and 36 minutes.

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

an all-rows scan into Spool 11 (all_amps), which is built locally

on the AMPs. The result spool file will not be cached in memory.

The size of Spool 11 is estimated with no confidence to be

92,742,235,930 rows (10,016,161,480,440 bytes). The estimated

time for this step is 11 hours and 58 minutes.

34) We execute the following steps in parallel.

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

way of an all-rows scan into Spool 66 (all_amps), which is

redistributed by the hash code of (

OLAP_DEV.W_POSITION_D.OU_ID, 0,

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID, 0, 0, (CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END)) to all AMPs. Then we do a SORT

to order Spool 66 by row hash. The result spool file will not

be cached in memory. The size of Spool 66 is estimated with

no confidence to be 92,742,235,930 rows (9,274,223,593,000

bytes). The estimated time for this step is 118 hours and 50

minutes.

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

way of an all-rows scan into Spool 67 (all_amps), which is

redistributed by the hash code of (

OLAP_DEV.W_POSITION_D.OU_ID, 0,

OLAP_DEV.W_INT_ORG_D.INTEGRATION_ID, 0, 0, (CASE WHEN

((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)))),3 )(FLOAT, FORMAT

'-9.99999999999999E-999'))>= 1.20000000000000E 001) THEN

((TRANSLATE((SUBSTR(TRIM(BOTH FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.'))+ 1) ELSE (TRANSLATE((SUBSTR(TRIM(BOTH

FROM ((((CURRENT_TIMESTAMP(6)) -

(OLAP_DEV.W_INVOICE_F.X_INVC_DT)) DAY(4) TO

SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC)),0 ,POSITION((' ') IN (TRIM(BOTH FROM

((((CURRENT_TIMESTAMP(6)) - (OLAP_DEV.W_INVOICE_F.X_INVC_DT))

DAY(4) TO SECOND(6)))(VARCHAR(50), CHARACTER SET LATIN, NOT

CASESPECIFIC))))))USING LATIN_TO_UNICODE)(DECIMAL(10,0),

FORMAT '----------9.')) END)) to all AMPs. Then we do a SORT

to order Spool 67 by row hash. The result spool file will not

be cached in memory. The size of Spool 67 is estimated with

no confidence to be 92,742,235,930 rows (9,274,223,593,000

bytes). The estimated time for this step is 118 hours and 50

minutes.

35) We do an all-AMPs JOIN step from Spool 66 (Last Use) by way of a

RowHash match scan, which is joined to Spool 67 (Last Use) by way

of a RowHash match scan. Spool 66 and Spool 67 are joined using a

merge join, with a join condition of ("(Field_8 = Field_8) AND

((Field_7 = Field_7) AND ((Field_6 = Field_6) AND ((INTEGRATION_ID

= INTEGRATION_ID) AND ((Field_4 = Field_4) AND (OU_ID = OU_ID

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

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

confidence to be 24 rows (2,496 bytes). The estimated time for

this step is 15 hours and 2 minutes.

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

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

all-rows scan. Spool 68 and Spool 13 are joined using a single

partition hash_ join, with a join condition of ("(Field_7 =

Field_7) AND ((Field_6 = Field_8) AND ((Field_5 = Field_6) AND

((INTEGRATION_ID = INTEGRATION_ID) AND ((Field_3 = Field_4) AND

(OU_ID = OU_ID )))))"). The result goes into Spool 69 (all_amps),

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

estimated with no confidence to be 24 rows (2,592 bytes). The

estimated time for this step is 7 hours and 5 minutes.

37) We do an all-AMPs JOIN step from Spool 69 (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 69 and Spool 12 are joined using a single

partition hash_ join, with a join condition of ("(Field_2 =

Field_7) AND ((Field_3 = Field_8) AND ((Field_4 = Field_6) AND

((INTEGRATION_ID = INTEGRATION_ID) AND ((Field_6 = Field_4) AND

(OU_ID = OU_ID )))))"). The result goes into Spool 70 (all_amps),

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

estimated with no confidence to be 24 rows (2,688 bytes). The

estimated time for this step is 7 hours and 5 minutes.

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

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

all-rows scan. Spool 70 and Spool 15 are joined using a single

partition hash_ join, with a join condition of ("(OU_ID = OU_ID)

AND ((Field_3 = Field_5) AND ((INTEGRATION_ID = INTEGRATION_ID)

AND ((Field_5 = Field_3) AND ((Field_6 = Field_8) AND (Field_7 =

Field_7 )))))"). The result goes into Spool 1 (all_amps), which

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

with no confidence to be 1 row (244 bytes). The estimated time

for this step is 16 hours and 24 minutes.

39) 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. The total estimated time is 7,163 hours and 14

minutes.

Dear Diter, Please help out fromthis situation

Pls see below mentioned things which we did

1) collect stats 2) created NUSI for filter col 3) all join tables have UPI

but every table has 5 cr of data.

Thanks for understanding & co-operation

Junior Contributor

Re: TUNING THE join

Hi Harshitha,

this was just a quick'n'dirty try to show you how to do it, you'll have to find out the exact query matching your requirements.

Hi Mahesh,

sorry, but this is way to much, i'm not willing to read all that stuff.

But a short glance showed:

- if "cr" means indian crore and every table has 50.000.000 rows, how can the optimizer estimate billions of rows?

- lots of "no confidence" indicating wrong/no stats, did you run DIAGNOSTIC HELPSTATS?

- lots of COUNT DITSINCT and a ROLLUP, hard to calculate

- change the COALESCE to an ORed condition

- NUSI are not used, not selective enough

- UPIs will not allow AMP-local joins, NUPIs might

Dieter