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.
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.
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.