If a query contains a left join where the inner table is materialized view or derived table with GROUP BY and join operation is an equi-jion on grouping fields then the following
transformation can be applied to the query:
- the derived is eliminated from the query
- the subquery from the view / derived table specification with WHERE condition enforced by join condition replaces the references to the fields of the view / derived table.
Here is an example of such transformation:
This re-writing can be done on the SQL level: both queries are valid SQL queries and they are equivalent.
In more general case:
the transformation produces an illegal query. However, it's obvious how this query can be executed.
The transformation with small changes can be applied to join queries as well.
This transformation allows to avoid materialization of grouping views / derived tables and improve the performance of many equi-join queries that use these views / derived tables.