No Primary Index and HW expansions

Database
Supporter

No Primary Index and HW expansions

Hi,

a client is going to add new nodes to the system.

CS run pre-checks and indicated that the NO PI tables will not redistributed during the HW expansion.

This can / will result in skew issues.

The proposed workaround is to

1. create a PI table and copy the no pi table data into this

2. do the HW expansion - incl. the PI table redistribution

3. drop the no pi table

4. create the no pi table

5. copy the data from the pi table to the no pi table

6. collect stats on the no pi table if existed before

 

The issue is that dropping and creating the NO PI table will also require to manage all table access rights again...

 

Why is the dropping of the no PI table needed?

 

Without HW expansition

the process of

1. create a PI table and copy the no pi table data into this

2. delete all data from the no pi table

3. copy the data from the pi table to the no pi table

is redistributing the data via the PI to all vprocs.

 

So I guess there are other issues to consider regarding NO PI tables and HW expansions?

 

Ulrich


 

 

6 REPLIES
Senior Supporter

Re: No Primary Index and HW expansions

Hi Ulrich,

I certainly wouldn't drop/create the NOPI table for exactly the reason that you've raised. Any non-default AccessRights on that NOPI table will have to be re-created. I would go with the delete and then re-populate.

 

I think the better process is:

1. create and populate a PI table

2. empty the NOPI table (this prevents any unexpected 'database full' errors during the reconfig

3. add hardware / reconfig etc etc

4. copy all data from PI table (created in #1) to your NOPI table

5. check all is ok

6. drop the PI table

 

In your original post you said (right at the bottom) "3. copy the data from the pi table to the no pi table

is redistributing the data via the PI to all vprocs"

 

If your sql to copy the data from the PI into the NOPI table is a simple insert/select, no joins, no aggregation etc. then this will be an amp-local copy, there will not be any re-distribution at this stage.

 

Does that help?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Supporter

Re: No Primary Index and HW expansions

Hi Dave,

 

thanks for confirming my thinking!

yes, my wording was not good on step 3 - the redistribution is done during the NO PI -> PI table copy and the PI -> NO PI copy is vproc local.

 

The challenge we face is that the TD statement states explicitly drop and create the NO PI table. And we got the message from TD that if you don't follow the official process you do it at your own risk.
I also heard from another customer that there had been issues with NO PIs and HW expansions in the past - related to collect stats which had been wrong even after recollection. Solution was to drop and re-create them...

 

What is a bit of unpleasant is that the status of the issue is very unclear. Was this only a problem of the past or does this issue still exists...

So I would appreciate if anybody could explain why the official statement states drop and create.

 

Ulrich

Senior Apprentice

Re: No Primary Index and HW expansions

Hi Ulrich,

I can't imagine any reason why the NoPI table should be recreated instead of a simple DELETE ALL, afaik there's no additional information stored for NoPI. In both cases it's empty and the distribution during Inserts is based on the new configuration.

 

Maybe you should inquire CS why they think it's neccessary :-)

 

Dieter

Tags (1)
Supporter

Re: No Primary Index and HW expansions

Hi Dieter,

 

let's assume this question was send to CS and I we didn't got an answer within the last week ;-)...

 

Ulrich

Senior Supporter

Re: No Primary Index and HW expansions

Hi Ulrich,

Why this is the 'official policy' only Teradata can tell you.

If you haven't had an answer after a week I'd start chasing them.

Regards,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: No Primary Index and HW expansions

I would expect that after emptying & repopulating the NoPI table, using FOR CURRENT modifier or (worst case)  DROP STATS / COLLECT STATS should suffice.