Archives of the TeradataForum
Message Posted: Fri, 22 Feb 2013 @ 13:47:52 GMT
<-- Anonymously Posted: Friday, February 22, 2013 08:00 -->
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
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
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 24 Jul 2020|