About Physical Joins

Physical joins indicate relationships between physical tables and tell the Oracle Analytics query engine how to retrieve data from the tables.

Creating a physical join automatically creates a join key for the identifier column and adds it to the primary table's properties. To view a table's join keys, open the physical table and then go to the General tab. You can view join keys, but not edit them. To find more information about a join, open the Physical Diagram and click a join to find out the tables it's joined to, join cardinality, join type, join condition, and so on.

You must explicitly define joins in each data source or between data sources to express relationships between tables in the physical layer. You can create a join can based on a join condition, or on an expression that you provide. For most data sources, join conditions are preferred for performance reasons. Joins based on expressions usually don't perform as well because they don't use key column relationships to form the join.

Joins that are defined to enforce referential integrity constraints can result in specifying incorrect joins in queries. For example, joins between a multipurpose lookup table and several other tables can result in unnecessary or invalid circular joins in the SQL queries issued by the Oracle Analytics query engine.

You can define a join from one metadata database object to another metadata database object. This is called a multi-database join.

While the Oracle Analytics query engine has several strategies for optimizing the performance of multi-database joins, these joins are significantly slower than joins between tables within the same database. As a result of the negative performance impact, you should avoid using multi-database joins whenever possible.