Archives of the TeradataForum

Message Posted: Wed, 13 Feb 2013 @ 15:28:33 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Strange Optimization Problem with PPI

From:		DEBOJIT.PAL

Hello Gurus,

I am facing a strange problem with PPI on a particular table.

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

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.

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.

Can anybody throw some light on what is happening here?

Thanks!

Debojit.