Work with Logical Joins

This topic provides information about how to create and modify logical joins.

Topics:

• About Logical Joins

• What Are Driving Tables?

• What Determines Join Trimming?

• Add and Define Logical Joins

• Identify the Physical Tables That Map to Logical Tables

About Logical Joins

Logical joins define relationships between logical tables.

Logical joins are conceptual joins and not physical joins. Logical joins don't join to specific keys or columns. A single logical join can correspond to many possible physical joins.

A key property of a logical join is cardinality. Cardinality expresses how rows in one table are related to rows in the table that it's joined to. A one-to-many cardinality means that for every row in the first logical dimension table there are 0, 1, or many rows in the second logical table. Semantic Modeler considers a table to be a logical fact table if it's at the Many end of all logical joins that connect it to other logical tables.

Specifying the logical table joins is required so that the Oracle Analytics query engine can have the necessary metadata to translate a logical request against the business model to SQL queries against the data sources. The logical join information provides the Oracle Analytics query engine with the many‐to‐one relationships between the logical tables. This logical join information is used when the Oracle Analytics query engine generates queries against the underlying data source.

You don't need to create logical joins in the logical layer if both of the following statements are true:

• You create the logical tables by dragging and dropping all required physical tables to the logical layer.

• The logical joins are the same as the joins in the physical layer.

You might need to create some logical joins in the logical layer because you can't drag and drop all physical tables simultaneously except in very simple models.

You use the logical diagram to create joins. When you create a join expression in the physical layer, you can specify expressions and the specific columns on which to create the join. When you create a logical join in the logical layer, you can't specify expressions or columns to create joins on. A join in the physical layer doesn't require a matching join in the logical layer.

What Are Driving Tables?

Driving tables optimize how the Oracle Analytics query engine processes cross-database joins when one table is very small and the other table is very large.

Specifying driving tables leads to query optimization in cases where the number of rows being selected from the driving table is much smaller than the number of rows in the table that it's joined to.

When you specify a driving table, the Oracle Analytics query engine uses the driving table if the query plan determines that the table’s use can optimize query processing. The small table (the driving table) is scanned, and parameterized queries are issued to the large table to select matching rows. The other tables, including other driving tables, are then joined together.

You can use driving tables with inner joins, and for outer joins when the driving table is the left table for a left outer join, or the right table for a right outer join. Driving tables aren't used for full outer joins.

Note the following information when deciding to set a driving table:

• Specify a driving table when the driving table is extremely small (less than 1000 rows).

• Specify a driving table only when multi-database joins are going to occur.

• If large numbers of rows are being selected from the driving table, specifying a driving table could lead to significant performance degradation or, if the MAX_QUERIES_PER_DRIVE_JOIN limit is exceeded the query terminates.

There are two entries in the database features table that control and tune driving table performance:

• MAX_PARAMETERS_PER_DRIVE_JOIN

  This is a performance tuning parameter. The larger its value, the fewer parameterized queries are generated. Values that are too large can result in parameterized queries that fail due to back-end database limitations. Setting the value to 0 (zero) turns off drive table joins.

• MAX_QUERIES_PER_DRIVE_JOIN

  This is used to prevent runaway drive table joins. If the number of parameterized queries exceeds its value, then the query is terminated and an error message is returned to the user.

What Determines Join Trimming?

This topic describes how the Oracle Analytics query engine determines which joins it can trim from a physical query.

These are the join trimming rules for tables within a logical table source:

• Join Outerness (Inner, Left Outer, Right Outer, or Full Outer).

• Join Cardinality (There are nine join cardinality combinations excluding those with Unknown cardinality on at least one side of the join.).

• Whether the logical table source contains a WHERE clause filter.

• Whether the physical join is a join or join expression.

The Oracle Analytics query engine uses the following criteria to trim a join:

• No references to the trimmed table can exists anywhere in the query such as in the projected list of columns or in the WHERE clause.

• The trimmed table must not cause the cardinality of the result set to change. If removing a join could potentially change the number of rows selected, then the Oracle Analytics query engine doesn't trim it.

  A join is considered to have the potential to change the number of rows in the result set if any of the following conditions are true. If any of these conditions are true, then the join isn't trimmed from the query:

  • The join is a full outer join, only inner joins, left outer joins, and right outer joins are candidates for trimming

  • The join cardinality is unknown on either side

  • The table to trim is on the many side of a join, in other words, the detail table is never trimmed in a primary-detail relationship

  • The table to trim has a 0..1 cardinality and the join is an inner join. 0..1 cardinality implies that a possible matching row in the table. A join with 0..1 cardinality on one side is effectively like a filter. The Oracle Analytics query engine can't trim the table without changing the number of rows selected.

  • The table to trim is on the left side of a left outer join or on the right side of a right outer join, the row-preserving table is never trimmed. There is an exception to this rule for queries that select only attributes in which a DISTINCT clause is added to the query. Because of the DISTINCT clause, trimming the row-preserving table doesn't affect the number of rows returned from the null-supplying table. In the special case of distinct queries on attributes, you can trim the row-preserving table from an outer join.

The following table provides examples of when the Oracle Analytics query engine trims joins from the query.

Scenario	Result
Employee INNER JOIN Department	The Oracle Analytics query engine can trim Department because it's on the one side of an inner join. The Oracle Analytics query engine can't trim Employee because it's on the many side of an inner join.
Employee LEFT OUTER JOIN Department	The Oracle Analytics query engine can trim Department because it's on the one side of the join and it's on the right side of a LEFT OUTER JOIN, the null supplying table. The Oracle Analytics query engine can't trim Employee because it's on the many side, and because it's on the left side of a LEFT OUTER JOIN, the row preserving table.
Employee RIGHT OUTER JOIN Department	The Oracle Analytics query engine can't trim Department because it's on the right side of a RIGHT OUTER JOIN, the row preserving table. The Oracle Analytics query engine can't trim Employee because it's on the many side of the join.
Employee INNER JOIN EmployeeInfo	The Oracle Analytics query engine can trim either side because both tables are on the one side of an inner join.
Employee LEFT OUTER JOIN EmployeeInfo	The Oracle Analytics query engine can trim EmployeeInfo since it's on the one side of the join, and it's on the right side of a LEFT OUTER JOIN, the null supplying table. The Oracle Analytics query engine can't trim Employee because it's on the left side of a LEFT OUTER JOIN, the row preserving table.
Employee RIGHT OUTER JOIN EmployeeInfo	The Oracle Analytics query engine can trim EmployeeInfo because it's on the right side of a RIGHT OUTER JOIN, the row preserving table. You can trim Employee because it's on the "one" side of the join, and it's on the left side of a RIGHT OUTER JOIN, the null supplying table.
Employee INNER JOIN Department	The Oracle Analytics query engine can trim Department because it's on the 0..1 side of an inner join. The Oracle Analytics query engine can trim Employee because it's on the many side of an inner join.
Employee LEFT OUTER JOIN Department	The Oracle Analytics query engine can trim Department because it's on the 0..1 side of an outer join, and it's on the right side of a LEFT OUTER JOIN, the null supplying table. The Oracle Analytics query engine allows trimming the null supplying table on the 0..1 side of an outer join, because in this case, trimming Department from the query wouldn't change the number of rows selected from the Employee table. The Oracle Analytics query engine can trim Employee since it's on the many side of an outer join.
Employee FULL OUTER JOIN Department	The Oracle Analytics query engine can't trim either side because the join is a FULL OUTER JOIN.
Employee MANY TO MANY Project	The Oracle Analytics query engine can't trim either side because the join is many-to-many.
Employee UNKNOWN Department	The Oracle Analytics query engine can't trim either side because the join has unknown cardinality.

Add and Define Logical Joins

You use the Logical Diagram to add and define joins between logical tables.

When you drag multiple tables from the physical layer to the logical layer, corresponding logical tables and logical columns are created, and a logical join is automatically created for each physical join. You can modify these joins as needed, or add new joins.

See About Logical Joins.

Note:

Use caution when specifying a driving table. Driving tables are used for query optimization only under rare circumstances and when the driving table is small (fewer than 1000 rows). Choosing a driving table incorrectly can lead to severe performance degradation. See What Are Driving Tables?

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click a table, right click, and select Show Logical Diagram and select Selected Tables Only.
4. Drag and drop additional tables to the Logical Diagram.
5. Working in the Logical Diagram, hover over the first table in the join (the table representing many in the one-to-many join.), grab its handle, and drag to the table that you want to join to (the table representing one in the one-to-many join).
   A box is displayed around the table that you are joining to.
6. In Add Join, modify the values in the Cardinality, Driving Table, and Join Type fields as needed.
7. Click Add.

Identify the Physical Tables That Map to Logical Tables

The Physical Diagram shows the physical tables that map to the selected logical table or tables. The diagram also shows the physical joins between each physical table.

1. On the Home page, click Navigator and then click Semantic Models.
2. In the Semantic Models page, click a semantic model to open it.
3. Click Logical Layer.
4. Click a table or Crtl click multiple tables, right click, and select Show Physical Diagram and select Selected Tables and Direct Joins.