Master index

Database
Teradata Employee

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

2 REPLIES
Senior Apprentice

Re: Master index

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
Teradata Employee

Re: Master index

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