Unnecessary IO and Product Join Indicators

Question

what are Unnecessary-IO and Product Join Indicators in Teradata. How are these metrics determined for a query?

Answer 1

Product Join Indicator is the ratio of CPU Seconds to IO for a query. Generally when the PJI is greater than 3 the query should be reviewed. Greater than 6 you may find the query is performing an unnecessary product join. To calculate the PJI using DBQL metrics: (AMPCPUTime * 1000) / TotalIOCount

Unnecessary IO Indicator is the ratio of IO to CPU Seconds. If the UII is greater than 3 the query should be reviewed to eliminate full-table scans and possibly redistribution steps. UII is a reasonable indicator to identify queries that may benefit from additional statistics to indexing improvements. To calculate the UII using DBQL metrics: TotalIOCount / (AMPCPUTime * 1000)