Finding PI for a table created already

Database
Enthusiast

Finding PI for a table created already

Hi All,

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.

Tags (1)
9 REPLIES
SAP
Enthusiast

Re: Finding PI for a table created already

Even , i have 400 tables with same sceanrio . Someone pls help !!!

Enthusiast

Re: Finding PI for a table created already

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.

Supporter

Re: Finding PI for a table created already

The PI has two purposes:

1. access and join support

2. distribution

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!

Teradata Employee

Re: Finding PI for a table created already

What you need to do is collect data demographics and factor in how the table is being queried/used. I've found that in most cases choosing a unique pi is beneficial but this is by no means always the case. I can envison writing an automated tool to give you the most unique combination of columns though I'm not aware of one. 

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.

REPLACE PROCEDURE ALTER_PI_UNIQUE

(

    IN DB VARCHAR(30),

    IN TB VARCHAR(30),

    IN INDEX_COLS VARCHAR(2000),

    OUT ReturnCode INTEGER

)

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Procedure Name        : ALTER_PI_UNIQUE

-- Purpose               : To alter the primary index columns and or whether the index is unique.

-- Version               : V 1.00

-- Create Date           : 2014-04-17

-- Created by            : Fred Newbrough

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Parameters     : DB = Database Name

--                       : TB = Table Name   

--                       : INDEX_COLS = INDEX Columns (can be null) otherwise will change the index to match passed columns.                                   

--                       : ReturnCode  =Indicates run status 0 indicates success.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Change History       

-----------------------------------------------------------------------------------------------------------------------------------------------------------

  --======================   STORED PROCEDURE CODE STARTS HERE

BEGIN

    DECLARE TEMP_TBL_NM VARCHAR(30);

    DECLARE v_sql_stmt VARCHAR(2000);

SET TEMP_TBL_NM=SUBSTRING( TB FROM 1 FOR 19)||(SESSION (FORMAT '9(11)') (CHAR(11)));

SET v_sql_stmt ='CREATE TABLE '|| TRIM(DB) || '.' || TEMP_TBL_NM || ' AS '|| TRIM(DB) ||'.'|| TRIM(TB)||'  WITH NO DATA;';

CALL DBC.SYSEXECSQL(v_sql_stmt);

 IF COALESCE(INDEX_COLS,'') = '' THEN

 SET v_sql_stmt ='ALTER TABLE '|| TRIM(DB)||'.'||TEMP_TBL_NM||' MODIFY UNIQUE PRIMARY INDEX ;';

ELSE 

 SET v_sql_stmt ='ALTER TABLE '|| TRIM(DB)||'.'||TEMP_TBL_NM||' MODIFY UNIQUE PRIMARY INDEX ('||TRIM(INDEX_COLS)||');';

END IF;

CALL DBC.SYSEXECSQL(v_sql_stmt);

SET v_sql_stmt ='INSERT INTO '|| TRIM(DB)||'.'||TEMP_TBL_NM||' SELECT * FROM '|| TRIM(DB)||'.'||TRIM(TB)||';';

CALL DBC.SYSEXECSQL(v_sql_stmt);

SET v_sql_stmt ='DROP TABLE '|| TRIM(DB)||'.'||TRIM(TB)||';';

CALL DBC.SYSEXECSQL(v_sql_stmt);

SET v_sql_stmt ='RENAME TABLE '||TRIM(DB)||'.'||TEMP_TBL_NM||' TO '|| TRIM(DB)||'.'||TRIM(TB)||';';

CALL DBC.SYSEXECSQL(v_sql_stmt);

     SET ReturnCode = 0;

END

;

Enthusiast

Re: Finding PI for a table created already

Thank you Fred :) and thank you all for your valuable suggestion :)

Teradata Employee

Re: Finding PI for a table created already

Welcome Sarah!

Enthusiast

Re: Finding PI for a table created already

Hi,

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?

thanks,

Enthusiast

Re: Finding PI for a table created already

I did not test the above proc. 3 in parms ,1 out parm:

CALL ALTER_PI_UNIQUE ('DS_DAT','PROD_PARTY','PRDCT_ID',parm5678);

Enthusiast

Re: Finding PI for a table created already

Thanks Raja..