Archives of the TeradataForum
Message Posted: Tue, 16 Sep 2003 @ 17:55:38 GMT
| 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
| 
