 |
 |
Archives of the TeradataForum
Message Posted: Fri, 17 Mar 2006 @ 12:12:22 GMT
| Subj: | | Re: Identifying Large NUSI ? |
| |
| From: | | Dieter Noeth |
Victor Sokovin wrote:
| | Ole, I think it might even be 10 for NPPI. Take, for example, the recent posting in the thread entitled "PPI and Master/Cylinder Index
structures". I will quote one paragraph from it: | |
| | "At first sight secondary indexes structures would not need the Partition # for NPPI tables, but I guess that in order to have a standard
row key layout (row key replaces the concept of row id, while formerly we had Row Hash + Uniqueness Value, we now have Partition #, + Row Hash +
Uniqueness, the row key has 10 bytes as it has been explained earlier), probably when a new secondary index is built for NPPI it also includes the
Partition # equal to zero, using the two additional bytes. I guess this dependes directly on the code." | |
| | OK, this is not a categoric statement yet but it does sound very plausible. For the sake of the uniform approach they might have added the
0-partition part even to NUSI on NPPI. It would be good to have this confirmed. | |
There's never a partition 0 physically stored:
The partition number is stored apart from the ROWID, according to the manuals after the first two presence bytes. A partition 0 is simply
COMPRESSed using one of the "reserved" bits in the first presence byte. Or maybe it simply doesn't exists at all, it's just one IF in the source
code.
If you query the ROWID it's always returning the new RowKey including the partition, but this is cstructed from old RowID and partition #.
Btw, during an upgrade to V2R5 only the cylinder index format changed. If the record layout would be different in V2R5 then *every*
table/datablock had to be changed during an upgrade. Ouch.
| | Anyway, it looks like the index size formulae in the "Database Design" > manual need to be amended so that the changes made for
partitioning are reflected there. The V2R6.0 version does not seem to be ? jour. I have not checked the V2R6.1. | |
It's correct in V2R6.1.
If rows/value is high (>> #AMPs) the size approaches 8/10 bytes/row.
If rows/value is low (< #AMPs) the size of the NUSI is similar to a USI.
But if the values are not evenly distributed, it somewhere inbetween.
Dieter
| |
