Difference between Application RI,Batch RI,explicit & standard RI?

Database
Enthusiast

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

Tags (1)
6 REPLIES
Teradata Employee

Re: Difference between Application RI,Batch RI,explicit & standard RI?

  • 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)
Enthusiast

Re: Difference between Application RI,Batch RI,explicit & standard RI?

Thanks Fred for your response!.

Can you please explain about explicit and application RI as well?

cheers!

Nishant

Teradata Employee

Re: Difference between Application RI,Batch RI,explicit & standard RI?

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.

Enthusiast

Re: Difference between Application RI,Batch RI,explicit & standard RI?

thanks Fred !

Enthusiast

Re: Difference between Application RI,Batch RI,explicit & standard RI?

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

Enthusiast

Re: Difference between Application RI,Batch RI,explicit & standard RI?

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) ?.