CODEWITHSUNDEEP
teradata
puneet madan
puneet madan

Reputation: 3

Need to apply Primary Indexes and secondary indexes in teradata tables

Can some one please help in solving my problem I have three tables to be joined ed using indexes in Teradata to improve performance. Query specified below:-

Select b.Id,  b.First_name,  b.Last_name, c. Id,
   c.First_name,  c.Last_name,  c.Result 
from
 (
   select a.Id,  a.First_name,  a. Last_name,  a.Approver1,  a.Approver2
   From table1 a
   Inner join table2 d
   On a.Id =D.Id
   and A.Approver1 =a.Approver1
   And a.Approve2 =D.Approver2
 )  b
Left join
 (
   select * from table3 
   where result is not null 
   and application like 'application1' 
 ) c
On c. Id=b.Id
Group by b.Id,  b.First_name,  b.Last_name, c.Id,
   c.First_name,  c.Last_name, c.Result

The above query is taking so much of time since PI not defined correctly. First two tables (table1 and 2) are with same set of columns hence pi can be defined like PI on I'd, approve1, approve2 However, while joining with table3 am confused and need to understand how to define pi. Is it something that PI can only work when we have same set of columns in the tables?

Structure of table3 is I'd, first name, last name, result

And table 1 and table2 Id , First name, Last name, Approved 1, Approved 2, Results

Can you please help in defining primary indexes so that query can be optimised.

Upvotes: 0

Views: 536

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Teradata will usually not use Secondary Indexes for joins. The best PI would be id for all three tables, of course you need to check if there are not too many rows per value and it's not too skewed.

GROUP BY can be simplified to a DISTINCT, why do you need it, can you show the Primary Keys of those tables?

Edit based on comment:

PI-based joins are by far the fastest way. But you should be able the get rid of the DISTINCT, too, it's always a huge overhead.

Try replacing the 1st join with a NOT EXISTS:

Select b.Id,  b.First_name,  b.Last_name, c. Id,
   c.First_name,  c.Last_name,  c.Result 
from
 (
   select a.Id,  a.First_name,  a. Last_name,  a.Approver1,  a.Approver2
   From table1 a
   WHERE EXISTS
     ( 
       SELECT *
       FROM table2 d
       WHERE a.Id =D.Id
         and A.Approver1 =a.Approver1
         And a.Approve2 =D.Approver2
     )
 )  b
Left join
 (
   select * from table3 
   where result is not null 
   and application like 'application1' 
 ) c
On c. Id=b.Id

Upvotes: 1

Related Questions