Absolutely, this is a known optimizer bug in Oracle 12.1.0.2 where filtering on ROW_NUMBER() = 1 leads the cost-based optimizer (CBO) to incorrectly estimate the result set size as just 1 row—ruining execution plans for larger queries. Here are practical fixes you can implement:

1. Use the KEEP Aggregate Function #

Instead of relying on a row-number subquery, use Oracle's KEEP syntax to directly fetch the last record per code via aggregation. This approach lets the CBO accurately calculate the expected number of rows (equal to the distinct count of code values) instead of guessing 1.

SELECT 
    code,
    MAX(some_column) KEEP (DENSE_RANK LAST ORDER BY send_date DESC, send_id DESC) AS some_column
    -- Add other columns you need, using MAX()/MIN() with KEEP accordingly
FROM my_table
GROUP BY code;

Since your table uses (code, send_date, send_id) as the primary key, each code will have exactly one "last" record, so this returns the same results as your original query but with better cardinality estimates.

2. Force Cardinality with an Optimizer Hint #

If you prefer to keep your original row-number structure, manually tell the optimizer how many rows to expect using the CARDINALITY hint. Replace <expected_rows> with the approximate number of distinct code values in your table (you can get this with SELECT COUNT(DISTINCT code) FROM my_table;).

SELECT * 
FROM ( 
    SELECT /*+ CARDINALITY(t <expected_rows>) */
           code, some_column, 
           ROW_NUMBER() OVER (PARTITION BY code ORDER BY send_date DESC, send_id DESC) n 
    FROM my_table t 
) 
WHERE n=1;

This overrides the buggy default estimate and helps the optimizer choose join/access paths appropriate for your actual data volume.

3. Materialize the Subquery with a CTE Hint #

Use a Common Table Expression (CTE) with the MATERIALIZE hint to force Oracle to execute the row-number subquery first and store its results temporarily. The optimizer will then calculate the correct cardinality from the materialized result set.

WITH ranked_data AS (
    SELECT /*+ MATERIALIZE */
           code, some_column, 
           ROW_NUMBER() OVER (PARTITION BY code ORDER BY send_date DESC, send_id DESC) n 
    FROM my_table
)
SELECT * FROM ranked_data WHERE n=1;

This works well if the subquery's result set is manageable in memory or on disk.

4. Apply a Patch or Upgrade Oracle #

The root cause is a confirmed bug in 12.1.0.2. If possible, apply the relevant Oracle support patch or upgrade to a newer Oracle release like 12cR2 (12.2.0.1) or 19c—both of which fix this cardinality estimation issue permanently.

内容的提问来源于stack exchange，提问作者Carlos Garces