As heavily skewed table decreases performance, planning to recreate skewed tables automatically with a new Primary index. Plan is to have an automated script which would fetch all the skewed tables (> 40%) owned by my team and analyze on the best index and recreate the table.
Difficult part seems to be automatically predicting suitable primary index. [Like, in case of summary tables - have to neglect metric columns from index and only choose the dimensions].
Hoping some of you would have faced similar situation before. Kindly help / share info on script to automatically choose PI for a table (interms of distribution & not having to add all the columns as PI).
i doubt a script like that exists. Choosing a PI without knowledge about the data plus join and access paths will not automatically result in better performance.
You should start doing it manually with the largest skewed tables first.
Btw, what's your definition of "skewed", > 40% is a quite large number.
Thanks for the inputs. Yeah, finally ended up tracking the skewed tables and recreated them (with manually chosen PI).