Soft RI


Soft RI

I am considering implementing Soft RI so as to gain the Optimizer benefits that are documented. Are there any restriction or drawbacks that anyone is aware?
Thank you.

Re: Soft RI


Just how soft? Remeber your data should already be in the 2nd Normal form.

There are no draw back to going higher and higher accept:
-- That you might cause redundancy
-- Multiple table joins to retrieve you data.
-- Multiple Table maintenace
-- Loading of data

So I suggest taking one step at a time (one NF at a time), chose very good PI and SI if necessary. Also do not forget JOJN indexes and Partitioning.

We have found in our environment that 3NF is about the most acceptable but that you will have some tables in 4NF or 2NF because of the above problems.


Re: Soft RI

Draw backs that I am awar of:

1) Need to drop constraint for fastload and multiload (same as regular RI).
2) You need to enforce the RI with code or quality checks (same as No RI).

If you fail to do #2, then some of your queries will give the wrong results based on the underlying data.

For example, if you have a parent table and a child table and there are records in the child table for which there is no parent, the following query gies the wrong results:

select count(*)
from child inner join parent

This will return the toal number of rows in child even if there are no rows in parent. This is becuase the optimizer assumes there is a row in parent for every row in child and since it has no need to access parent (no column selected), it doesn't actually do the join.


Re: Soft RI

I was inquiring about soft RIs and found above notes.
Can some one shed more light on soft RIs ? If data integrity can be guaranteed by ETL ,then soft RI is an impressive feature (even compared to complex join indices ) for performance improvement as it results in join elimination.
Why then ,this feature is not used widely within Teradata practice?
Teradata Employee

Re: Soft RI


As of restriction, it depends how you plan to implement it. For drawbacks, it include more effort and more CPU time for processing the same data-set. Nothing much I believe.

Regards, MAC
Senior Apprentice

Re: Soft RI

Hi Adeel,
what do you mean by "more effort and more CPU time for processing the same data-set"?
Soft RI is a dummy, which is not checked by the DBMS.

you answered your own question:
"If data integrity can be guaranteed by ETL "

If there is faulty data the result set will be wrong, if the optimizer eliminates a join.

And you need a to reference a USI/UPI column, which is sometimes not possible, because you didn't implement the USI or you have a temporal table with valid_from/valid_to and then it's not possible with a traditional FK.
In TD13 this restriction "referencing a unique column" is removed, now you got a dummy FK and a dummy PK :-)

And TD13.10 implements temporal tables.


Re: Soft RI

Thanks always ..u rock...

I have few additional point to share besides ETL check..These are from DBA perspective..

1. REFERENCES access right( which is used for soft RI implementation) is granted automatically to the CREATOR of object and implicitly to the OWNER of the object.
This means that in a Teradata practice where DBAs use their individual id (rather than common id like SYSDBA etc) end up as the only person who can implement soft RI on tables created by his/her id . Further alter table wont be possible by another team member unless that table is dropped and recreated by another team member who in turn becomes the creator of the table then. In theory , this privilege will always be held by 1 DBA( the creator) rather than all.
This is one such hassle in maintaining soft RI.

2. REFERENCES access right is granted on table level rather than database level. Hence more difficult to maintain with additional number of entries in access rights tables.

Hope it'll help to anyone considering +/- of soft RI

Re: Soft RI

Thanks Dieter & Karam. This helped me today.