Storage Impact for PPI vs NPPI tables

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
KN
Enthusiast

Storage Impact for PPI vs NPPI tables

Gurus,

 

What is the storage impact if i convert a NPPI table with Index on a specific column to PPI table?

Instead of a index that column would be a partioned column..

The column is a date column and has a huge data for the last 20 years until today? on top of it i would be reviewing the compression too.

 

i am planning to test it tomorrow but like to know your opinions and observations.

 

thanks

KN

 

1 REPLY 1
Ambassador

Re: Storage Impact for PPI vs NPPI tables

Partitioning adds either 2 (< 64k partitions) or 8 bytes (> 64k) to each row/index.

 

But in your case (replacing an index on date with PPI) you will save space:

As there's only a small number of possibe values the size of the existing index will be slightly over 8 bytes/row. With a date-PPI you get 2 byte partition number, thus approx. 8-2=6 bytes less per row. If you're on a system with BLC compressibilty should be better, too.

 

Don't forget to create that table with a RANGE_N far into the future (e.g. 2035-12-31), so you never have to add partitions.