Archives of the TeradataForum

Message Posted: Fri, 22 Feb 2013 @ 13:47:52 GMT

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

<-- Anonymously Posted: Friday, February 22, 2013 08:00 -->

Hello All,

I had to change the PI of a large table of mine for better access. While inserting into the new table from old, I'm running a spool space error.

OLD TABLE has the below definition

     PRIMARY INDEX ( MAINT_COST_TYPE_BM ,EVNT_BK ) PARTITION BY MAINT_COST_TYPE_BM ;

NEW TABLE has been redefined as

     PRIMARY INDEX (EVNT_BK )
     PARTITION BY MAINT_COST_TYPE_BM ;

The column EVNT_BK is an integer and it spreads uniformly across all amps when checked with the hashamp(hashbucket(hashrow))) function

I'm trying to do INS into Sel * from

I was expecting spool to be redistributed on EVNT_BK ONLY but it strangely EXPLAIN gives the below statement:

We do an all-AMPs RETRIEVE step from OLD TABLE by way of an all-rows scan with no residual conditions into Spool 1 (all_amps), which is redistributed by rowkey to all AMPs with hash fields ("OLD TABLE.EVNT_BK, OLD TABLE.MAINT_COST_TYPE_BM"). Then we do a SORT to partition Spool 1 by rowkey followed by. We do an all-AMPs MERGE into from Spool 1 (Last Use).

The queries in my mind are

- Why is the spool distributed on ?"OLD TABLE.EVNT_BK, OLD TABLE.MAINT_COST_TYPE_BM", though target table is defined a PI on EVNT_BK only?

- How is the merge done given that the target and spool PIs are different?

- It shouldn't really run out of spool space because spool 1 file would also have pretty good distribution across all amps, so there is no hot amp scenario here. I double checked it using the hashamp function on the PI columns of the spool as well as target.

Has it got anything to do with partition column? Kindly help me in understanding this.

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