Dec 25

Join Indexes

The join index JOIN the two tables together and keeps the result set in the permanent space of Teradata. This JOIN index will hold the result set of the two table, and at the time of JOIN parsing engine will decide whether it is fast to build the result set from the actual BASE tables or the JOIN index. User never directly query the JOIN index. In the sense JOIN index is the result of joining two tables together so that parsing engine always decide to take the result set from this JOIN index instead of going and doing manual join on the base table.

Types of JOIN index –
Multi table JOIN index

Suppose we have two BASE tables EMPLOYEE_TABLE and DEP_TABLE, which holds the data of EMPLOYEE and DEPARTMENT respectively. Now a JOIN index on these two tables will be somewhat-


This way the JOIN index EMP_DEPT holds the result set of two BASE tables, and at the time of JOIN PE will decide weather it is faster to join actual tables or to take result set from this JOIN index. So always choose wise list of columns and tables to create JOIN index.

Single Table JOIN index

A single table JOIN index duplicate a single table, but changes the primary index. Users will only query the base table and its PE who decide which result set is faster, from JOIN index or from actual BASE tables. The reason to create the single table JOIN index is so joins can be performed faster because no redistribution or duplication needs to occur.


Aggregate JOIN index

An aggregate JOIN index will allow the tracking of Averages SUM and COUNT on any table. This JOIN index is basically used if we need to perform any aggregate function in the data of the table.


The main fundamentals of JOIN indexes are

    • JOIN index is not a pointer to data it actually store data in PERM space
    • Users never query them directly, its PE who decide which result set to take
    • Updated when base tables are changed
    • Can’t be loaded with Fastload or Multiload.



Skip to comment form

  1. VJ


    How can we find out the data value which is having -ve sign infront of it in the column.? Appreciate your answer.

    1. admin

      am assuming your column is string data type
      code –
      case when substring(‘-123’ from 1 to 1) = ‘-‘
      then ‘Having -ve sign’
      else ‘not having -ve sign’

  2. Babu

    Hi Admin

    Why multiload didn’t support join index?


  3. Babu

    Hi Admin

    Why we are not using join index in multiload?
    Can you please explain?

    Thanks in advance

  4. Sanjeev

    Its very easy to grasp the way things are explained. Thanks for same!!!

  5. silentp7

    we are creating JI to join tables from two (2) different DB’s – A and B. If A is down, for refresh purposes; and, the JI is created on A, will the JI result set (since it is on A) be available ; and, the PE will ignore the off-line B db.

  6. prasadh

    hi admin, it was nice explanation.

    i would like to know how to drop ji.is there a chance to drop the JOIN INDEX /alter the colmns. thanks in advance.

  7. Priyanka Bhakuni

    Can we create join index inside a procedure and drop the same in the procedure?

  8. Sheela

    Hi Admin..
    Thanks for the blog.. Finally i understood JI.
    Can we say JI is something like a view but occupying physical space in DB?

    1. admin

      that’s right !!!

  9. viraj raina

    Hi Admin,

    I’m new to teradata.
    Can you explain single table join index with example in layman language with example 🙂

    1. admin

      single table join index is similar to normal join index, in Single table JI we used only single table and no join with other table is required.
      Main advantage of single table join index is the flexibility to change the PI of the table, which helps to make PI = PI join with other tables.

    2. viraj raina

      Thanks for the reply admin. Please have a look at below questions:-

      1. In 1st para, you are saying that no join with other table is required and in 2nd para you are saying PI=PI join with other table.

      2. In USI and NUSI, can both tables (base table and sub table) reside in same amp. If yes, how?

      The above questions may be elemantary to you but i thank for your efforts you are putting in to clarify people’s doubts

      1. admin

        in single table join index, there is no need to join any other table.
        What i was telling in second para is the usage of single table JI in teradata projects.

        for USI and NUSI please refer secondary index –

      2. Mallesh

        Hi Viraj Raina,

        In case of USI Sub table will reside in one AMP and Base table will reside in another(different ) AMP.

        And in case of NUSI both Sub table and Base table will reside in same AMP.

  10. Mahesh Zac

    Hi Admin,

    I’ve a little doubt in my mind, Is it really required to have a UNIQUE PRIMARY INDEX(EMP_NO) for JOIN INDEX? If it YES, How about distribution for same?

    Can we use another column as UPI for JOIN INDEX, which is not a UPI of same table but column having unique values.


  11. ravi

    Hi Admin,

    When we are creating joining index on 2 tables with 10 columns in the select clause.In my query if we are using 11 th column which is not in the join index will it use join index for join.


    1. admin

      No, if the number of selected columns is more than what given in JI then optimizer will not make use of JI.

  12. admin

    Hi All,

    We have started a new special online teradata training batch for developers and DBA profile. For those who are interested to learn teradata can register in this batch. Fees is quite less when you compare it with other batches and biggest advantage is that its a instructor led training batch so you can ask any doubt in the training itself. Also we’ll be covering latest version of Teradata i.e. Teradata 14.10

    Please check following link for more details about this batch –

    Limited Seats. So try to register ASAP.

  13. sam

    Hi Amin,

    Thanks for such an simple and clear explanation on topics. I’m late for the show but I’m quite enjoying reading through your blog.

    For the question regarding Single Table Join Index and Secondary Index, I think both are different. In STJI the table is stored in Amps by hash value generated on the column(not on the PI column), in your example its “EMP_DEPT”. But in SI we use subtable concept.

    Please correct me if I’m wrong.

    1. admin

      in addition to that, Join Index stores actual ROWS (based on your select query of JI) while Secondary Index is like a pointer to the actual base table row.

      1. sam

        Thanks !!

  14. Amit

    Good explanation about indexes………….Keep posting


  15. Amit

    Good explanation about indexes………….Kepp posting


  16. Shashi

    Hi Admin,

    Thanks once again for your wonderful blogs…I have one doubt.

    Suppose we have a report requires 10 tables to join , say A1,A2…A10.

    and we have created join index as “Join_index_temp” for joining table A1 to A5.

    Now in the report which joins table A1 to A10 how the syntex should be…joining all the 10 tables or joining “Join_index_temp” and remaining A6 to A10. I want to know how the PE will know whether an Join Index has been created which has join among 1st 5 tables.

    Hope my question is clear..


    1. admin

      As soon as you create JI, there is an entry in the DBC.INDICES table (indextype =’j’). If in your reporting query you are selecting those columns defined in JI, then PE will select the rows from JI only. If there is any other column which is not mentioned in your JI then PE will consider the base tables.

  17. Bhawani

    appriciate ur explanation, i always reffer ur blogs

  18. Chetan K.

    I am confused about the Aggregate Join Index query above. I’ve pasted it here again..

    GROUP BY 1;

    Now, why would I sum the salary of an employee who is GROUP’ed BY EMP_NO only? I am assuming that the EMP_NO is unique in the EMP_SAL table. If it is not, we have a problem. I am trying to understand what performance gain will be achieved by building an AGGREGATE JOIN INDEX on the above query. The above query is the same as..



    1. admin

      Hi Chetan,

      Thanks for pointing this.
      Your are right, this query is same what you have mentioned below and no significant performance gain.

      We have changed the example now.


  19. sravani

    I am new to Teradata. From the above expalination i understood like, single table JOIN INDEX can be treated as Secondery index. Please correct if my understanding is not correct…

    1. admin

      Single table JI cannot be treated as SI, because there are lot of differences between these two. The most important one is – SI is like a pointer to the base table row while JI stores the actual row, based on your select query.

  20. Vaishu

    Hey admin.. Your explanations are pretty much clear. Appreciate the effort that you are putting. Gr8 job. Thanks for spending Time:):)

  21. Rajagopal

    Thank You very very much, it’s really really useful…………

  22. Manju

    How does PE gets to know whether a join Index is created for a particular join??

    Suppose I query using the below SQL, then how will PE know that there is a join index EMP_DEPT existing for this join?


    1. admin

      As soon as you create any JI, there is an entry in the system table for it . DBC.INDICES where indextype = ‘J’.

  23. priyabrat

    Hi Admin,
    I have the following doubts:
    1. Suppose i create a join index on a column then how can i use that join index in my stored procedure ,just like i create stats on some columns and later i just write Collect stats on table_name in my procedure.
    2.How the join indexes are getting updated if new records are inserted into the table.
    3.You have mentioned that :
    The reason to create the single table JOIN index is so joins can be performed faster because no redistribution or duplication needs to occur. Can u please explain how ??

  24. Venkadeshwaran

    Hi admin,

    First i would like to say thanks for your great job.

    i have some doubts regarding join index.

    1. Both Join Index and the normal join operations from base table are created by the user only. Then what is the use of creating index and wasting extra PERM space.
    2. How will the PE identify which is faster? is there any criteria for that?

    Thanks in advance.

    1. admin

      your answers goes below –

      1)Suppose for your reporting queries you requires join of 10 dimensional table to fetch data. When this query runs it will make data by joining tables at run time and slows the process. If you made join index for 2-3 tables before the reporting query then optimizer knows that it don’t need to make the data at run time instead of that it picks data from join index itself. So the processing time for join of this 2 tables is eliminated. This is just one example i have given in which join index is used to save execution time. Similarly you can have lot of scenarios in real life projects.

      2) Deciding which approach to choose is the optimizer call. This decision is totally based on getting effective explain plan. If optimizer believes that it can fetch the data by normal join processing, then it’s also possible that it wont go for join index.

      1. Venkadeshwaran

        Thank you.

  25. Aarsh

    Can you please explain partition primary index as well?

  26. Himanshu

    hey admin, thanks a lot for all your blogs. They are very easy to understand…

    Could you please explain that when should we create JOIN INDEX ? It would be great if you could provide a real life scenerio to elaborate…

    1. admin

      JOIN index will make a join between 2 or more tables and store the result in PERM space. Any changes in the parent tables data will be reflected automatically in JOIN index. So if you are confident enough that in your analysis you always required the same join on 2 or more tables, then its advisable to create JOIN index.

      1. iliyas khan

        Hi Admin,

        Please clarify my doubt. As you mentioned –
        “So if you are confident enough that in your analysis you always required the same join on 2 or more tables, then its advisable to create JOIN index.”

        In above we can choose to create a permanent table instead of join index. Why join index is advisable in this scenario ?

        Iliyas Khan

        1. admin

          in permanent table you need to add ETL for inserting data into that permanent table.
          while in JOIN INDEX, data loading takes places internally as soon as base tables data changes.

          1. learner

            hi admin,

            Thanks for such a great work.

            Can you please tell whether secondary index can replace single table join index. If yes what are the advantages of one over other?

  27. leela prasad

    how the system can know that to create any index at the initial stages of table creation

    2. is the join tables should be created by user or system based on queries?

    1. admin

      JOIN Index is created by the USER manually, but it is up to optimizer that he wants to make plan considering JOIN index or actual base tables. It picks the least expensive plan which ever is possible.

  28. Anu

    Thanks a lot…its easy to understand.I am new to Tera data and its very interesting…
    keep posting good articles..
    Thank you…

    1. admin

      welcome 🙂

  29. abhishek

    This explanation is pretty much clear……………………Thanks a LOT…..:)

    1. admin

      thanks …

  30. Anith Babu

    Thats a good and simple explanation!!!
    Thanks a lot 🙂 🙂

  31. Ram

    I came across so many discussions forums and websites to understand join indexes. But I didn’t find them useful to understand. Here Very good explanation…Really helpful to understand… Thanks a lot…

    1. admin

      Thanks Ram …

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>