Multi Table Join Index

Database
Enthusiast

Multi Table Join Index

Scenario:

  • Teradata 13.0 with 13.10 upgrade coming in the next couple of months.
  • MicroStrategy report that hits approximately 10 tables in Teradata.
  • Most expensive joins are between a table showing customers and a table containing details about which services/products these customers have both currently and prior to the changes displayed in the row in the customer table.
  • Need to join from the customers table to teh services table twice, once for current package, and once for prior package services.
  • The initial join for current services usually occurs first, and the join for prior services usually occurs 4 or more joins later in the explain plan.
  • The explain indicates the services table is being duplicated across all amps which is eating up IO etc.
  • There is no opportunity to align the Primary Indexes on these two tables.

Current State:

  • I have created two Multi Table Global Join Indexes with a PPI the same as the customers table.  The single level partition is on a date surrogate key.  This index contains the ROWID for both the customers table and the services table.
  • One index joins customers to services for current package, and the other index joins customers to previous package data.  (I can't wait until Multi Table Join Indexes support self-joins etc.)
  • The indexes also contain some surrogate keys from tables other than customer and services tables.  Index has a total column count of 6 including the ROWIDs mentioned above.

Issue:

  • The join index works wonderfully for the first join from the customer table to the services table for current package info, but I cannot get the optimizer to use the other join index when joining customers to services for the previous package.

Any help or ideas are greatly appreciated.

Thanks

1 REPLY
Enthusiast

Re: Multi Table Join Index

Bump.....

Anyone?