If you are wondering what makes Teradata to perform faster then here is the answer, it is the “Primary Index”. This is one of the most important concepts to understand in Teradata.    Teradata achieves its exceptional query performance and reduced administration by automatically distributing data across all available hardware resources, with the help of Primary Indexes. Teradata uses the value of the primary index of each table to determine where (physically) to place individual rows.  Thus, the more unique the values of the column(s) that define the primary index, the better the data distribution for that table and the better the performance in accessing it.

The primary index of a table should not be confused with the table’s primary key.  A table can define a primary key that is the same or different from the primary index.  They are mutually exclusive.  In fact, a table need not have a primary key at all, but it must always have a primary index. All tables must have a primary index.  If not specified explicitly when the table is created, Teradata uses the first column of the table definition as the primary index by default.  The primary index can be comprised of one or more columns and does not need to be absolutely unique, although more uniqueness is always better.   Let’s take the following CREATE table example:

CREATE TABLE salary
(
 emp_id INTEGER,
 first_name VARCHAR(30),
 last_name VARCHAR(30),
amount   INTEGER
)
PRIMARY INDEX (emp_id) ; 
 

As you see, in the example above, the table does not specify a primary key, which is perfectly fine.  If the values present in the emp_id column are unique in nature, then this column can be considered as a candidate for the Primary Key. While primary key can be declared either implicitly, or explicitly as follows:

CREATE TABLE salary
(
 emp_id INTEGER,
 first_name VARCHAR(30),
 last_name VARCHAR(30),
 amount   INTEGER,
 PRIMARY KEY(emp_id)
)
PRIMARY INDEX (emp_id) ;

In either case (having a primary key and without a primary key), the primary index is used internally within Teradata to distribute the rows of the table, while the primary key is used to uniquely identify each row within the table in order to support application requirements. But having both definitely improves faster retrieval of query results.

Our Random Articles

More Links