Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 16 Sep 2003 @ 17:55:38 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Unresolved Query using Partitioned Hash Joins
From:   McBride, Michael

I have a query that refuses to complete and seems to spend hours spinning in one step of a job...

41)  We do an All-AMPs JOIN step from Spool 9029 (Last Use) by way of an
all-rows scan, which is joined to Spool 9030. Spool 9029 and Spool 9030
are joined using a hash join of 18 partitions. ...

The query never gets past step #41 (left running for over 5 hours). The database is at V2R4.1.2, and is a two node system. The data being queried is roughly 60 gigabytes encompassing no more than 240 million rows. I do not understand why a partitioned hash join would cause the query to hang? Does anyone have any ideas.

One thing I could do has change the DBS Control record to not allow partitioned hash joins and see if the query will run, but I'm afraid that that will adversely affect other production queries negatively, that I know have better performance with hash joins than without.

Under what scenarios will a partitioned hash join perform this poorly? Has anyone else experience severe negative query performance using hash joins? Is there anyway to allow partitioned hash joins in general, but prevent the optimizer from using them on a specific query?

Michael E. McBride
Teradata Certified Master
Teradata Database Administrator
Data Architect and Data Warehouse Practitioner
American Eagle Outfitters

  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023