Virtual DataPort: Derived Views, Joins, Unions, and Query Optimization
What this domain tests
Derived views combine, filter and reshape base views without moving data. The exam probes join types and conditions, set operations, aggregation, and above all how VDP decides what runs at the source versus in its own engine.
Derived view essentials
Selection and projection are the workhorses: push filters and column lists down whenever possible. Joins take a type (inner, left outer, full outer, cross) plus a condition; set operations are union, union all, intersection and minus. Union requires the same number of fields with compatible types, and union all avoids the duplicate-elimination sort that plain union forces. Aggregation views group and compute, and flatten/unnest expand arrays and XML.
Optimization rules
VDP is a federation engine. When two joined views live in the same data source, the optimizer can push the whole join down as one source query - usually the biggest win. Cross-source joins are executed in VDP using nested loop, hash or merge strategies chosen by the cost-based optimizer, which depends on gathered statistics. Join reordering is enabled by default, aggregation can be pushed partially or fully, and the execution plan shows which rule fired.
Worked example
Orders and customers reside in Oracle; region lookup resides in Salesforce. A report joins all three and returns 12 columns. Reasoning: the orders–customers join is same-source, so it is rewritten into a single Oracle query with projection pushdown; the Salesforce side is fetched once and joined in VDP with a hash join. The optimizer will not push the Oracle–Salesforce join anywhere, so keep the Salesforce projection minimal. Taking the plan and confirming two source queries plus one VDP join is the verification step.
Traps that cost marks
- Pushing a filter on the null-supplying side of an outer join into the inner side, silently converting it to an inner join.
- Assuming join order in the SQL text is the execution order; the optimizer reorders.
- Using union where union all is semantically correct and paying for a sort.
- Expecting a cross-source join to be pushed down because the sources are "the same vendor".
- Trusting the optimizer with stale or missing statistics.