Support Pushdown of Joins in Virtual Schemas

Detail name	Value
Changelog Number	2167
Type	Improvement
Status	Resolved
Fix Versions	Exasol 6.2.0
Resolution Date	2019-09-27

Background

Virtual Schemas did not support pushdown of joins to the external system before version 6.2.0. Instead, only individual tables were pushed down and then joined by the local database. This is potentially slow due to the increased data transfer size.

Improvement Description

We now support the pushdown of joins to the external system, as long as the FROM clause contains only tables from the same virtual schema and no references to other tables. This requires that the adapter script used for the virtual schema also supports joins. The Exasol JDBC adapter supports this for the dialects Exasol, Oracle, PostgreSQL, and Hive since version 1.16.0. Authors of other adapters must implement the new join capabilities.

Example

Using the virtual tables a and b from the same virtual schema:

SELECT *
FROM a
    JOIN b ON a.col_1 = b.col_1
WHERE a.col_2 = 123;

Causes the following pushdown queries:

In Exasol 6.2.0	Before Exasol 6.2.0

SELECT *
FROM a
    JOIN b ON a.col_1 = b.col_1
WHERE a.col_2 = 123;

SELECT *
FROM a
WHERE a.col_2 = 123;

and

SELECT *
FROM b;

Benefits

Evaluating joins in the external system is usually faster due to reduced data transfer size.

Restrictions

• Joins won't be pushed down if the FROM clause contains tables from more than one virtual schema or references to other tables.
• All joined tables must be connected by equi conditions.
• We recommend to put the join conditions in the ON clause instead of the WHERE clause.