All Forums Database
14 Jul 2013
Difference between Application RI,Batch RI,explicit & standard RI?

Hi Experts,

 

As we have various types of RI in teradata like Soft RI,application RI ,Batch RI,Explicit RI, Standard RI..

 

I know that  Soft RI  allows the Optimizer to utilize join elimination in the generated

 

plan with out enforcement performed implicitly during the execution of the insert, delete, or update..

 

On the  other hand ,other  RI's  allows the Optimzer to utilize the Join elimination with enforcemenit performed explicilty during Insert/Update & Deletes.

 

I am not sure what are the difference between Application RI,Batch RI,explicit & standard RI as they all fall in the same category.

 

Can any one please elaborate more on this?

 

Cheers!

 

Nishant

 

 

Fred 1096 posts Joined 08/04
16 Jul 2013
  • Standard - checked for each individual SQL request
  • Batch - checking postponed until transaction COMMIT time
  • Soft - not enforced by the DBMS (in which it should be Application enforced)
17 Jul 2013

Thanks Fred for your response!.
Can you please explain about explicit and application RI as well?
 
cheers!
Nishant

Fred 1096 posts Joined 08/04
17 Jul 2013

Explicit just means the RI constraints are declared to the database. So Standard, Batch, Soft are all explicit.
The Application can also enforce RI without explicitly declaring (Soft) constraints.

17 Jul 2013

thanks Fred !

Raja_KT 1246 posts Joined 07/09
17 Jul 2013

Hard RI is when we implement references ( say emp vs dept tables) where deptno is referenced in emp table to dept table explicitly. However, when we comment the code in DDL about this same referencing, then it becomes  soft RI. So, the optimizer does the join elimination.
I am curious about the cost-effectiveness in terms of throughput and response time if I have hundreds of those queries. As per Fred's mail I see that batch soft RI is the best option for good throughput.
Thanks,
Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

gopinadhs 18 posts Joined 10/11
08 Aug 2014

Fred,
How does Application can also enforce RI without explicitly declaring (Soft) constraints ?.
Here is my situation, and i have implemented SOFT RI on database side, and all of suddently my lookups, and inserts running longer time(hours) than expected. I found this is because of Soft RI on 4 tables, and it is internally checking across all tables.
How do i define a Soft RI on application side(BTEQ) ?.
 

You must sign in to leave a comment.