Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 13 Feb 2013 @ 16:41:19 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Strange Optimization Problem with PPI
From:   Dieter Noeth


  The PPI is defined as - RANGE_N((HASHBUCKET(HASHROW(COL1 )))/ 16 BETWEEN 498 AND 65401 EACH 3000 , along with other PPIs.  

This should be 0 and 65535 each 3000, otherwise you might get a partitioning violation for new values.

What's the datatype of COL1?

  Let's say COL1 has values like 1000, 2000, 2400, 2900, 3000, 3200 .. 10,000 and defines regions within a continent.  

  In a downstream application, this table is referred in a join, the PPI is used in a 'WHERE' condition where a range is looked up to segregate data by continent. There are two exactly similar queries for Asia region and Europe region where the only difference is in the WHERE clause of COL1.  

Different WHEREs will result in different numbers of estimated rows, which might change the join type.

  The problem is the query for Asia region is utilizing huge spool while the Europe region query is running normally. I checked the partitions to make sure they are evenly distributed for Asia region.  

Could you post the Explains and the existing Stats?


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020