Structural optimization of skyline queries

Detail name	Value
Changelog Number	10698
Type	Improvement
Status	Resolved
Fix Versions	Exasol 7.0.3
Resolution Date	2020-10-15

Description

When the Skyline Feature was introduced, the query optimizer was made to tread carefully around the new feature. As a result, subqueries containing PREFERRING clauses cause additional materializations.

Improvement

This improvement allows the optimizer to avoid certain materializations, reducing execution time and memory footprint when nested views meet embedded skyline queries.

Example

CREATE OR REPLACE TABLE t (a int, b int);
INSERT INTO t VALUES (1,1), (1,2), (1,3), (2,1), (2,2), (3,1);

with filter as (
    select * from T where b != 2
)
, skyline as (
   select * from filter PREFERRING HIGH a
)
select b, sum(a) from skyline group by b

• Before the enhancement, this results in three materializations ("filter", "skyline" and result set)
• With the improvement, all three (sub)queries collapse into a single pipeline.