Turning a 5‑Second Oracle Pagination Query into Sub‑Second Speed

Background

The author, a seasoned SQL specialist, encountered a typical pagination problem: an Oracle query returning only 15 rows still required more than five seconds to execute.

Problem Statement

The original SQL joins twelve tables, uses ORDER BY CCP_CORSS_DATA_ID, and filters primarily on the CCP_CROSS_DATA_T table. Despite the small result set, the query runs slowly because the ORDER BY forces a full sort on a large intermediate result.

Initial Observations

Performance metrics reported:

Execution time > 5 s for 15 rows.

ORDER BY identified as the bottleneck; commenting it out makes the query fast.

Target execution time: under 2 s.

Counting rows in the main table showed only 169 613 records, which is not enough to explain the severe slowdown.

Execution‑Plan Findings

The plan revealed a low overall COST but an unexpected INDEX FULL SCAN DESCENDING on the primary key PK_CCP_CROSS_DATA. The index field CCP_CORSS_DATA_ID is exactly the ORDER BY column, and the table holds about 40 million rows.

Further inspection of the index confirmed it is a unique index on CCP_CORSS_DATA_ID, the same column used for ordering.

Optimization Strategy

Instead of sorting the entire result set, the author rewrote the query to first retrieve the 15 primary‑key values, then join the remaining tables using those keys. This approach eliminates the costly sort and leverages the index efficiently.

The logical steps are:

Join CCP_CROSS_DATA_T with CPP_CHARGING_AMOUNT_T and apply all filters to obtain the full list of CCP_CORSS_DATA_ID values.

Sort this list and keep the top 15 IDs.

Use the 15 IDs to LEFT JOIN the other tables, fetching only the needed rows.

Rewritten SQL and Results

The new query uses a WITH clause to materialize the ID list, then joins the rest of the tables. The execution plan shows a reduced cost (from 560 to 249) and the query finishes in 0.7 s .

General Lessons for Pagination Queries

The case illustrates several key principles:

Identify the main ("moon") table and treat other tables as "stars"; join them after the primary keys are selected.

When the result set is small, avoid sorting large intermediate data; drive the query by the indexed primary key.

Using a WITH clause or a subquery to fetch the required IDs first can dramatically cut cost and execution time.

ORDER BY is often a symptom, not the root cause; the underlying issue is usually an unoptimized data‑access path.

By applying these ideas, the author turned a painfully slow pagination query into a sub‑second operation, demonstrating the power of index‑driven pagination and thoughtful query rewriting.