Automatic script to change PI in case of high Skew

Database
Fan

Automatic script to change PI in case of high Skew

Hi,

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

Regards,

Saran

2 REPLIES
Senior Apprentice

Re: Automatic script to change PI in case of high Skew

Hi Saran, 

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.

Dieter

Fan

Re: Automatic script to change PI in case of high Skew

Hi Dnoeth,

  Thanks for the inputs. Yeah, finally ended up tracking the skewed tables and recreated them (with manually chosen PI).

Regards,

Saran