All Forums Database
Ashwin4tera 19 posts Joined 11/11
25 Jul 2014
Avoid Product Join Step

Hi,
Could you please suggest how to avoid this product join step and explain about this step.
Usually how should i understand this kind of steps all stats seems to be up to date on these columns.
 

17) We do an all-AMPs JOIN step from Spool 26 (Last Use) by way of an
all-rows scan, which is joined to Spool 27 (Last Use) by way of an
all-rows scan. Spool 26 and Spool 27 are joined using a product
join, with a join condition of ("(CLIENT_NBR = CLIENT_NBR) AND
(POP_ID = POP_ID)"). The result goes into Spool 15 (all_amps)
(compressed columns allowed), 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 252,638,840,002
rows (108,887,340,040,862 bytes). The estimated time for this
step is 3 hours and 36 minutes.
 
Thank you.

 

goldminer 118 posts Joined 05/09
25 Jul 2014

I will supply the lowest hanging fruit!  :-)
 
Statistics

dnoeth 4628 posts Joined 11/04
26 Jul 2014

As there's a good join-condition the product join was choosen by the optmizer cause it's the best way to get the result.
Did you check in DBQL if the estimated number of rows is actually in that range?
What are the steps producing spool 26 and 27?

Dieter

Ashwin4tera 19 posts Joined 11/11
28 Jul 2014

Hi Dieter,
The actual rows are not in that range. And the stats on columns CLIENT_NBR and POP_ID are up to date.
Here are the steps for spool 26 and 27.
Spool 26:
   2) We do a group-AMP RETRIEVE step from Spool 2 (Last Use) by way
         of an all-rows scan with a condition of (
         "(population_client_user.USER_ID = 'XWL5KAM') AND
         ((population_client_user.CLIENT_NBR = 5610) AND
         (population_client_user.POP_ID = 0 ))") into Spool 26
         (all_amps) (compressed columns allowed), which is duplicated
         on all AMPs.  The size of Spool 26 is estimated with no
         confidence to be 2,841,072 rows (102,278,592 bytes).  The
         estimated time for this step is 0.08 seconds.
Spool 27:
16) We do an all-AMPs JOIN step from Spool 24 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 25 (Last Use) by way
     of a RowHash match scan.  Spool 24 and Spool 25 are joined using a
     merge join, with a join condition of ("SK_HICL_CDE = SK_HICL_cde").
     The result goes into Spool 27 (all_amps) (compressed columns
     allowed), which is built locally on the AMPs.  The size of Spool
     27 is estimated with no confidence to be 110,976,868 rows (
     46,832,238,296 bytes).  The estimated time for this step is 7.10
     seconds.
Thank you.
 

dnoeth 4628 posts Joined 11/04
29 Jul 2014

Both steps show no confidence, are you sure that the necessary stats exist?

Dieter

Ashwin4tera 19 posts Joined 11/11
31 Jul 2014

Hi Dieter,
The stats are up to date for these columns double checked what could be the issue.
is there any way to find stale stats on table.
Thank you.

dnoeth 4628 posts Joined 11/04
31 Jul 2014

You need to show the actual query plus explain to find out.

Dieter

You must sign in to leave a comment.