Archives of the TeradataForum
Message Posted: Tue, 16 Sep 2003 @ 17:55:38 GMT
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
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|