Solving Missing Location Issues in Spatial Views and SQL Performance Tuning with KEEP (DENSE_RANK LAST)

In this post, I would like to document a development issue I encountered while working on a real production service.

 

The service I develop and maintain includes a feature that displays spatial information (coordinates) on a map. Spatial data is rendered through a database View that joins a table containing location information with another table that stores measurement values collected from each location at one-minute intervals.

However, as the number of hardware devices exceeded 100 and various external factors caused intermittent data loss, an unexpected issue began to surface.

The issue was that “locations with no recent measurement data completely disappeared from the map.” The root cause was a “recent time condition” inside the View definition. Locations without recent data produced no result rows at all, which caused both the measurement data and the spatial coordinates to be excluded from the View result.

In this article, I will walk through how this problem was resolved while also improving query performance.

 

1) Problem: Locations disappear when data is missing

The View used to display spatial data has the following structure:

• Location (coordinate) table: LOCATION_TABLE
• Measurement (time-series) table: MEASURE_TABLE
• Requirement: Locations must always be displayed; measurements should appear if available, otherwise be treated as missing

The problem occurred because the View first filtered the measurement table using a “recent 1-hour data” condition, then joined the result (using a MAX-based latest-time selection). If a location had no recent measurements, it produced no rows in the View result, and therefore disappeared entirely from the map.

In short, “when measurement data was missing, the location itself disappeared.” Providing the full query would make this easier to understand, but since it contains internal business logic, I cannot share it here.

 

2) First attempt: What if we remove the time condition?

The most straightforward idea was: “If locations disappear because of the recent-time condition, let’s remove that condition.” After removing it, all locations reappeared on the map.

However, a new problem immediately emerged: the query became extremely slow.

Without the time filter, the database had to scan the entire measurement table and calculate the latest timestamp for each location on every query. This resulted in large Full Scans and expensive GROUP BY operations. Although functionally correct, the performance was unacceptable in a production environment. The map sometimes took nearly 10 seconds before spatial data appeared.

 

3) Identifying the core requirements

The core requirements could be summarized as follows:

1. Locations must always be visible.
2. Measurement data should be attached if available; otherwise NULL is acceptable.
3. We must avoid scanning the entire measurement table repeatedly.

Query locations as the primary dataset,
and attach only the latest measurement per location efficiently.

 

4) Solution strategy: Change the JOIN structure

• The location table becomes the driving (LEFT) table
• The measurement table is reduced to only the latest data and joined via LEFT JOIN

With this approach:

• Locations remain visible even when data is missing
• Measurement values appear as NULL when unavailable
• The JOIN dataset becomes much smaller, stabilizing performance

 

5) Introducing KEEP (DENSE_RANK LAST …)

The key question was how to efficiently retrieve “the latest measurement per location.” In Oracle, this is commonly achieved using the KEEP (DENSE_RANK LAST ORDER BY ...) pattern.

Basic form

MAX(value) KEEP (
  DENSE_RANK LAST ORDER BY column name
)

Conceptually, this means:

After ordering by a column (such as timestamp),
select the value associated with the most recent (LAST) rank.

This pattern is extremely useful for extracting the latest value in time-series data.

 

6) Understanding with a small example

Assume the measurement table looks like this:

DEVICE_ID	MEASURE_TIME	MEASURE_VALUE
A	10:00	12
A	11:00	15
B	09:30	8

The latest value per device can be retrieved using:

SELECT
  DEVICE_ID,
  MAX(MEASURE_TIME) AS MEASURE_TIME,
  MAX(MEASURE_VALUE) KEEP (
    DENSE_RANK LAST ORDER BY MEASURE_TIME
  ) AS MEASURE_VALUE
FROM MEASURE_TABLE
GROUP BY DEVICE_ID;

Which yields:

DEVICE_ID	MEASURE_TIME	MEASURE_VALUE
A	11:00	15
B	09:30	8

This returns exactly one row per device, containing the most recent measurement.

 

7) Final JOIN structure (conceptual)

The final approach joins locations with a reduced result set that contains only the most recent measurement per device. The measurement table is also constrained to a recent time window to prevent unnecessary scans.

FROM LOCATION_TABLE L
LEFT JOIN (
  SELECT
    DEVICE_ID,
    MAX(MEASURE_TIME) AS MEASURE_TIME,
    MAX(MEASURE_VALUE) KEEP (
      DENSE_RANK LAST ORDER BY MEASURE_TIME
    ) AS MEASURE_VALUE
  FROM MEASURE_TABLE
  WHERE MEASURE_TIME >= SYSDATE - INTERVAL '1' HOUR
  GROUP BY DEVICE_ID
) M
ON L.DEVICE_ID = M.DEVICE_ID

The key point here is not whether conditions were moved between WHERE and ON, but that the dataset was reduced before the JOIN and the location table was used as the driving table.

 

8) Why this approach is fast

1. The entire measurement table is not joined
2. The dataset is reduced to one row per device before joining
3. Unnecessary string operations (e.g., SUBSTR-based parsing) are eliminated
4. Index usage becomes more effective (e.g., (DEVICE_ID, MEASURE_TIME))

In short, performance comes down to one principle:

“How much data you reduce before the JOIN determines performance.”

 

9) Summary

When spatial services require locations to remain visible even without recent measurements, the JOIN direction and aggregation strategy deserve careful attention.

• Removing time conditions blindly can introduce severe performance issues
• If locations must always appear, the location table should be the LEFT-side driver
• Attaching only the latest measurement per location balances correctness and performance
• KEEP (DENSE_RANK LAST ...) is a powerful pattern for latest-value queries in Oracle

Thank you for reading.