Soft Referential Integrityn - Performance

Database
Fan

Soft Referential Integrityn - Performance

Hi -

 

Soft RI is created on a fact table which joins with dimensions. When full data is accessed from fact table soft RI is used by the optimizer (observed in the explain plan) and the response time is effective. Where as, When any filter condition is applied on fact table RI is not observed in explain plan and the responce time is more.

 

Is this behaviour expected as per soft RI limitation ?