Query Performance Issue

Database

Query Performance Issue

We are using the same query for different country and when we used the country code US query is executing in 10 secs but when we are  using different country code it is taking ~2Mins 30 secs.

Below are the query:

1) Query executing in ~10 secs:

select distinct rptgTxn.TRANSACTION_ID,rptgTxn.IS_A_VALID_RPTER_TO_NON_RPTER,

rptgTxn.KIT_PRNT_COMPONENT_OR_NOT_KIT,rptgTxn.POTENTIAL_DUPLICATE,

rptgTxn.CLICK_CLAIM_FLAG,rptgTxn.RECORD_CREATION_DATE,rptgTxn.RECORD_CHANGE_DATE,

rptgTxn.CHANGE_BY,rptgTxn.MATERIAL,rptgTxn.PRODUCT_HIERARCHY,

rptgTxn.PARTNER_MATERIAL,rptgTxn.UNITS,rptgTxn.TRANSACTION_PRICE,

rptgTxn.DISTRIBUTOR_PRICE,rptgTxn.LIST_PRICE,rptgTxn.SUR,rptgTxn.NET_STUR_SAUR,

rptgTxn.TRIPLE_NET_STUR_SAUR,rptgTxn.aur,rptgTxn.bsd,rptgTxn.CUSTOM_PRICE_1,

rptgTxn.CUSTOM_PRICE_2,rptgTxn.CUSTOM_PRICE_3,rptgTxn.REPORTED_PRICE,

rptgTxn.CURRENCY_OF_THIS_TRANSACTION,rptgTxn.SERVICES_RENDERED_DATE,

rptgTxn.REPORTED_DATE,rptgTxn.AGREEMENT_CONTRACT_NUMBER,rptgTxn.AGREEMENT_CONTRACT_TYPE,

rptgTxn.SAP_CONTRACT_PO_NUM,rptgTxn.SFA_INTERNATIONAL_CONTRACT_REF,

rptgTxn.INVOICE_NUMBER,rptgTxn.INVOICE_LINE_NUMBER,rptgTxn.ORDERING_PARTY_PO_NUMBER,

rptgTxn.SALE_DATA_SOURCE,rptgTxn.SALE_TYPE,rptgTxn.ORIGINAL_REPORTER_ID,

rptgTxn.ORIGINAL_REPORTER_SALES_ORG,rptgTxn.REPORTER_COUNTRY,

rptgTxn.SOURCE_FROM,rptgTxn.REPORTER_SALES_ORG,rptgTxn.SOURCE_FROM_COUNTRY,

rptgTxn.ORDERING_PARTY,rptgTxn.ORDERING_PARTY_SALES_ORG,rptgTxn.ORDERING_PARTY_COUNTRY,

rptgTxn.BILL_TO,rptgTxn.BILL_TO_PARTY_SALES_ORG,rptgTxn.BILL_TO_COUNTRY,

rptgTxn.SHIP_TO,rptgTxn.SHIP_TO_PARTY_SALES_ORG,rptgTxn.SHIP_TO_COUNTRY,

rptgTxn.PARTICIPATING_PARTY,rptgTxn.PARTICIPATING_PARTY_SALES_ORG,

rptgTxn.PARTICIPATING_PARTY_COUNTRY,rptgTxn.TRANSACTION_COUNTRY,

rptgTxn.BUSINESS_PROCESS,rptgTxn.SGMNTTN_BUSINESS_AREA,rptgTxn.SGMNTTN_BUSINESS_SEGMENT,

rptgTxn.ENTERPRISE_REP,rptgTxn.CHANNEL_REP,rptgTxn.SALES_DOCUMENT_NUMBER,

rptgTxn.SALES_DOCUMENT_ITEM,rptgTxn.SALES_DOCUMENT_TYPE,rptgTxn.REASON_FOR_ORDER,

rptgTxn.POSTING_DATE,rptgTxn.FREE_CHAR_ATTRIBUTE_1,rptgTxn.FREE_CHAR_ATTRIBUTE_2,

rptgTxn.FREE_CHAR_ATTRIBUTE_3,rptgTxn.FREE_NUM_ATTRIBUTE_1,rptgTxn.FREE_NUM_ATTRIBUTE_2,

rptgTxn.FREE_NUM_ATTRIBUTE_3,rptgTxn.FREE_DATE_ATTRIBUTE_1,rptgTxn.FREE_DATE_ATTRIBUTE_2,

rptgTxn.FREE_DATE_ATTRIBUTE_3,rptgTxn.PRDCT_MODEL_NM ,

CASE WHEN cncTxn.rptgTxnId IS NOT NULL THEN 0 

WHEN UPPER(rptgTxn.RECORD_TYPE)='MCACT1' THEN 0 

ELSE

END , prty0.prty_key_id AS cpi0, prty0.clean_prty_name AS cpn0,

prty0.clean_prty_addr_1 AS cpa0, prty0.clean_prty_city AS cpc0,

prty0.clean_prty_rgn_st_prov AS cprsp0, prty0.clean_prty_postal_cd AS cppc0,

prty1.prty_key_id AS cpi1, prty1.clean_prty_name AS cpn1, prty1.clean_prty_addr_1 AS cpa1,

prty1.clean_prty_city AS cpc1, prty1.clean_prty_rgn_st_prov AS cprsp1,

prty1.clean_prty_postal_cd AS cppc1, prty2.prty_key_id AS cpi2,

prty2.clean_prty_name AS cpn2, prty2.clean_prty_addr_1 AS cpa2,

prty2.clean_prty_city AS cpc2, prty2.clean_prty_rgn_st_prov AS cprsp2,

prty2.clean_prty_postal_cd AS cppc2, prty3.prty_key_id AS cpi3,

prty3.clean_prty_name AS cpn3, prty3.clean_prty_addr_1 AS cpa3,

prty3.clean_prty_city AS cpc3, prty3.clean_prty_rgn_st_prov AS cprsp3,

prty3.clean_prty_postal_cd AS cppc3, prty4.prty_key_id AS cpi4,

prty4.clean_prty_name AS cpn4, prty4.clean_prty_addr_1 AS cpa4,

prty4.clean_prty_city AS cpc4, prty4.clean_prty_rgn_st_prov AS cprsp4,

prty4.clean_prty_postal_cd AS cppc4, prty5.prty_key_id AS cpi5,

prty5.clean_prty_name AS cpn5, prty5.clean_prty_addr_1 AS cpa5,

prty5.clean_prty_city AS cpc5, prty5.clean_prty_rgn_st_prov AS cprsp5,

prty5.clean_prty_postal_cd AS cppc5, rptgTxn.FREE_CHAR_ATTRIBUTE_2 AS prdctlinenm,

rptgTxn.FREE_CHAR_ATTRIBUTE_3 AS prdctsrsnm, rptgTxn.CUSTOM_PRICE_1 AS estimatedTransactionPrice 

from (

select top 5000 TRANSACTION_ID, IS_A_VALID_RPTER_TO_NON_RPTER,

KIT_PRNT_COMPONENT_OR_NOT_KIT, POTENTIAL_DUPLICATE, CLICK_CLAIM_FLAG,

RECORD_CREATION_DATE, RECORD_CHANGE_DATE, CHANGE_BY, MATERIAL,

PRODUCT_HIERARCHY, PARTNER_MATERIAL, UNITS, TRANSACTION_PRICE,

DISTRIBUTOR_PRICE, LIST_PRICE, SUR, NET_STUR_SAUR, TRIPLE_NET_STUR_SAUR,

aur, bsd, CUSTOM_PRICE_1, CUSTOM_PRICE_2, CUSTOM_PRICE_3, REPORTED_PRICE,

CURRENCY_OF_THIS_TRANSACTION, SERVICES_RENDERED_DATE, REPORTED_DATE,

AGREEMENT_CONTRACT_NUMBER, AGREEMENT_CONTRACT_TYPE, SAP_CONTRACT_PO_NUM,

SFA_INTERNATIONAL_CONTRACT_REF, INVOICE_NUMBER, INVOICE_LINE_NUMBER,

ORDERING_PARTY_PO_NUMBER, SALE_DATA_SOURCE, SALE_TYPE, ORIGINAL_REPORTER_ID,

ORIGINAL_REPORTER_SALES_ORG, REPORTER_COUNTRY, SOURCE_FROM, REPORTER_SALES_ORG,

SOURCE_FROM_COUNTRY, ORDERING_PARTY, ORDERING_PARTY_SALES_ORG,

ORDERING_PARTY_COUNTRY, BILL_TO, BILL_TO_PARTY_SALES_ORG, BILL_TO_COUNTRY,

SHIP_TO, SHIP_TO_PARTY_SALES_ORG, SHIP_TO_COUNTRY, PARTICIPATING_PARTY,

PARTICIPATING_PARTY_SALES_ORG, PARTICIPATING_PARTY_COUNTRY, TRANSACTION_COUNTRY,

BUSINESS_PROCESS, SGMNTTN_BUSINESS_AREA, SGMNTTN_BUSINESS_SEGMENT,

ENTERPRISE_REP, CHANNEL_REP, SALES_DOCUMENT_NUMBER, SALES_DOCUMENT_ITEM,

SALES_DOCUMENT_TYPE, REASON_FOR_ORDER, POSTING_DATE, FREE_CHAR_ATTRIBUTE_1,

FREE_CHAR_ATTRIBUTE_2, FREE_CHAR_ATTRIBUTE_3, FREE_NUM_ATTRIBUTE_1,

FREE_NUM_ATTRIBUTE_2, FREE_NUM_ATTRIBUTE_3, FREE_DATE_ATTRIBUTE_1,

FREE_DATE_ATTRIBUTE_2, FREE_DATE_ATTRIBUTE_3, related_transaction_id,

RECORD_TYPE, PRDCT_MODEL_NM 

from CDM_ST_EV.REPORTING_TRANSACTION  rptTx  

WHERE SERVICES_RENDERED_DATE >= '2015-01-01' 

AND SERVICES_RENDERED_DATE <= '2015-02-28'  

AND TRANSACTION_COUNTRY IN ('CA','US')  

and FREE_CHAR_ATTRIBUTE_1 IN (20,10,30,50) 

AND (DELETED is null 

or DELETED = 0) 

AND (RECORD_TYPE IN('SOACT2','MCACT1') 

OR (RECORD_TYPE='SIACT2' 

AND IS_A_VALID_RPTER_TO_NON_RPTER=1) )  

AND IS_A_VALID_RPTER_TO_NON_RPTER = 1  

AND (KIT_PRNT_COMPONENT_OR_NOT_KIT is null 

OR KIT_PRNT_COMPONENT_OR_NOT_KIT <> 'KC')  

AND (BILLING_TYPE <> 'ZF8' 

OR BILLING_TYPE is NULL) 

order by PARTICIPATING_PARTY, SERVICES_RENDERED_DATE desc )rptgTxn left join  (

SELECT RPTG_TXN_ID AS rptgTxnId 

FROM CDM_OPS_EV.CNC_CLAIMED_TXN 

WHERE RPTG_TXN_ID IS NOT NULL 

AND CLAIM_STTS IN ('PENDING' , 'ERROR') ) cncTxn 

on rptgTxn.TRANSACTION_ID = cncTxn.rptgTxnId LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty0 

ON rptgTxn.PARTICIPATING_PARTY = prty0.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty1 

ON rptgTxn.ORDERING_PARTY = prty1.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty2 

ON rptgTxn.BILL_TO = prty2.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty3 

ON rptgTxn.SHIP_TO = prty3.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty4 

ON rptgTxn.ORIGINAL_REPORTER_ID = prty4.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty5 

ON rptgTxn.SOURCE_FROM = prty5.prty_key_id  

order by PARTICIPATING_PARTY, SERVICES_RENDERED_DATE desc

Explain Plan :

  1) First, we lock CDM_ST.REPORTING_TRANSACTION for access, we lock

     CDM_OPS.PRTY_KEY in view CDM_OPS_EV.PRTY_KEY for access, and we

     lock CDM_OPS.CNC_CLAIMED_TXN for access. 

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

     CDM_ST.REPORTING_TRANSACTION with a condition of (

     "(CDM_ST.REPORTING_TRANSACTION.IS_A_VALID_RPTER_TO_NON_RPTER = 1)

     AND ((CDM_ST.REPORTING_TRANSACTION.SERVICES_RENDERED_DATE <= DATE

     '2015-02-28') AND (((CDM_ST.REPORTING_TRANSACTION.DELETED = 0) OR

     (CDM_ST.REPORTING_TRANSACTION.DELETED IS NULL )) AND

     (((CDM_ST.REPORTING_TRANSACTION.KIT_PRNT_COMPONENT_OR_NOT_KIT <

     'KC') OR

     ((CDM_ST.REPORTING_TRANSACTION.KIT_PRNT_COMPONENT_OR_NOT_KIT IS

     NULL) OR

     (CDM_ST.REPORTING_TRANSACTION.KIT_PRNT_COMPONENT_OR_NOT_KIT >

     'KC'))) AND ((CDM_ST.REPORTING_TRANSACTION.SERVICES_RENDERED_DATE

     >= DATE '2015-01-01') AND

     ((((CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1 (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 1.00000000000000E 001) OR

     (((CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1 (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 2.00000000000000E 001) OR

     (((CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1 (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 3.00000000000000E 001) OR

     ((CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1 (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 5.00000000000000E 001 )))) AND

     (((CDM_ST.REPORTING_TRANSACTION.BILLING_TYPE < 'ZF8') OR

     ((CDM_ST.REPORTING_TRANSACTION.BILLING_TYPE IS NULL) OR

     (CDM_ST.REPORTING_TRANSACTION.BILLING_TYPE > 'ZF8'))) AND

     (((CDM_ST.REPORTING_TRANSACTION.TRANSACTION_COUNTRY = 'US') OR

     (CDM_ST.REPORTING_TRANSACTION.TRANSACTION_COUNTRY = 'CA')) AND

     ((CDM_ST.REPORTING_TRANSACTION.RECORD_TYPE = 'SOACT2') OR

     ((CDM_ST.REPORTING_TRANSACTION.RECORD_TYPE = 'MCACT1') OR

     ((CDM_ST.REPORTING_TRANSACTION.RECORD_TYPE = 'SIACT2') AND

     (CDM_ST.REPORTING_TRANSACTION.IS_A_VALID_RPTER_TO_NON_RPTER = 1

     )))))))))))") into Spool 2 (all_amps) (compressed columns allowed),

     which is built locally on the AMPs.  The input table will not be

     cached in memory, but it is eligible for synchronized scanning. 

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

     Spool 2 is estimated with no confidence to be 5,179,818 rows (

     33,404,646,282 bytes).  The estimated time for this step is 1

     minute and 24 seconds. 

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

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

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

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

     built locally on the AMPs.  This step is used to retrieve the TOP

     5000 rows.  The size is estimated with no confidence to be 5,000

     rows (32,335,000 bytes). 

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

     an all-rows scan with a condition of ("Field_78 <= 5000") into

     Spool 1 (used to materialize view, derived table or table function

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

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

     confidence to be 5,000 rows (32,275,000 bytes).  The estimated

     time for this step is 0.03 seconds. 

  5) We execute the following steps in parallel. 

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

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

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

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

          CDM_ST.REPORTING_TRANSACTION.TRANSACTION_ID).  The size of

          Spool 9 is estimated with no confidence to be 5,000 rows (

          32,235,000 bytes).  The estimated time for this step is 0.04

          seconds. 

       2) We do an all-AMPs RETRIEVE step from CDM_OPS.CNC_CLAIMED_TXN

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

          "((CDM_OPS.CNC_CLAIMED_TXN.CLAIM_STTS = 'PENDING') OR

          (CDM_OPS.CNC_CLAIMED_TXN.CLAIM_STTS = 'ERROR')) AND (NOT

          (CDM_OPS.CNC_CLAIMED_TXN.RPTG_TXN_ID IS NULL ))") into Spool

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

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

          by the hash code of (CDM_OPS.CNC_CLAIMED_TXN.RPTG_TXN_ID). 

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

          303,855 rows (24,612,255 bytes).  The estimated time for this

          step is 0.07 seconds. 

  6) 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 (TRANSACTION_ID IS NULL)"), with a join

     condition of ("TRANSACTION_ID = RPTG_TXN_ID").  The result goes

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

     redistributed by the hash code of (

     CDM_ST.REPORTING_TRANSACTION.SOURCE_FROM) to all AMPs.  Then we do

     a SORT to order Spool 11 by row hash.  The size of Spool 11 is

     estimated with no confidence to be 5,499 rows (35,825,985 bytes). 

     The estimated time for this step is 8.55 seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions.  Spool 11 and CDM_OPS.PRTY_KEY are left outer

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

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

     of ("SOURCE_FROM = CDM_OPS.PRTY_KEY.prty_key_id").  The input

     table CDM_OPS.PRTY_KEY will not be cached in memory.  The result

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

     is redistributed by the hash code of (

     CDM_ST.REPORTING_TRANSACTION.ORIGINAL_REPORTER_ID) to all AMPs. 

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

     Spool 12 is estimated with no confidence to be 5,500 rows (

     40,584,500 bytes).  The estimated time for this step is 4.85

     seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 12

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (ORIGINAL_REPORTER_ID IS NULL)"), with a join condition of (

     "ORIGINAL_REPORTER_ID = CDM_OPS.PRTY_KEY.prty_key_id").  The input

     table CDM_OPS.PRTY_KEY will not be cached in memory.  The result

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

     is redistributed by the hash code of (

     CDM_ST.REPORTING_TRANSACTION.SHIP_TO) to all AMPs.  Then we do a

     SORT to order Spool 13 by row hash.  The size of Spool 13 is

     estimated with no confidence to be 5,501 rows (45,344,743 bytes). 

     The estimated time for this step is 5.48 seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 13

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (SHIP_TO IS NULL)"), with a join condition of ("SHIP_TO =

     CDM_OPS.PRTY_KEY.prty_key_id").  The input table CDM_OPS.PRTY_KEY

     will not be cached in memory.  The result goes into Spool 14

     (all_amps) (compressed columns allowed), which is redistributed by

     the hash code of (CDM_ST.REPORTING_TRANSACTION.BILL_TO) to all

     AMPs.  Then we do a SORT to order Spool 14 by row hash.  The size

     of Spool 14 is estimated with no confidence to be 5,502 rows (

     50,106,714 bytes).  The estimated time for this step is 5.96

     seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 14

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (BILL_TO IS NULL)"), with a join condition of ("BILL_TO =

     CDM_OPS.PRTY_KEY.prty_key_id").  The input table CDM_OPS.PRTY_KEY

     will not be cached in memory.  The result goes into Spool 15

     (all_amps) (compressed columns allowed), which is redistributed by

     the hash code of (CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY) to

     all AMPs.  Then we do a SORT to order Spool 15 by row hash.  The

     size of Spool 15 is estimated with no confidence to be 5,503 rows

     (54,870,413 bytes).  The estimated time for this step is 6.47

     seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 15

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (ORDERING_PARTY IS NULL)"), with a join condition of (

     "ORDERING_PARTY = CDM_OPS.PRTY_KEY.prty_key_id").  The input table

     CDM_OPS.PRTY_KEY will not be cached in memory.  The result goes

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

     redistributed by the hash code of (

     CDM_ST.REPORTING_TRANSACTION.PARTICIPATING_PARTY) to all AMPs. 

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

     Spool 16 is estimated with no confidence to be 5,504 rows (

     59,635,840 bytes).  The estimated time for this step is 7.02

     seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 16

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (PARTICIPATING_PARTY IS NULL)"), with a join condition of (

     "PARTICIPATING_PARTY = CDM_OPS.PRTY_KEY.prty_key_id").  The input

     table CDM_OPS.PRTY_KEY will not be cached in memory.  The result

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

     code of (CDM_ST.REPORTING_TRANSACTION.CUSTOM_PRICE_1,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_3,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_2,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id, (

     CASE WHEN (NOT (CDM_OPS.CNC_CLAIMED_TXN.RPTG_TXN_ID IS NULL ))

     THEN (0) WHEN ((UPPER((CDM_ST.REPORTING_TRANSACTION.RECORD_TYPE

     )))= 'MCACT1') THEN (0) ELSE (1) END),

     CDM_ST.REPORTING_TRANSACTION.PRDCT_MODEL_NM,

     CDM_ST.REPORTING_TRANSACTION.FREE_DATE_ATTRIBUTE_3,

     CDM_ST.REPORTING_TRANSACTION.FREE_DATE_ATTRIBUTE_2,

     CDM_ST.REPORTING_TRANSACTION.FREE_DATE_ATTRIBUTE_1,

     CDM_ST.REPORTING_TRANSACTION.FREE_NUM_ATTRIBUTE_3,

     CDM_ST.REPORTING_TRANSACTION.FREE_NUM_ATTRIBUTE_2,

     CDM_ST.REPORTING_TRANSACTION.FREE_NUM_ATTRIBUTE_1,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_3,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_2,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1,

     CDM_ST.REPORTING_TRANSACTION.POSTING_DATE,

     CDM_ST.REPORTING_TRANSACTION.REASON_FOR_ORDER,

     CDM_ST.REPORTING_TRANSACTION.SALES_DOCUMENT_TYPE,

     CDM_ST.REPORTING_TRANSACTION.SALES_DOCUMENT_ITEM,

     CDM_ST.REPORTING_TRANSACTION.SALES_DOCUMENT_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.CHANNEL_REP,

     CDM_ST.REPORTING_TRANSACTION.ENTERPRISE_REP,

     CDM_ST.REPORTING_TRANSACTION.SGMNTTN_BUSINESS_SEGMENT,

     CDM_ST.REPORTING_TRANSACTION.SGMNTTN_BUSINESS_AREA,

     CDM_ST.REPORTING_TRANSACTION.BUSINESS_PROCESS,

     CDM_ST.REPORTING_TRANSACTION.TRANSACTION_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.PARTICIPATING_PARTY_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.PARTICIPATING_PARTY_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.SHIP_TO_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.SHIP_TO_PARTY_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.SHIP_TO,

     CDM_ST.REPORTING_TRANSACTION.BILL_TO_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.BILL_TO_PARTY_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.BILL_TO,

     CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY,

     CDM_ST.REPORTING_TRANSACTION.SOURCE_FROM_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.REPORTER_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.SOURCE_FROM,

     CDM_ST.REPORTING_TRANSACTION.REPORTER_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.ORIGINAL_REPORTER_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.ORIGINAL_REPORTER_ID,

     CDM_ST.REPORTING_TRANSACTION.SALE_TYPE,

     CDM_ST.REPORTING_TRANSACTION.SALE_DATA_SOURCE,

     CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY_PO_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.INVOICE_LINE_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.INVOICE_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.SFA_INTERNATIONAL_CONTRACT_REF,

     CDM_ST.REPORTING_TRANSACTION.SAP_CONTRACT_PO_NUM,

     CDM_ST.REPORTING_TRANSACTION.AGREEMENT_CONTRACT_TYPE,

     CDM_ST.REPORTING_TRANSACTION.AGREEMENT_CONTRACT_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.REPORTED_DATE,

     CDM_ST.REPORTING_TRANSACTION.CURRENCY_OF_THIS_TRANSACTION,

     CDM_ST.REPORTING_TRANSACTION.REPORTED_PRICE,

     CDM_ST.REPORTING_TRANSACTION.CUSTOM_PRICE_3,

     CDM_ST.REPORTING_TRANSACTION.CUSTOM_PRICE_2,

     CDM_ST.REPORTING_TRANSACTION.CUSTOM_PRICE_1,

     CDM_ST.REPORTING_TRANSACTION.BSD, CDM_ST.REPORTING_TRANSACTION.AUR,

     CDM_ST.REPORTING_TRANSACTION.TRIPLE_NET_STUR_SAUR,

     CDM_ST.REPORTING_TRANSACTION.NET_STUR_SAUR,

     CDM_ST.REPORTING_TRANSACTION.SUR,

     CDM_ST.REPORTING_TRANSACTION.LIST_PRICE,

     CDM_ST.REPORTING_TRANSACTION.DISTRIBUTOR_PRICE,

     CDM_ST.REPORTING_TRANSACTION.TRANSACTION_PRICE,

     CDM_ST.REPORTING_TRANSACTION.UNITS,

     CDM_ST.REPORTING_TRANSACTION.PARTNER_MATERIAL,

     CDM_ST.REPORTING_TRANSACTION.PRODUCT_HIERARCHY,

     CDM_ST.REPORTING_TRANSACTION.MATERIAL,

     CDM_ST.REPORTING_TRANSACTION.CHANGE_BY,

     CDM_ST.REPORTING_TRANSACTION.RECORD_CHANGE_DATE,

     CDM_ST.REPORTING_TRANSACTION.RECORD_CREATION_DATE,

     CDM_ST.REPORTING_TRANSACTION.click_claim_flag,

     CDM_ST.REPORTING_TRANSACTION.POTENTIAL_DUPLICATE,

     CDM_ST.REPORTING_TRANSACTION.KIT_PRNT_COMPONENT_OR_NOT_KIT,

     CDM_ST.REPORTING_TRANSACTION.IS_A_VALID_RPTER_TO_NON_RPTER,

     CDM_ST.REPORTING_TRANSACTION.TRANSACTION_ID,

     CDM_ST.REPORTING_TRANSACTION.SERVICES_RENDERED_DATE,

     CDM_ST.REPORTING_TRANSACTION.PARTICIPATING_PARTY) to all AMPs. 

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

     eliminating duplicate rows.  The size of Spool 8 is estimated with

     no confidence to be 5,000 rows (204,230,000 bytes).  The estimated

     time for this step is 3.74 seconds. 

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

     in processing the request.

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

     statement 1. 

Query 2 : Execution time is 2 mins 30 secs.

select distinct rptgTxn.TRANSACTION_ID,rptgTxn.IS_A_VALID_RPTER_TO_NON_RPTER,

rptgTxn.KIT_PRNT_COMPONENT_OR_NOT_KIT,rptgTxn.POTENTIAL_DUPLICATE,

rptgTxn.CLICK_CLAIM_FLAG,rptgTxn.RECORD_CREATION_DATE,rptgTxn.RECORD_CHANGE_DATE,

rptgTxn.CHANGE_BY,rptgTxn.MATERIAL,rptgTxn.PRODUCT_HIERARCHY,

rptgTxn.PARTNER_MATERIAL,rptgTxn.UNITS,rptgTxn.TRANSACTION_PRICE,

rptgTxn.DISTRIBUTOR_PRICE,rptgTxn.LIST_PRICE,rptgTxn.SUR,rptgTxn.NET_STUR_SAUR,

rptgTxn.TRIPLE_NET_STUR_SAUR,rptgTxn.aur,rptgTxn.bsd,rptgTxn.CUSTOM_PRICE_1,

rptgTxn.CUSTOM_PRICE_2,rptgTxn.CUSTOM_PRICE_3,rptgTxn.REPORTED_PRICE,

rptgTxn.CURRENCY_OF_THIS_TRANSACTION,rptgTxn.SERVICES_RENDERED_DATE,

rptgTxn.REPORTED_DATE,rptgTxn.AGREEMENT_CONTRACT_NUMBER,rptgTxn.AGREEMENT_CONTRACT_TYPE,

rptgTxn.SAP_CONTRACT_PO_NUM,rptgTxn.SFA_INTERNATIONAL_CONTRACT_REF,

rptgTxn.INVOICE_NUMBER,rptgTxn.INVOICE_LINE_NUMBER,rptgTxn.ORDERING_PARTY_PO_NUMBER,

rptgTxn.SALE_DATA_SOURCE,rptgTxn.SALE_TYPE,rptgTxn.ORIGINAL_REPORTER_ID,

rptgTxn.ORIGINAL_REPORTER_SALES_ORG,rptgTxn.REPORTER_COUNTRY,

rptgTxn.SOURCE_FROM,rptgTxn.REPORTER_SALES_ORG,rptgTxn.SOURCE_FROM_COUNTRY,

rptgTxn.ORDERING_PARTY,rptgTxn.ORDERING_PARTY_SALES_ORG,rptgTxn.ORDERING_PARTY_COUNTRY,

rptgTxn.BILL_TO,rptgTxn.BILL_TO_PARTY_SALES_ORG,rptgTxn.BILL_TO_COUNTRY,

rptgTxn.SHIP_TO,rptgTxn.SHIP_TO_PARTY_SALES_ORG,rptgTxn.SHIP_TO_COUNTRY,

rptgTxn.PARTICIPATING_PARTY,rptgTxn.PARTICIPATING_PARTY_SALES_ORG,

rptgTxn.PARTICIPATING_PARTY_COUNTRY,rptgTxn.TRANSACTION_COUNTRY,

rptgTxn.BUSINESS_PROCESS,rptgTxn.SGMNTTN_BUSINESS_AREA,rptgTxn.SGMNTTN_BUSINESS_SEGMENT,

rptgTxn.ENTERPRISE_REP,rptgTxn.CHANNEL_REP,rptgTxn.SALES_DOCUMENT_NUMBER,

rptgTxn.SALES_DOCUMENT_ITEM,rptgTxn.SALES_DOCUMENT_TYPE,rptgTxn.REASON_FOR_ORDER,

rptgTxn.POSTING_DATE,rptgTxn.FREE_CHAR_ATTRIBUTE_1,rptgTxn.FREE_CHAR_ATTRIBUTE_2,

rptgTxn.FREE_CHAR_ATTRIBUTE_3,rptgTxn.FREE_NUM_ATTRIBUTE_1,rptgTxn.FREE_NUM_ATTRIBUTE_2,

rptgTxn.FREE_NUM_ATTRIBUTE_3,rptgTxn.FREE_DATE_ATTRIBUTE_1,rptgTxn.FREE_DATE_ATTRIBUTE_2,

rptgTxn.FREE_DATE_ATTRIBUTE_3,rptgTxn.PRDCT_MODEL_NM ,

CASE WHEN cncTxn.rptgTxnId IS NOT NULL THEN 0 

WHEN UPPER(rptgTxn.RECORD_TYPE)='MCACT1' THEN 0 

ELSE

END , prty0.prty_key_id AS cpi0, prty0.clean_prty_name AS cpn0,

prty0.clean_prty_addr_1 AS cpa0, prty0.clean_prty_city AS cpc0,

prty0.clean_prty_rgn_st_prov AS cprsp0, prty0.clean_prty_postal_cd AS cppc0,

prty1.prty_key_id AS cpi1, prty1.clean_prty_name AS cpn1, prty1.clean_prty_addr_1 AS cpa1,

prty1.clean_prty_city AS cpc1, prty1.clean_prty_rgn_st_prov AS cprsp1,

prty1.clean_prty_postal_cd AS cppc1, prty2.prty_key_id AS cpi2,

prty2.clean_prty_name AS cpn2, prty2.clean_prty_addr_1 AS cpa2,

prty2.clean_prty_city AS cpc2, prty2.clean_prty_rgn_st_prov AS cprsp2,

prty2.clean_prty_postal_cd AS cppc2, prty3.prty_key_id AS cpi3,

prty3.clean_prty_name AS cpn3, prty3.clean_prty_addr_1 AS cpa3,

prty3.clean_prty_city AS cpc3, prty3.clean_prty_rgn_st_prov AS cprsp3,

prty3.clean_prty_postal_cd AS cppc3, prty4.prty_key_id AS cpi4,

prty4.clean_prty_name AS cpn4, prty4.clean_prty_addr_1 AS cpa4,

prty4.clean_prty_city AS cpc4, prty4.clean_prty_rgn_st_prov AS cprsp4,

prty4.clean_prty_postal_cd AS cppc4, prty5.prty_key_id AS cpi5,

prty5.clean_prty_name AS cpn5, prty5.clean_prty_addr_1 AS cpa5,

prty5.clean_prty_city AS cpc5, prty5.clean_prty_rgn_st_prov AS cprsp5,

prty5.clean_prty_postal_cd AS cppc5, rptgTxn.FREE_CHAR_ATTRIBUTE_2 AS prdctlinenm,

rptgTxn.FREE_CHAR_ATTRIBUTE_3 AS prdctsrsnm, rptgTxn.CUSTOM_PRICE_1 AS estimatedTransactionPrice 

from (

select top 5000 TRANSACTION_ID, IS_A_VALID_RPTER_TO_NON_RPTER,

KIT_PRNT_COMPONENT_OR_NOT_KIT, POTENTIAL_DUPLICATE, CLICK_CLAIM_FLAG,

RECORD_CREATION_DATE, RECORD_CHANGE_DATE, CHANGE_BY, MATERIAL,

PRODUCT_HIERARCHY, PARTNER_MATERIAL, UNITS, TRANSACTION_PRICE,

DISTRIBUTOR_PRICE, LIST_PRICE, SUR, NET_STUR_SAUR, TRIPLE_NET_STUR_SAUR,

aur, bsd, CUSTOM_PRICE_1, CUSTOM_PRICE_2, CUSTOM_PRICE_3, REPORTED_PRICE,

CURRENCY_OF_THIS_TRANSACTION, SERVICES_RENDERED_DATE, REPORTED_DATE,

AGREEMENT_CONTRACT_NUMBER, AGREEMENT_CONTRACT_TYPE, SAP_CONTRACT_PO_NUM,

SFA_INTERNATIONAL_CONTRACT_REF, INVOICE_NUMBER, INVOICE_LINE_NUMBER,

ORDERING_PARTY_PO_NUMBER, SALE_DATA_SOURCE, SALE_TYPE, ORIGINAL_REPORTER_ID,

ORIGINAL_REPORTER_SALES_ORG, REPORTER_COUNTRY, SOURCE_FROM, REPORTER_SALES_ORG,

SOURCE_FROM_COUNTRY, ORDERING_PARTY, ORDERING_PARTY_SALES_ORG,

ORDERING_PARTY_COUNTRY, BILL_TO, BILL_TO_PARTY_SALES_ORG, BILL_TO_COUNTRY,

SHIP_TO, SHIP_TO_PARTY_SALES_ORG, SHIP_TO_COUNTRY, PARTICIPATING_PARTY,

PARTICIPATING_PARTY_SALES_ORG, PARTICIPATING_PARTY_COUNTRY, TRANSACTION_COUNTRY,

BUSINESS_PROCESS, SGMNTTN_BUSINESS_AREA, SGMNTTN_BUSINESS_SEGMENT,

ENTERPRISE_REP, CHANNEL_REP, SALES_DOCUMENT_NUMBER, SALES_DOCUMENT_ITEM,

SALES_DOCUMENT_TYPE, REASON_FOR_ORDER, POSTING_DATE, FREE_CHAR_ATTRIBUTE_1,

FREE_CHAR_ATTRIBUTE_2, FREE_CHAR_ATTRIBUTE_3, FREE_NUM_ATTRIBUTE_1,

FREE_NUM_ATTRIBUTE_2, FREE_NUM_ATTRIBUTE_3, FREE_DATE_ATTRIBUTE_1,

FREE_DATE_ATTRIBUTE_2, FREE_DATE_ATTRIBUTE_3, related_transaction_id,

RECORD_TYPE, PRDCT_MODEL_NM 

from CDM_ST_EV.REPORTING_TRANSACTION  rptTx  

WHERE SERVICES_RENDERED_DATE >= '2015-01-01' 

AND SERVICES_RENDERED_DATE <= '2015-02-28'  

AND TRANSACTION_COUNTRY IN ('NZ','AU')  

and FREE_CHAR_ATTRIBUTE_1 IN (20,10,30,50) 

AND (DELETED is null 

or DELETED = 0) 

AND (RECORD_TYPE IN('SOACT2','MCACT1') 

OR (RECORD_TYPE='SIACT2' 

AND IS_A_VALID_RPTER_TO_NON_RPTER=1) )  

AND IS_A_VALID_RPTER_TO_NON_RPTER = 1  

AND (KIT_PRNT_COMPONENT_OR_NOT_KIT is null 

OR KIT_PRNT_COMPONENT_OR_NOT_KIT <> 'KC')  

AND (BILLING_TYPE <> 'ZF8' 

OR BILLING_TYPE is NULL) 

order by PARTICIPATING_PARTY, SERVICES_RENDERED_DATE desc )rptgTxn left join  (

SELECT RPTG_TXN_ID AS rptgTxnId 

FROM CDM_OPS_EV.CNC_CLAIMED_TXN 

WHERE RPTG_TXN_ID IS NOT NULL 

AND CLAIM_STTS IN ('PENDING' , 'ERROR') ) cncTxn 

on rptgTxn.TRANSACTION_ID = cncTxn.rptgTxnId LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty0 

ON rptgTxn.PARTICIPATING_PARTY = prty0.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty1 

ON rptgTxn.ORDERING_PARTY = prty1.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty2 

ON rptgTxn.BILL_TO = prty2.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty3 

ON rptgTxn.SHIP_TO = prty3.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty4 

ON rptgTxn.ORIGINAL_REPORTER_ID = prty4.prty_key_id LEFT JOIN CDM_OPS_EV.PRTY_KEY AS prty5 

ON rptgTxn.SOURCE_FROM = prty5.prty_key_id  

order by PARTICIPATING_PARTY, SERVICES_RENDERED_DATE desc

Eplain Plan

1) First, we lock CDM_ST.REPORTING_TRANSACTION for access, we lock

     CDM_OPS.PRTY_KEY in view CDM_OPS_EV.PRTY_KEY for access, and we

     lock CDM_OPS.CNC_CLAIMED_TXN for access. 

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

     CDM_ST.REPORTING_TRANSACTION by way of index # 40

     "CDM_ST.REPORTING_TRANSACTION.TRANSACTION_COUNTRY = 'NZ'"

     extracting row ids only with no residual conditions into Spool 3

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

     3 is estimated with high confidence to be 119,702 rows.  The

     estimated time for this step is 0.10 seconds. 

  3) We do an all-AMPs RETRIEVE step from CDM_ST.REPORTING_TRANSACTION

     by way of index # 40

     "CDM_ST.REPORTING_TRANSACTION.TRANSACTION_COUNTRY = 'AU'"

     extracting row ids only with no residual conditions into Spool 3

     (all_amps), which is built locally on the AMPs.  Then we do a SORT

     to order Spool 3 by row id eliminating duplicate rows.  The size

     of Spool 3 is estimated with high confidence to be 4,670,727 rows. 

     The estimated time for this step is 0.13 seconds. 

  4) We do an all-AMPs RETRIEVE step from CDM_ST.REPORTING_TRANSACTION

     by way of row ids from Spool 3 (Last Use) with a residual

     condition of (

     "(CDM_ST.REPORTING_TRANSACTION.IS_A_VALID_RPTER_TO_NON_RPTER = 1)

     AND ((CDM_ST.REPORTING_TRANSACTION.SERVICES_RENDERED_DATE <= DATE

     '2015-02-28') AND (((CDM_ST.REPORTING_TRANSACTION.DELETED = 0) OR

     (CDM_ST.REPORTING_TRANSACTION.DELETED IS NULL )) AND

     (((CDM_ST.REPORTING_TRANSACTION.KIT_PRNT_COMPONENT_OR_NOT_KIT <

     'KC') OR

     ((CDM_ST.REPORTING_TRANSACTION.KIT_PRNT_COMPONENT_OR_NOT_KIT IS

     NULL) OR

     (CDM_ST.REPORTING_TRANSACTION.KIT_PRNT_COMPONENT_OR_NOT_KIT >

     'KC'))) AND ((CDM_ST.REPORTING_TRANSACTION.SERVICES_RENDERED_DATE

     >= DATE '2015-01-01') AND

     ((((CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1 (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 1.00000000000000E 001) OR

     (((CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1 (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 2.00000000000000E 001) OR

     (((CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1 (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 3.00000000000000E 001) OR

     ((CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1 (FLOAT,

     FORMAT '-9.99999999999999E-999'))= 5.00000000000000E 001 )))) AND

     (((CDM_ST.REPORTING_TRANSACTION.RECORD_TYPE = 'SOACT2') OR

     ((CDM_ST.REPORTING_TRANSACTION.RECORD_TYPE = 'MCACT1') OR

     ((CDM_ST.REPORTING_TRANSACTION.RECORD_TYPE = 'SIACT2') AND

     (CDM_ST.REPORTING_TRANSACTION.IS_A_VALID_RPTER_TO_NON_RPTER = 1

     )))) AND ((CDM_ST.REPORTING_TRANSACTION.BILLING_TYPE < 'ZF8') OR

     ((CDM_ST.REPORTING_TRANSACTION.BILLING_TYPE IS NULL) OR

     (CDM_ST.REPORTING_TRANSACTION.BILLING_TYPE > 'ZF8')))))))))") into

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

     locally on the AMPs.  The input table will not be cached in memory,

     but it is eligible for synchronized scanning.  The size of Spool 2

     is estimated with no confidence to be 1,475,035 rows (

     9,512,500,715 bytes).  The estimated time for this step is 33.35

     seconds. 

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

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

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

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

     built locally on the AMPs.  This step is used to retrieve the TOP

     5000 rows.  The size is estimated with no confidence to be 5,000

     rows (32,335,000 bytes). 

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

     an all-rows scan with a condition of ("Field_78 <= 5000") into

     Spool 1 (used to materialize view, derived table or table function

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

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

     confidence to be 5,000 rows (32,275,000 bytes).  The estimated

     time for this step is 0.03 seconds. 

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

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

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

     CDM_ST.REPORTING_TRANSACTION.ORIGINAL_REPORTER_ID) to all AMPs. 

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

     Spool 10 is estimated with no confidence to be 5,000 rows (

     32,235,000 bytes).  The estimated time for this step is 7.71

     seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions.  Spool 10 and CDM_OPS.PRTY_KEY are left outer

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

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

     condition of ("ORIGINAL_REPORTER_ID = CDM_OPS.PRTY_KEY.prty_key_id"). 

     The input table CDM_OPS.PRTY_KEY will not be cached in memory. 

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

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

     CDM_ST.REPORTING_TRANSACTION.SOURCE_FROM) to all AMPs.  Then we do

     a SORT to order Spool 11 by row hash.  The size of Spool 11 is

     estimated with no confidence to be 5,000 rows (36,555,000 bytes). 

     The estimated time for this step is 8.91 seconds. 

  9) We execute the following steps in parallel. 

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

          of a RowHash match scan, which is joined to CDM_OPS.PRTY_KEY

          in view CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan

          with no residual conditions locking CDM_OPS.PRTY_KEY for

          access.  Spool 11 and CDM_OPS.PRTY_KEY are left outer joined

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

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

          condition of ("SOURCE_FROM = CDM_OPS.PRTY_KEY.prty_key_id"). 

          The input table CDM_OPS.PRTY_KEY will not be cached in memory. 

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

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

          SORT to order Spool 12 by the hash code of (

          CDM_ST.REPORTING_TRANSACTION.TRANSACTION_ID).  The size of

          Spool 12 is estimated with no confidence to be 5,000 rows (

          40,875,000 bytes).  The estimated time for this step is 0.21

          seconds. 

       2) We do an all-AMPs RETRIEVE step from CDM_OPS.CNC_CLAIMED_TXN

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

          "((CDM_OPS.CNC_CLAIMED_TXN.CLAIM_STTS = 'PENDING') OR

          (CDM_OPS.CNC_CLAIMED_TXN.CLAIM_STTS = 'ERROR')) AND (NOT

          (CDM_OPS.CNC_CLAIMED_TXN.RPTG_TXN_ID IS NULL ))") into Spool

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

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

          by the hash code of (CDM_OPS.CNC_CLAIMED_TXN.RPTG_TXN_ID). 

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

          303,855 rows (24,612,255 bytes).  The estimated time for this

          step is 0.07 seconds. 

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

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

     of a RowHash match scan.  Spool 12 and Spool 13 are left outer

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

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

     condition of ("TRANSACTION_ID = RPTG_TXN_ID").  The result goes

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

     redistributed by the hash code of (

     CDM_ST.REPORTING_TRANSACTION.SHIP_TO) to all AMPs.  Then we do a

     SORT to order Spool 14 by row hash.  The size of Spool 14 is

     estimated with no confidence to be 5,499 rows (45,328,257 bytes). 

     The estimated time for this step is 11.01 seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 14

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (SHIP_TO IS NULL)"), with a join condition of ("SHIP_TO =

     CDM_OPS.PRTY_KEY.prty_key_id").  The input table CDM_OPS.PRTY_KEY

     will not be cached in memory.  The result goes into Spool 15

     (all_amps) (compressed columns allowed), which is redistributed by

     the hash code of (CDM_ST.REPORTING_TRANSACTION.BILL_TO) to all

     AMPs.  Then we do a SORT to order Spool 15 by row hash.  The size

     of Spool 15 is estimated with no confidence to be 5,500 rows (

     50,088,500 bytes).  The estimated time for this step is 5.96

     seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 15

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (BILL_TO IS NULL)"), with a join condition of ("BILL_TO =

     CDM_OPS.PRTY_KEY.prty_key_id").  The input table CDM_OPS.PRTY_KEY

     will not be cached in memory.  The result goes into Spool 16

     (all_amps) (compressed columns allowed), which is redistributed by

     the hash code of (CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY) to

     all AMPs.  Then we do a SORT to order Spool 16 by row hash.  The

     size of Spool 16 is estimated with no confidence to be 5,501 rows

     (54,850,471 bytes).  The estimated time for this step is 6.47

     seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 16

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (ORDERING_PARTY IS NULL)"), with a join condition of (

     "ORDERING_PARTY = CDM_OPS.PRTY_KEY.prty_key_id").  The input table

     CDM_OPS.PRTY_KEY will not be cached in memory.  The result goes

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

     redistributed by the hash code of (

     CDM_ST.REPORTING_TRANSACTION.PARTICIPATING_PARTY) to all AMPs. 

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

     Spool 17 is estimated with no confidence to be 5,502 rows (

     59,614,170 bytes).  The estimated time for this step is 7.02

     seconds. 

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

     RowHash match scan, which is joined to CDM_OPS.PRTY_KEY in view

     CDM_OPS_EV.PRTY_KEY by way of a RowHash match scan with no

     residual conditions locking CDM_OPS.PRTY_KEY for access.  Spool 17

     and CDM_OPS.PRTY_KEY are left outer joined using a merge join,

     with condition(s) used for non-matching on left table ("NOT

     (PARTICIPATING_PARTY IS NULL)"), with a join condition of (

     "PARTICIPATING_PARTY = CDM_OPS.PRTY_KEY.prty_key_id").  The input

     table CDM_OPS.PRTY_KEY will not be cached in memory.  The result

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

     code of (CDM_ST.REPORTING_TRANSACTION.CUSTOM_PRICE_1,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_3,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_2,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id,

     CDM_OPS.PRTY_KEY.clean_prty_postal_cd,

     CDM_OPS.PRTY_KEY.clean_prty_rgn_st_prov,

     CDM_OPS.PRTY_KEY.clean_prty_city,

     CDM_OPS.PRTY_KEY.clean_prty_addr_1,

     CDM_OPS.PRTY_KEY.clean_prty_name, CDM_OPS.PRTY_KEY.prty_key_id, (

     CASE WHEN (NOT (CDM_OPS.CNC_CLAIMED_TXN.RPTG_TXN_ID IS NULL ))

     THEN (0) WHEN ((UPPER((CDM_ST.REPORTING_TRANSACTION.RECORD_TYPE

     )))= 'MCACT1') THEN (0) ELSE (1) END),

     CDM_ST.REPORTING_TRANSACTION.PRDCT_MODEL_NM,

     CDM_ST.REPORTING_TRANSACTION.FREE_DATE_ATTRIBUTE_3,

     CDM_ST.REPORTING_TRANSACTION.FREE_DATE_ATTRIBUTE_2,

     CDM_ST.REPORTING_TRANSACTION.FREE_DATE_ATTRIBUTE_1,

     CDM_ST.REPORTING_TRANSACTION.FREE_NUM_ATTRIBUTE_3,

     CDM_ST.REPORTING_TRANSACTION.FREE_NUM_ATTRIBUTE_2,

     CDM_ST.REPORTING_TRANSACTION.FREE_NUM_ATTRIBUTE_1,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_3,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_2,

     CDM_ST.REPORTING_TRANSACTION.FREE_CHAR_ATTRIBUTE_1,

     CDM_ST.REPORTING_TRANSACTION.POSTING_DATE,

     CDM_ST.REPORTING_TRANSACTION.REASON_FOR_ORDER,

     CDM_ST.REPORTING_TRANSACTION.SALES_DOCUMENT_TYPE,

     CDM_ST.REPORTING_TRANSACTION.SALES_DOCUMENT_ITEM,

     CDM_ST.REPORTING_TRANSACTION.SALES_DOCUMENT_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.CHANNEL_REP,

     CDM_ST.REPORTING_TRANSACTION.ENTERPRISE_REP,

     CDM_ST.REPORTING_TRANSACTION.SGMNTTN_BUSINESS_SEGMENT,

     CDM_ST.REPORTING_TRANSACTION.SGMNTTN_BUSINESS_AREA,

     CDM_ST.REPORTING_TRANSACTION.BUSINESS_PROCESS,

     CDM_ST.REPORTING_TRANSACTION.TRANSACTION_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.PARTICIPATING_PARTY_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.PARTICIPATING_PARTY_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.SHIP_TO_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.SHIP_TO_PARTY_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.SHIP_TO,

     CDM_ST.REPORTING_TRANSACTION.BILL_TO_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.BILL_TO_PARTY_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.BILL_TO,

     CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY,

     CDM_ST.REPORTING_TRANSACTION.SOURCE_FROM_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.REPORTER_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.SOURCE_FROM,

     CDM_ST.REPORTING_TRANSACTION.REPORTER_COUNTRY,

     CDM_ST.REPORTING_TRANSACTION.ORIGINAL_REPORTER_SALES_ORG,

     CDM_ST.REPORTING_TRANSACTION.ORIGINAL_REPORTER_ID,

     CDM_ST.REPORTING_TRANSACTION.SALE_TYPE,

     CDM_ST.REPORTING_TRANSACTION.SALE_DATA_SOURCE,

     CDM_ST.REPORTING_TRANSACTION.ORDERING_PARTY_PO_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.INVOICE_LINE_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.INVOICE_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.SFA_INTERNATIONAL_CONTRACT_REF,

     CDM_ST.REPORTING_TRANSACTION.SAP_CONTRACT_PO_NUM,

     CDM_ST.REPORTING_TRANSACTION.AGREEMENT_CONTRACT_TYPE,

     CDM_ST.REPORTING_TRANSACTION.AGREEMENT_CONTRACT_NUMBER,

     CDM_ST.REPORTING_TRANSACTION.REPORTED_DATE,

     CDM_ST.REPORTING_TRANSACTION.CURRENCY_OF_THIS_TRANSACTION,

     CDM_ST.REPORTING_TRANSACTION.REPORTED_PRICE,

     CDM_ST.REPORTING_TRANSACTION.CUSTOM_PRICE_3,

     CDM_ST.REPORTING_TRANSACTION.CUSTOM_PRICE_2,

     CDM_ST.REPORTING_TRANSACTION.CUSTOM_PRICE_1,

     CDM_ST.REPORTING_TRANSACTION.BSD, CDM_ST.REPORTING_TRANSACTION.AUR,

     CDM_ST.REPORTING_TRANSACTION.TRIPLE_NET_STUR_SAUR,

     CDM_ST.REPORTING_TRANSACTION.NET_STUR_SAUR,

     CDM_ST.REPORTING_TRANSACTION.SUR,

     CDM_ST.REPORTING_TRANSACTION.LIST_PRICE,

     CDM_ST.REPORTING_TRANSACTION.DISTRIBUTOR_PRICE,

     CDM_ST.REPORTING_TRANSACTION.TRANSACTION_PRICE,

     CDM_ST.REPORTING_TRANSACTION.UNITS,

     CDM_ST.REPORTING_TRANSACTION.PARTNER_MATERIAL,

     CDM_ST.REPORTING_TRANSACTION.PRODUCT_HIERARCHY,

     CDM_ST.REPORTING_TRANSACTION.MATERIAL,

     CDM_ST.REPORTING_TRANSACTION.CHANGE_BY,

     CDM_ST.REPORTING_TRANSACTION.RECORD_CHANGE_DATE,

     CDM_ST.REPORTING_TRANSACTION.RECORD_CREATION_DATE,

     CDM_ST.REPORTING_TRANSACTION.click_claim_flag,

     CDM_ST.REPORTING_TRANSACTION.POTENTIAL_DUPLICATE,

     CDM_ST.REPORTING_TRANSACTION.KIT_PRNT_COMPONENT_OR_NOT_KIT,

     CDM_ST.REPORTING_TRANSACTION.IS_A_VALID_RPTER_TO_NON_RPTER,

     CDM_ST.REPORTING_TRANSACTION.TRANSACTION_ID,

     CDM_ST.REPORTING_TRANSACTION.SERVICES_RENDERED_DATE,

     CDM_ST.REPORTING_TRANSACTION.PARTICIPATING_PARTY) 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

     no confidence to be 5,000 rows (204,230,000 bytes).  The estimated

     time for this step is 3.74 seconds. 

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

     in processing the request.

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

     statement 1. 

We are only changing the parameter TRANSACTION_COUNTRY value and query execution time are different. We have checked the explain plan and it was also changing.

Can anybody help me to resolve this.

1 REPLY
Junior Contributor

Re: Query Performance Issue

Hi Asish,

did you check DBQL for estimated vs. actual number of rows?

The optimizer's choice picking the index on transaction_country is bad. 

There's a bad condition, comparing string to numeric, which results in a loss of statistics:

and FREE_CHAR_ATTRIBUTE_1 IN (20,10,30,50) 

better change to

and FREE_CHAR_ATTRIBUTE_1 IN ('20','10','30','50')

Check if this helps.

What's the partitioning/indexes of this table?