Join Elimination by SI


Join Elimination by SI

Hi all,
How does the Soft Referential Integrity eliminate Joins?

Thanks in advanced,

Re: Join Elimination by SI

If you have a soft RI relationship set up between two tables and you code a join in SQL between the two tables, the optimizer will only go to the table that contains the foreign key if it needs to go to it to get a column. It will not go to it to simply carry out the join.

Here is an example where I might use this:

I have two code tables and those codes are used in a bigger fact table. To help with the implementation of a tool or to make things easier for the user, I create a view that inner joins all three tables (the two code tables and the fact table). If a user accesses that view when you are not using soft RI, all three tables must be accessed to solve the query (even if the query is only interested in columns from the fact table). However, if a user accesses the view when you are using soft RI, the optimizer will not go to the 2 code tables unless the query is actually getting some data from them. It will assume that the foreign key specified in the fact table does exist in the code table.