I have tables which are already created in DB. Almost half the tables are having more skewness. Now, I need to change PI for those tables which are having high skewness. Instead of checking each and every combination of columns manually, is there any way to check them at a stretch? I have nearly 200 tables.
Any help would be appreciated. Thanks in advance.
It seems you are aware of the query to find skewness, so I will not re-iterate it.
How skewed is your data?
I m not sure if there is any tool that gives those details. However, if it is me, I will write an automation script based on the query HASHAMP ...HASHBUCKET...HASHROW on IDs(hoping that data modeler has toed the line of proper naming convention :)), reading for each column and or combination of columns.
From performance point of view, maybe you can see TD Index wizard, because you may think of partitioning, STJI, SI but not PI.
The PI has two purposes:
1. access and join support
A good PI will serve BOTH purposes and using a automated procedure which focus on distribution will often not result in best perfromance.
PI selection is one mayor part of the physical data modeling!
I did write a simple stored proc to make it easier to alter the primary index columns and modify the index to be unique when I had a situation where I needed make a mass change of PIs to tables. I determined the correct unique PIs and then used a spreadsheet to generate the procedure calls.
I am calling the below procedur but i am getting error as 'CALL failed. 3812: The positional Assignment list has too few values'
CALL ALTER_PI_UNIQUE ('DS_DAT','PROD_PARTY','PRDCT_ID');
Please correct me incsae am calling a wrong procedure?
I did not test the above proc. 3 in parms ,1 out parm:
CALL ALTER_PI_UNIQUE ('DS_DAT','PROD_PARTY','PRDCT_ID',parm5678);