Non-optimal Pushdowns for Views with Virtual Schemas

Detail name	Value
Changelog Number	3697
Type	Improvement
Status	Resolved
Fix Versions	Exasol 6.0.12
Resolution Date	2018-08-20

Background

The view optimization in Exasol does not take the special properties of virtual schemas into account. This can lead to non-optimal pushdown queries.

Example:

CREATE SCHEMA NATIVE;
CREATE TABLE t(a int, b int);
INSERT INTO t VALUES (1, 1), (2, 2), (3, 3), (4, 4);

CREATE OR REPLACE JAVA ADAPTER SCRIPT JDBC_ADAPTER AS
%scriptclass com.exasol.adapter.jdbc.JdbcAdapter;
%jvmoption -Xms64m -Xmx64m;
%jar /buckets/bucket1/jdbc-adapter/virtualschema-jdbc-adapter.jar;
/
CREATE VIRTUAL SCHEMA VS1 USING NATIVE.JDBC_ADAPTER WITH CONNECTION_STRING='jdbc:exa:localhost:11111' USERNAME='user' PASSWORD='pwd' SCHEMA_NAME='NATIVE' IS_LOCAL='TRUE' SQL_DIALECT='EXASOL';

CREATE or replace VIEW NATIVE.PUSHDOWN_VIEW2_VS1 AS SELECT * FROM VS1.T;

EXPLAIN VIRTUAL SELECT a FROM NATIVE.PUSHDOWN_VIEW2_VS1;

The EXPLAIN VIRTUAL shows that Exasol creates the non-optimal pushdown query:

SELECT * FROM NATIVE.T;

instead of

SELECT A FROM NATIVE.T;

Improvement

The view optimization has to take the special properties of virtual schemas (e.g., a longer time to access data) into account.

Workaround

Disable the view optimization on database level using the extra database parameter -disableViewOptimization=1.