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