Except for the requirements on the join condition between the tables in the LTS we may choose outer join, but default join condition between two tables in the LTS are joined as INNER JOIN, and we prefer Inner Join.
You can think of the tables in a logical dimension table source as being like a database view. When it formulates physical SQL, Oracle BI Server will leave out the tables in this “view” that are not needed to satisfy the logical query (join elimination), but only if the join type is Inner. When the join type is Outer, however, Oracle BI Server will always include the tables.
So for the better performance we always use INNER JOIN.
Btw dont believe on my words... Just my analysis...
(Reference: Oracle BI Suite EE 10g R3 - Activity Guide)
Good post. Please post some examples with queries also. I think Outer joins is used when there is functional need for this otherwise outer joins are not needed. Outer joins are always prone to performance issue as outer joins may impact execution plan generation. In warehouse we always prefer to avoid outer joins by using some default rows in tables and use the same to eliminate the outer joins(by populating some user defined values for null which corresponds to default row in table where values are missing).
ReplyDelete