Index question

Database
Enthusiast

Index question

Hello Teradata Gurus/Enthusiasts,

 

Primary Index is esentially how TD will decide how rows are distributed across Amp's. Under Ideal conditions PI (UPI,NUPI) are one Amp operations. Since all unique row Id records are put in an unique Amp and when we submit a sql ("Select * from Employee where Emp_Id=1;" PI is on Emp_id) it just points to the AMP where the record is stored.

 

Employee Table:

Emp_Id  Emp_Name

1            Raj

2            Shiva

3            Sid

4            Aditya

 

But what if I have a query that says "Select * from Employee where Emp_Id=1 And Emp_Name= 'Sid';" (NUPI on Emp_id and no other indexes) How many Amp operation is it? Is it just one Amp? Or How does PE/Amp's deal with Inequality conditions, conditional operations (<,>) ??

 

Employee Table:

Emp_Id  Emp_Name

1            Raj

1           Shiva

1            Sid

1          Aditya

 

Can anyone explain me the PE/Amp level functioning of the query above?

 

-Thanks in Advance

Siddarth

 

2 REPLIES
Enthusiast

Re: Index question

Hi,

 

PI query is always an 1 AMP operation. If it is UPI, it will return 1 row and if it a NUPI, it will return more than 1 rows. So, even if you add a condition - name not like '%SID%' and emp_no=1 - in the 2nd condition, it will do an 1 amp operation and search all rows, and return you more than 1 row. You can check this in the explain plan. it will say 1 amp operation.

--samir

Enthusiast

Re: Index question

Thanks Samir! I was sure it will be a 1 Amp but wanted to check. Kudos!