
Archives of the TeradataForumMessage Posted: Tue, 02 Dec 2003 @ 13:39:00 GMT
Rachana Agarwal wrote :
I write : 1. In Teradata Primary Index do not need any extra space. 2.1 For USI The following formula can be used to estimate size of a USI subtable. p = Number of data rows in the base table  same as USI subtable k = Length (in bytes) of a fixed length USI value (or the average length of a variable USI value) po = Presence bit overhead (ceiling): ((1 + number of nullable USI fields) / 8); if none, 0 vo = Variable length field overhead: (number of variable length USI fields + 1) * 2. For example Row length 2 USI RowID 8 Spare0 1 Presence 1 Offsets 6 Base Table RowID 8 Reference Array 2  28 USI subtable (no fallback) size = p * ( po + vo + k + 28) The offsets are used as following: Offset[0] is the offset of the first byte for the USI value 2.2 For NUSI... The following information is needed to calculate the size of a NUSI subtable. * (Row Count * 8) is derived from the 8 bytes of Row ID which the subtable stores for each row in the base table. This gives us the total number of bytes devoted to base table Row IDs. * (# distinct values) is an estimate of the number of NUSI subtable rows since a NUSI subtable contains at least one index row per AMP for each distinct index value in the base table on that AMP. * The 20 bytes of overhead per subtable row comes from the same place that the 28 bytes did for USIs. The offsets are used as following: Offset[0] is the offset of the first byte for the NUSI value. * MIN(NumAMPs, RowsPerValue) is the minimum of the two (see Case 1 and Case 2 below). Case 1: NumAMPs < RowsPerValue Case 2: NumAMPs > RowsPerValue Thanks, Vivek.
 
 
Copyright 2016  All Rights Reserved  
Last Modified: 23 Jun 2019  