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

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

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

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

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

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

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

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

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

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

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

Employee UNKNOWN Department

The Oracle Analytics query engine can't trim either side because the join has unknown cardinality.