Archives of the TeradataForum

Message Posted: Fri, 14 Mar 2008 @ 10:16:00 GMT

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

A developer coded a query two different ways with puzzling results. Joining to the table using a primary index column takes longer to return data than joining to the table using a non-index column containing the same value. Comparing the spool space used in the two examples, the index query uses far less spool space but still takes longer. Does anyone have an explanation for this behavior? The column and index have up-to-date statistics collected. Thanks.

Example with no index:

1) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to Spool 5 by way of a RowHash match scan. Spool 4 and Spool 5 are joined using a merge join, with a join condition of ("PARTY_ID = PARTY_ID"). The result goes into Spool 1 (group_amps), which is redistributed by hash code to all AMPs. The size of Spool 1 is estimated with low confidence to be 6,993,445 rows. The estimated time for this step is 2 minutes and 47 seconds.

Example with index:

1) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of a RowHash match scan, which is joined to PM_EDW_MSTR_D.d_ORGANIZATION by way of a RowHash match scan. Spool 4 and PM_EDW_MSTR_D.d_ORGANIZATION are joined using a merge join, with a join condition of ("PARTY_ID = PM_EDW_MSTR_D.d_ORGANIZATION.ORG_ID"). The input table PM_EDW_MSTR_D.d_ORGANIZATION will not be cached in memory. The result goes into Spool 1 (group_amps), which is redistributed by hash code to all AMPs. The size of Spool 1 is estimated with low confidence to be 6,993,445 rows. The estimated time for this step is 4 minutes and 51 seconds.

*** Teradata Database Release is V2R.06.01.01.49
*** Teradata Database Version is 06.01.01.51

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