Archives of the TeradataForum

Message Posted: Tue, 24 Jul 2007 @ 22:03:42 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Pls find the following explain plan for step4.

     4) We execute the following steps in parallel.
          1) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of
             an all-rows scan, which is joined to Spool 5 (Last Use) by
             way of an all-rows scan.  Spool 4 and Spool 5 are left outer
             joined using a product join, with a join condition of (
             "(imei <= end_imei) AND (imei >= beg_imei)").  The result
             goes into Spool 6 (all_amps), which is built locally on the
             AMPs.  The size of Spool 6 is estimated with no confidence to
             be 300,410,527 rows.  The estimated time for this step is 4
             minutes and 30 seconds.
          2) We do an all-AMPs RETRIEVE step from mktdb.esn_ref by way of
             an all-rows scan with no residual conditions into Spool 7
             (all_amps), which is duplicated on all AMPs.  The size of
             Spool 7 is estimated with high confidence to be 2,950,828
             rows.  The estimated time for this step is 3.20 seconds.

The above explain step 4 trying to build SPOOL 6 using SPOOL 4 and 5.

SPOOL 4 is built like:

The size of Spool 4 is estimated with low confidence to be 150,205,354 rows.

SPOOL 5 is built like:

The size of Spool 5 is estimated with high confidence to be 2,677,836 rows.

Doubt:

How to improve confidence level in SPOOL 6 in the above explain plan.

Could you please let me know best practice tips on built locally on the AMPs SPOOL space?

Appreciate for your help.

Regards,

Ramu.

	<Prev	Next>		<<First	<Prev	Next>	Last>>