How I Turned a Half‑Hour Oracle WM_CONCAT Query into a 3‑Second Execution

Background

The author, a seasoned SQL specialist, joined a project at H Company and inherited a critical Oracle query that aggregated milestone data using the undocumented WM_CONCAT function. The query was scheduled to run on a Saturday, but initial testing showed it took more than half an hour to complete.

Problem Description

The SQL consisted of two parts: a WITH clause producing a modest result set (under 200,000 rows) and a second part that performed a GROUP BY on the WM_CONCAT output. Execution plans showed only index scans, yet the query stalled when run in the SQL window (F8).

Investigation

Running the WITH subquery alone returned results instantly, confirming the bottleneck lay in the second part.

Counting the WITH result set yielded fewer than 200,000 rows, which should not cause a half‑hour delay.

Inspection of the second part revealed the use of WM_CONCAT. Commenting out this function reduced execution time to under one second.

Root Cause

The WM_CONCAT function concatenated a large amount of data per TASK_NAME, causing severe performance degradation.

Solution

Instead of relying on WM_CONCAT, the author rewrote the query to perform all required calculations directly in SQL, leveraging window functions (e.g., LEAD) to compute milestone relationships and time differences. The revised SQL eliminated the need for Java‑side post‑processing and removed the costly concatenation step.

Running the new query in the database completed in under three seconds.

Results

Execution time dropped from >30 minutes to < 3 seconds.

Data transfer to the Java layer was eliminated, reducing network and processing overhead.

The solution proved stable across multiple releases.

Reflections

The case highlights that SQL performance issues often stem from hidden costs of undocumented functions and that many data‑processing tasks performed in application code can be expressed more efficiently in SQL. It also underscores the importance of understanding the underlying data model, using proper aggregation techniques, and being willing to replace ad‑hoc functions with standard SQL constructs.