PPI table having a column for same PI and SI

Data Modeling
Enthusiast

PPI table having a column for same PI and SI

Hi,

Please suggest this design if I define PI and USI for the same column. My date range partition for now.

Thanks and regards,

Raja

8 REPLIES
Enthusiast

Re: PPI table having a column for same PI and SI

Hi,

If the column is PI, then what do you need SI? 

Khurram
Teradata Employee

Re: PPI table having a column for same PI and SI

I'm assuming the purpose of the USI is simply to enforce the PK, otherwise I can think of no other reason to include the PI in a secondary index as the optimizer should always choose to utilize the PI for access over the USI. 

And in rereading your question, are you implying that the USI would be a single column USI with the same column as the NUPI?  Teradata will not permit you to implement multiple indexes with the exact same column(s).  The USI can include the PI column(s), but would need to have additional columns or it would be a duplicate index.

Enthusiast

Re: PPI table having a column for same PI and SI

Hi,

Of course, I mean NUPI and USI. I think from the standpoint of distribution and demography cases.

Thanks and regards,

Raja

Teradata Employee

Re: PPI table having a column for same PI and SI

What I still do not understand is are you asking about implementing identical NUPI and USI indexes, or using the NUPI column(s) in a USI with additional columns? 

Let me create an example.  Say a table has 5 columns: ColumnA, ColumnB, ColumnC, ColumnD, ColumnE.   ColumnA and ColumnB comprise the PK, a NUPI is defined on ColumnA, and ColumnC is the partitioning date column.  Do you want to build a NUSI on ColumnA alone or on (ColumnA, ColumnB)?  Teradata will not allow you to build a Secondary Index of any type on ColumnA alone since an index (the NUPI) already exists using just that column.  You could build a USI (or a NUSI) on the combination of ColumnA and ColumnB, but I cannot think of a reason why that would benefit you except in the case of using Teradata to physically enforce the PK.  The USI index will likely never be used for access since the NUPI access is single AMP while USI access is two AMP, first going to Secondary Index subtable and then to the table row.  Perhaps in cases where a very large number of rows exist with the same PI and the optimizer believes going to the USI subtable first will be quicker than scanning all the rows with just the PI the USI will be used, but I see that as an exception and no something I'd point to as typical.  Row distribution is determined solely by the PI, so declaring a secondary index will not impact it in any way. 

Perhaps if you explain precisely what it is that you are trying to do I can better job at answering your question?  As it stands, I can think of only a small handful of exception cases where I would build a USI that includes the PI column(s).

Junior Contributor

Re: PPI table having a column for same PI and SI

You can define a SI on top of the PI if the table is partitioned :-)

In fact it's even recommended when there are lots of partitions and the partitioning column is not part of the PI and you need to access using the PI columns only.

E.g. the PK (ColumnA, ColumnB) can't be created as a UPI if the table is partitioned by (columnC). A WHERE condition on the PK will result in a single-AMP step, but as it's unknown in which partition the row is located all partitions must be checked ("single AMP retrieve step using all partitions" in explain).

Similar for a NUSI on NUPI: lots of partitions, but a low number of rows per value.

In that case both USI and NUSI access will result in a single AMP-access as the optimizer knows it's an AMP-local index.

Dieter

Enthusiast

Re: PPI table having a column for same PI and SI

Hi Dieter,

Thanks a lot. Yes , I did mention about partition in my original posting "  My date range partition for now" . So I will go ahead with my thought.

Thanks and regards,

Raja

Teradata Employee

Re: PPI table having a column for same PI and SI

Good to know, Dieter.  For some reason when I tried that scenario on my version of Teradata Express 13.1 it would not allow me to build the duplicate secondary (I always like to fact check before posting). 

Enthusiast

Re: PPI table having a column for same PI and SI

Hi,

It makes sense also for some business cases to have this feature (PPI+PI+SI)

To be very honest, I find that it is always a  learning. Dieter is always a man. I dont know elsewhere was a person by that name, when I used to work in SAP DS 3 years back, I posted many questions. He was always there. Is that you Dieter in SAP DS too?

Thanks and regards,

Raja