Cross-source join spilling 22 GB of hash table
A retail group exposes a sales fact base view on an Impala data source: 480 million rows, partitioned by sales_date, with no statistics ever collected. A 6,000-row store_hierarchy dimension lives on Oracle. The reporting view joins the two on store_id and is filtered to the last 7 days by a selection placed above the join.
Runtime is 38-45 minutes. The execution trace shows a hash join with the build side in VDP memory, 22 GB spilled to local disk. The Impala cluster has 400 GB of RAM across 96 cores; the Oracle host has 32 GB.
Three proposals are on the table. A developer wants a base view in Impala that reads Oracle through a foreign table, but the cluster has no Oracle connector. A second wants to move the dimension to Impala nightly with a data movement job; the dimension changes about 200 rows a week. A third wants to raise the VDP JVM heap from 16 GB to 64 GB.
1. Diagnose why Denodo is building this hash join itself, and rank the three proposals with reasoning.
Read the worked answer
Diagnosis. The join spans two different data sources, so no single source can evaluate it; Denodo must execute the join itself, pulling at least one side across the network. The spilling shows the build side was sized wrong. With no statistics on either view, the cost-based optimizer estimates cardinality from defaults, and it can easily decide to build on the 480-million-row side. Even with the build side chosen correctly, streaming hundreds of millions of fact rows through VDP and joining row-by-row will never approach what a distributed engine does natively.
Ranking.
- Move the dimension (best). The dimension is 6,000 rows and changes 200 a week. A nightly data movement into Impala makes the join intra-source, so Denodo delegates the entire query, including the 7-day partition predicate, to Impala's own hash join with partition pruning. Runtime drops to seconds-to-minutes and VDP memory is irrelevant.
- Raise the heap (worst). 22 GB of spill does not prove that 64 GB of heap fixes anything - it proves the wrong side was built. You would buy RAM to make a design defect fit, while adding GC pressure to a node that also serves other queries. It does not reduce the 480-million-row transfer either.
- Foreign table (not viable here). The connector does not exist, and even if it did, it would make Impala fetch rows from Oracle row-by-row for a large dimension. It solves the topology problem by adding a worse one.
If data movement is politically blocked, the fallback is to at least force the join strategy and swap the sides so the 6,000-row dimension is the build side, plus collect statistics - but that is a mitigation, not a fix.
2. The developer insists the 7-day filter above the join already limits the data. What do you check in the execution plan, and what would make the filter ineffective?
Read the worked answer
A selection above a join is not automatically a pushdown. The optimizer should move an immutable, source-agnostic predicate below the join and into the delegated SQL of the fact view, but you must verify it in the trace rather than assume it.
What to check. Open the execution trace for the fact base view's delegated query and confirm it contains a literal range predicate on the partition column, e.g. WHERE sales_date BETWEEN DATE '2024-05-01' AND DATE '2024-05-07'. If the delegated SQL has no predicate, or the predicate appears only in Denodo's post-join node, partitioning is doing nothing and Impala scans every partition.
What breaks it.
- Non-sargable expressions on the partition column:
TO_CHAR(sales_date,'YYYY-MM-DD') BETWEEN ..., aCASTon the left-hand side, or a function wrapping the column. These hide the column from the partition pruner. Rewrite as a raw date range. - Parameterised or dynamic predicates that the source cannot fold at delegation time.
- A view boundary that blocks folding, e.g. a derived view with an aggregation or a union that the optimizer cannot push through.
- A join that is swapped or materialised first, so the filter is consumed after the fact.
Also verify the join is not being executed because of an implicit type mismatch - joining an INT store_id to a VARCHAR store_id can force Denodo to evaluate the join and the predicate itself. Prove pushdown by running the exact delegated SQL manually in Impala with EXPLAIN; partition pruning should be visible there.
3. You choose the nightly data movement. Describe how to implement it so the reporting view is never served from a half-loaded table, and what happens on failure.
Read the worked answer
Design. Create a data movement (or a Scheduler snapshot) that reads the Oracle dimension through its base view and writes into an Impala table in the same database as the fact. Because the dimension is 6,000 rows, a full refresh is far simpler and safer than incremental - there is no high-water mark to lose and no chance of drifting keys.
Never expose a half-loaded table. Write to a staging table and swap, so the object the reporting view reads is replaced atomically. Denodo's data movement supports a swap strategy for exactly this reason; a truncate-and-insert leaves a window where the dimension is empty and the inner join silently returns zero sales rows, which looks like a data outage rather than a load job.
Freshness contract. Schedule the movement after the Oracle dimension's own load and before the reporting window opens; add a job dependency so it cannot start early. 200 changed rows a week does not justify a more frequent schedule, so a nightly full refresh is entirely adequate.
Failure behaviour. If the movement fails, the previous table contents remain intact because nothing was swapped - the report runs on yesterday's dimension. That is the correct trade-off for a hierarchy table, but it is invisible unless instrumented: set the job to notify on failure and add an alert on the loaded row count deviating from the expected ~6,000. Do not let the failure pass silently for a week, because the store hierarchy does occasionally gain a store.