I need some general guidance on choosing a new PI to make table A more easily accessible in joins.
If my join looks like this:
LEFT JOIN A
ON A.INVC_ID = B.INVC_ID
AND A.DTA_SRC_ID = B.DTA_SRC_ID
AND (C.DW_LD_TS BETWEEN A.EFF_STRT_TS AND AI.EFF_END_TS)
AND C.DTA_SRC_ID = A.DTA_SRC_ID
…and I’m examining the PI for table A to determine if I can redefine it to be used for access, am I looking at whether all 4 distinct table A columns are in the PI, or is it different for each table listed in the join section (B and C)? The current PI for table A is INVC_ID, and was originally selected by the Teradata consultant that originally defined the table.
I'm just now getting to the point that I fully understand that we have a lot of work that we need to do to redefine indexes, and that we need to do some partitioning, but I'm struggling with how to make the decision on the columns to include.
Thanks very much in advance.
I do not think this would be the only query that runs on this table and always that this join will be there in all the associated queries. if otherwise, then that should not be a bad idea to add all the 4 columns to the PI. However if you try to join with other tables on other columns, it would result in a redistribution of the table. All the columns should be mentioned in joining this table to any other table if all 4 are made as the PI combination to avoid redistribution.
if you consider this join to be persistent and used very frequently in all the queries, then JOIN INDEX is one thing which you can think of.
As a general rule, the combination of columns which are most frequently involved in a query's where clause and those which are involved in a join would be ideal candidates for PI. Also the skew of the table needs to be considered when deciding the PI as this would impact the distribution of the table and a heavily skewed table results in a bad performance. Also it would be ideal to avoid updates on PI candidates.
Partitioning helps with Deletes, updates or selects based on the partition columns. Also includingas a PPI or CPPI needs to be analysed on a case to case basis.
Thanks for your response.
Many of our tables are only joined one way, but some are joined in multiple ways - for those, we're thinking of creating bridge tables to help keep the joins as consistent as possible...not a final decision yet, though. The vast majority of our joins are structured just as the example appears.
We're still working to decide which approaches would best suit us.
I will do some further testing and post back soon.