All Forums Database
sushil.paras 2 posts Joined 03/14
19 Jan 2016
Master index

hi,

Can somebody please explain how teradata finds a cylinder for the following scenario.

 

We know master index contain following entry.

 

• Lowest table ID in the cylinder

• Lowest partition/rowhash/uniqueness value on the cylinder (associated with the lowest table ID)

  This value is 0 for NPPI tables.

• Highest table ID in the cylinder

• Highest partition rowhash (not the uniqueness value) value on the cylinder (associated with the highest table ID)

  This value is 0 for NPPI tables.

• Cylinder number

 

The process for finding the required cylinder block is as follows.

1 The file system performs a binary search on the master index using the target tableID and rowID as the search key.

2 This results in locating the cylinder ID for cylinder number 169

 

Please help me to understand how teradata seaches for a table whose tableID is neither lowest nor highest in any of the cylinders.

 

My understanding is as following and please correct me if i am wrong at any point.

 

1) A cylinder can contain data for many tables and there are as many entries in master index as there are cylinders in that AMP.

   Consider following scenario with following assuptions.

    

   1) There are only 4 cylinder allocated to the AMP.

   2) The table Teradata is looking for has tableID = 6

   3) "x" represents that cylinder 3 and 4 does not have data for tableID 6 from the 

      fact that lowest and highest table ids are greater than tableId 6

   4) I have mentioned 6 between between lowest TableID and Highest TableID for ease of understanding.

 

  Lowest       Highest   Cylinder
  Table ID     Table ID    ID
          
    1       6     10         1
    3       6     23         2
    7              9         3       x 
   11             20         4       x

 

 

Please help me to understand how teradata searches for the appropriate cylinder(s) which in this case is 1 and 2.

 

Thanks

 

dnoeth 4628 posts Joined 11/04
20 Jan 2016

You got something wrong, the Table/RowID ranges stored on cylinders will never overlap.
The "Lowest Table/RowID" of the (logically) following cylinder will be greater than the "Highest Table/RowID" of the previous cylinder. Based on your example it might be stored like that:

Lowest       Highest   Cylinder
Table ID     Table ID    ID
         
  1              4         4
  5       6      7         2
  7              9         3 
 11             20         1

 

Dieter

sushil.paras 2 posts Joined 03/14
20 Jan 2016

Thanks Dieter for explaining this in very clear and simple way.

You must sign in to leave a comment.