Avoid prdct join

Database
Enthusiast

Avoid prdct join

Hi al!

I have this simple update statement but the product join index is very high-221. Is there anyway to avoid the prdct join?

UPDATE View.TABLEA FROM

(SELECT TGT_KY,DATA_DT FROM View.TABLB_STG WHERE SRC_CD='OI-PF') TBL_DERV

SET END_DT= TBL_DERV.DATA_DT

WHERE END_DT='2001-12-31'

AND trim(INV_SRC_ID)||'|'||trim(INV_PRD__ID)||'|'||trim(ARGM_NBR) = TBL_DERV.TGT_KY;;

PRIMARY INDEX TABLB_STG _PI (TGT_KY);

PRIMARY INDEX TABLEA _PI (ARGM_NBR);

1) First, we lock a distinct TABLEDB."pseudo table" for write on a

     RowHash to prevent global deadlock for TABLEDB.TABLEA.

  2) Next, we lock TABLE2DB.TABLB_STG for access, and we lock

     TABLEDB.TABLEA for write.

  3) We do an all-AMPs RETRIEVE step from TABLE2DB.TABLB_STG by way of

     an all-rows scan with a condition of (

     "TABLE2DB.TABLB_STG.SRC_CD = 'OI-PF'") into Spool 2

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

     is estimated with no confidence to be 2,250 rows.  The estimated

     time for this step is 0.01 seconds.

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

     all-rows scan, which is joined to TABLEDB.TABLEA by way of

     an all-rows scan with a condition of (

     "TABLEDB.TABLEA.END_DT = DATE '2100-12-31'").  Spool 2 and

     TABLEDB.TABLEA are joined using a product join, with a join

     condition of ("(((((TRIM(BOTH FROM

     {RightTable}.INV_SRC_ID (VARCHAR(20), CHARACTER SET

     LATIN, NOT CASESPECIFIC, FORMAT

     '------------------9.')))||'|')||(TRIM(BOTH FROM

     {RightTable}.INV_PRD__ID (VARCHAR(20), CHARACTER SET

     LATIN, NOT CASESPECIFIC, FORMAT

     '------------------9.'))))||'|')||(TRIM(BOTH FROM

     {RightTable}.ARGM_NBR (VARCHAR(20), CHARACTER SET LATIN, NOT

     CASESPECIFIC, FORMAT '------------------9.'))))= TGT_KY").

     The input table TABLEDB.TABLEA will not be cached in memory,

     but it is eligible for synchronized scanning.  The result goes

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

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

     spool field1.  The size of Spool 1 is estimated with no confidence

     to be 56,715,964 rows.  The estimated time for this step is 22.22

     seconds.

  5) We do a MERGE Update to TABLEDB.TABLEA from Spool 1 (Last

     Use) via ROWID.

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

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

     BEGIN RECOMMENDED STATS ->

  7) "COLLECT STATISTICS TABLE2DB.TABLB_STG INDEX (TGT_KY)".

     (HighConf)

  8) "COLLECT STATISTICS TABLE2DB.TABLB_STG COLUMN (SRC_CD

     ,TGT_KY)".  (HighConf)

  9) "COLLECT STATISTICS TABLE2DB.TABLB_STG COLUMN SRC_CD".

     (HighConf)

     <- END RECOMMENDED STATS

6 REPLIES
Enthusiast

Re: Avoid prdct join

Anyone suggestions?

Enthusiast

Re: Avoid prdct join

Dieter can you help?

Supporter

Re: Avoid prdct join

So what is your issue with the product join?

At the end - the explain gave you a hint to collect some more stats. 

But - as you know if you join two tables both rows which need to be joined have to be on the same vproc!

The join condition of

trim(INV_SRC_ID)||'|'||trim(INV_PRD__ID) ||'|'||trim(ARGM_NBR) = TBL_DERV.TGT_KY

makes it unlikly that you get good stats infos for the matches in the target table. In any case the concat is not the PI of the target table and therefore you can't expect a direct merge join.

Step 3 estimates 2,250 rows for the derived table after duplication to all amps. If this number is true - product join might be a good choice. If it is wrong. Collect stats and check if the plan change.

Enthusiast

Re: Avoid prdct join

Step 3 shows "no confidence", check if you have stats collected on TABLE2DB.TABLB_STG.SRC_CD column. 

Run "diagnostic helpstats on for session;" and collect the stats recommended with high confidence, check the explain again.

If nothing works out and you really need to avoid product join, use "diagnostic noprodjoin on for session"

Enthusiast

Re: Avoid prdct join

Hi,

 Try this,


UPDATE H1 FROM (SELECT  trim(INV_SRC_ID)||'|'||trim(INV_PRD__ID) ||'|'||trim(ARGM_NBR)  as src_ky , END_DT from View.TABLEA) H1,

(SELECT TGT_KY,DATA_DT FROM View.TABLB_STG WHERE SRC_CD='OI-PF') TBL_DERV

SET END_DT= TBL_DERV.DATA_DT

WHERE H1.END_DT='2001-12-31'

AND H1.SRC_KY= TBL_DERV.TGT_KY;

Thanks,

Karthik N

Enthusiast

Re: Avoid prdct join

Hi Karthik

It ill throw syntax error. we can't update a derived table. I guess the only option is to collect stats on recommended columns.