Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 19 Dec 2003 @ 10:47:45 GMT

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

Subj:   Re: can anyone help me out here
From:   Narayan Murthy

Using NUSI or not is a question which the optimizer would try resolving based on how many datablocks would get accessed. Since it is a nusi we aren't considered about how many amps gets accessed because it is an all-amp operation. So though we have a NUSI defined, if the optimizer finds out that accessing rows for a particular NUSI value would more or less cover all the base table datablocks(a full table scan) then it would be better of for it to go for a full table scan, because a NUSI access always involves first accessing the NUSI subtable and then fetching the base table rows.

So if a column(s) is to be considered for a NUSI one should considered to what extent the column(s) tends towards containing less number of rows per NUSI value.

An Example :

If you had :

10000 rows each of size 20 bytes.

Size of a datablock = 2000 bytes

# of rows/datablock = 2000/20 = 100 rows/datablock

# of datablocks = 10000/100 = 100 datablocks.

Suppose the NUSI had 10 distinct values and there is an equal distribution of each of these values. Then :

# of rows per NUSI value = 10000/10 = 1000 rows - (A)

So considering 1000 rows/NUSI value and 100 datablocks - the likehood of scanning all the base table datablocks for a given NUSI value is more - A FULL TABLE SCAN. Here not only would you land up doing a full table scan of the base subtable but you would also be accessing the NUSI subtable datablocks. So in such case a FULL TABLE SCAN would be better than a NUSI access.

Thereby according to Equation (A) if the number of rows per NUSI value where to decrease then it is less likely we would land up scanning all the base table datablocks and thereby a NUSI access would work out better than a full table scan.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019