How I Cut a 4‑Minute Oracle Query to 3.5 Seconds: A Step‑by‑Step SQL Optimization Tale

Background

In August 2016 a 347‑line Oracle SQL query took more than four minutes to return the first rows and fourteen minutes to fetch only 800 rows. The query accessed two very large tables: ORDER_RELEASE (tens of millions of rows) and ORDER_RELEASE_REFNUM (hundreds of millions of rows), each more than ten times, and contained many subqueries and views.

Problem Identification

Two repetitive subquery patterns were found:

A‑type subqueries – five occurrences with identical code.

B‑type subqueries – three occurrences with identical code.

Both A and B joined ORDER_RELEASE R and ORDER_RELEASE_REFNUM O_REF on ORDER_RELEASE_GID, the primary‑key/foreign‑key relationship.

First Optimization – "Big Cut"

Replace the A‑ and B‑type subqueries with two WITH common‑table‑expressions (CTEs), drop the ORDER_RELEASE table entirely, and rewrite the IN predicates as INNER JOIN s. This reduces the number of large‑table scans.

Result: overall execution time remained around four minutes, but the drill‑down of 6,625 rows completed in under ten seconds.

Second Optimization – "Merge View"

The execution plan showed a view with an unusually high cost that was not being merged. Adding the missing SHIPMENT_GID column to the B‑type subquery and introducing an additional WITH CTE to expose the extra column allowed the optimizer to merge the view.

Result: total runtime reduced to 2 minutes 28 seconds (≈ 50 % improvement), confirming that preparing a small driver set is essential.

Third Optimization – "With‑Driven Set"

Three top‑level subqueries ( ORDER_REL, SHP, REL) each ran in ~2 seconds when executed alone, yet their combination caused severe slowdown. Each subquery was encapsulated in a WITH clause and the main query was rewritten to join the three small result sets.

Result: total execution time dropped to 3.5 seconds, and deep drilling stayed under ten seconds, meeting the performance goal.

Key Takeaways

Simplify SQL : remove unnecessary joins, DISTINCT, ORDER BY, GROUP BY, and other redundant operations.

Driver Table : ensure the driving set is small so the optimizer can filter large tables efficiently.

Set‑Based Thinking : treat tables, views, and subqueries as sets and design the query flow around concise set operations.