Purpose of Primary Index :
• To explain the distribution of the rows to the AMPs.
• To provide access to rows more efficiently than with a full‑table scan.
• To provide for efficient joins.
• To provide a means for efficient aggregations.
ROW ID = Rowhash + Unique Value
Row Value = Row ID Appending to Data
Row Value = Row ID Appending to Data
Row Hash: It will be calculated by the Parsing engine passing respective row to the Hashing algorithm.
Unique Value: It is generally used for UPI (the value incremented based if two columns has the same value.
Unique Value: It is generally used for UPI (the value incremented based if two columns has the same value.
When a row is inserted into a table, the file system stores the 32-bit row hash value of the primary index primary indexed Teradata Database table rows are self-indexing based on their primary index and so takes no additional storage space.
For Unique Primary Index Unique value is 0 and for Non Unique primary Index value is incremented in ascending order for same row hash values.
Types of Primary Index : There are two types of Primary Index
- Unique Primary Index
- Non Unique Primary Index
Hashing in Primary Index :
1) One PRIMARY INDEX per table.
2) PRIMARY INDEX value can be UNIQUE OR NON-UNIQUE.
3) PRIMARY INDEX value can be null.
4) PRIMARY INDEX value can be modified.
5) PRIMARY INDEX of a populated table can not be modified.
6) PRIMARY INDEX has a limit of 64 columns.
0 Comments