Compatibility: Teradata Extension
A Join Index is an index table that pre-joins the joined rows of two or more tables and, optionally, aggregates selected columns. They are used to speed up queries that frequently join certain tables. Teradata join indexes can be defined as hash-ordered or value-ordered. Join indexes are defined in a way that allows join queries to be resolved without accessing or joining their underlying base tables.
A Join Index takes two or more tables and physically joins the tables together into another physical index table. It also updates the Join Index table when the base rows of the joining base tables are updated. Join indexes are a great way to aggregate columns from tables with a large range of values. A Join Index can play the role of a summary table without denormalizing the logical design of the database and without causing update anomalies presented by denormalized tables. This actually gives you the ability to keep your detail data in 3rd normal form and your summary tables in a star schema format. Brilliant Teradata!
The syntax for a JOIN INDEX is:
In release V2R4, you can have upto 16 columns in a Join Index. Starting with release V2R5, the number of columns has been increased to 64.
Here is an example of creating a Join Index between the Employee table and the Department table:
In the example above a JOIN INDEX called emp_dept_idx has been created on the tables Employee and Department. The rows from the two tables have been physically joined together and will be maintained when rows in the Employee or Department tables change because of INSERTS, UDATES, or DELETES. When users run queries they don’t specify or mention the JOIN INDEX table. Instead, when they run queries that can be satisfied by the JOIN INDEX table faster the Teradata database will choose to pull the data from the JOIN INDEX table instead of the base tables. When SQL is run and the columns asked for are any combination of the above columns defined in the JOIN INDEX then Teradata may choose to use the JOIN INDEX instead of the actual base tables. This is called a covering query. You can also explicitly define a Primary Index for a JOIN INDEX. Teradata spreads the rows of the join index across the AMPs. The AMPs read and write the rows in parallel. So, utilizing a good Primary Index can be important.
It is also becoming a common practice to use a create a single-table join index. This allows the columns that referenced by join SELECT to be distributed on a PI that is different than the real table. It does not eliminate the need to join the tables, but it does eliminate the requirement to redistribute of data in SPOOL to do a join with another table.
You can also drop a JOIN INDEX. Here is an example:
DROP JOIN INDEX TOMC.JOINIDX_2;