In order to understand PI choice , one criteria is to know the frequency of column access in joins and in WHERE clause. Iam looking out for a script which can give a historical column usage for a table, i.e. for ex. in last 1 month how many times a particular column is used in joins and where condition while the table was acceessed? This can be used to validate the PI choice and also knowing other PI column candidates which might be having greater usage compared to PI. Kindly suggest if anyone is using a script or know a solution.
You will have to enable DBQL logging at the 'Object' level on your database. Once that's done, you can query both DBQLogTbl and DBQLObjTbl to look into different statistics against your columns over a period of time. Look for ObjectTableName and ObjectColumnName in DBQLObjTbl which has the ObjectType equalling 'C' (for column) and join it's QueryID with QueryID in DBQLogTbl.
And if you want to enable DBQL for this purpose only then you can set SQL limit to '0'. Let me know if you want more help on this or on enabling DBQL logging.