 |
 |
Archives of the TeradataForum
Message Posted: Wed, 13 Feb 2013 @ 16:41:19 GMT
| Subj: | | Re: Strange Optimization Problem with PPI |
| |
| From: | | Dieter Noeth |
DEBOJIT.PAL wrote:
| | 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?
Dieter
| |
