Thanks Fred for your response!.
Can you please explain about explicit and application RI as well?
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.
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.
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) ?.