Analytic functions have been among my favorite Oracle SQL features ever since their introduction in Oracle 8i. I’ve used them extensively over the years, and I still remember how much easier they made many SQL problems that previously required rather complicated solutions.

Tom Kyte once described analytic functions as “the coolest thing to happen to the SQL language since the introduction of the keyword SELECT.” I couldn’t agree more.

But there was one thing I kept missing: the ability to filter directly on the results of analytic functions. With the new QUALIFY clause in Oracle 26ai, this is finally possible. It’s a relatively small addition to Oracle SQL, but a long-awaited and very welcome one.

Let’s see how it works – and then take a look at something rather surprising under the hood.

The Problem: Filtering on Analytic Functions

Suppose we want to find the five best-selling products by revenue for each calendar year, using Oracle’s SH sample schema.

We can calculate the revenue per product and year and assign a row number within each year:

SELECT t.calendar_year,
       p.prod_name,
       SUM(s.amount_sold) AS revenue,
       ROW_NUMBER() OVER (
         PARTITION BY t.calendar_year
         ORDER BY SUM(s.amount_sold) DESC
       ) AS rn
FROM sh.sales s
JOIN sh.times t    ON t.time_id = s.time_id
JOIN sh.products p ON p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_name;

Listing 1: Calculating revenue and row numbers per year

So far, so good. But how do we restrict the result to the top five products per year?

We cannot simply add the filter to the WHERE or HAVING clause:

WHERE   ROW_NUMBER() OVER (PARTITION BY t.calendar_year
        *
ERROR at line 9:
ORA-30483: window  functions are not allowed here

The WHERE clause cannot reference the result of an analytic function. It is because analytic functions are evaluated after the WHERE clause. The same applies to HAVING, which is evaluated after grouping and aggregation, but before analytic functions. Unlike aggregate functions, analytic functions do not participate in grouping.

Traditionally, we have to wrap the query in an inline view or a common table expression:

WITH ranked_products AS (
  SELECT t.calendar_year,
         p.prod_name,
         SUM(s.amount_sold) AS revenue,
         ROW_NUMBER() OVER (
           PARTITION BY t.calendar_year
           ORDER BY SUM(s.amount_sold) DESC
         ) AS rn
  FROM sh.sales s
  JOIN sh.times t    ON t.time_id = s.time_id
  JOIN sh.products p ON p.prod_id = s.prod_id
  GROUP BY t.calendar_year, p.prod_name
)
SELECT calendar_year, prod_name, revenue, rn
FROM ranked_products
WHERE rn <= 5
ORDER BY calendar_year, rn;

---------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |    25 |  3902   (2)|
|   1 |  SORT ORDER BY                    |          |    25 |  3902   (2)|
|*  2 |   VIEW                            |          |    25 |  3901   (2)|
|*  3 |    WINDOW SORT PUSHED RANK        |          |   237 |  3901   (2)|
|   4 |     HASH GROUP BY                 |          |   237 |  3901   (2)|
|*  5 |      HASH JOIN                    |          |   918K|  3847   (1)|
|   6 |       PART JOIN FILTER CREATE     | :BF0000  |  1826 |    17   (0)|
|   7 |        TABLE ACCESS FULL          | TIMES    |  1826 |    17   (0)|
|*  8 |       HASH JOIN                   |          |   918K|  3828   (1)|
|   9 |        TABLE ACCESS FULL          | PRODUCTS |    72 |     3   (0)|
|  10 |        PARTITION RANGE JOIN-FILTER|          |   918K|  3823   (1)|
|  11 |         TABLE ACCESS FULL         | SALES    |   918K|  3823   (1)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("RN"<=5)
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."CALENDAR_YEAR" ORDER BY SUM("S"."AMOUNT_SOLD") 
              DESC )<=5)
   5 - access("T"."TIME_ID"="S"."TIME_ID")
   8 - access("P"."PROD_ID"="S"."PROD_ID")

Listing 2: Top-5 query using a CTE and its execution plan

There is nothing fundamentally wrong with this solution. It works, and Oracle has been able to optimize such queries efficiently for a long time. Looking at the execution plan in Listing 2 (slightly shortened for readability), the most interesting operation is WINDOW SORT PUSHED RANK. In simple terms, Oracle applies the Top-N filter while processing the analytic function, rather than calculating the row numbers for all rows first and filtering them afterwards.

But the additional query block exists only because we need to filter on the result of an analytic function. It does not add anything to the business logic.

The Solution: QUALIFY

Oracle 26ai introduces the QUALIFY clause, which allows us to apply filtering conditions to the results of analytic functions directly within the same query block.

Our example becomes:

SELECT t.calendar_year,
       p.prod_name,
       SUM(s.amount_sold) AS revenue,
       ROW_NUMBER() OVER (
         PARTITION BY t.calendar_year
         ORDER BY SUM(s.amount_sold) DESC
       ) AS rn
FROM sh.sales s
JOIN sh.times t    ON t.time_id = s.time_id
JOIN sh.products p ON p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_name
QUALIFY rn <= 5
ORDER BY t.calendar_year, rn;
---------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |    25 |  3884   (2)|
|   1 |  SORT ORDER BY                    |          |    25 |  3884   (2)|
|*  2 |   VIEW                            |          |    25 |  3883   (2)|
|*  3 |    WINDOW SORT PUSHED RANK        |          |   237 |  3883   (2)|
|   4 |     HASH GROUP BY                 |          |   237 |  3883   (2)|
|*  5 |      HASH JOIN                    |          |   918K|  3847   (1)|
|   6 |       PART JOIN FILTER CREATE     | :BF0000  |  1826 |    17   (0)|
|   7 |        TABLE ACCESS FULL          | TIMES    |  1826 |    17   (0)|
|*  8 |       HASH JOIN                   |          |   918K|  3828   (1)|
|   9 |        TABLE ACCESS FULL          | PRODUCTS |    72 |     3   (0)|
|  10 |        PARTITION RANGE JOIN-FILTER|          |   918K|  3823   (1)|
|  11 |         TABLE ACCESS FULL         | SALES    |   918K|  3823   (1)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("from$_subquery$_006"."sys_qualify$_h$_predex$_0"=TRUE)
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."CALENDAR_YEAR" ORDER BY SUM("S"."AMOUNT_SOLD") 
              DESC )<=5)
   5 - access("T"."TIME_ID"="S"."TIME_ID")
   8 - access("P"."PROD_ID"="S"."PROD_ID")

Listing 3: Top-5 query using QUALIFY and its execution plan

No inline view or additional CTE needed in our SQL.

The QUALIFY clause is evaluated after the analytic functions, so we can reference their results directly. We can also use the alias rn, which makes the condition particularly readable.

This makes QUALIFY somewhat similar to HAVING: while HAVING filters grouped results, QUALIFY filters results based on analytic functions.

And just as with HAVING, we do not necessarily have to expose the expression used for filtering in the select list.

For example, if we are not interested in displaying the row number:

SELECT t.calendar_year,
       p.prod_name,
       SUM(s.amount_sold) AS revenue
FROM sh.sales s
JOIN sh.times t    ON t.time_id = s.time_id
JOIN sh.products p ON p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_name
QUALIFY ROW_NUMBER() OVER (
          PARTITION BY t.calendar_year
          ORDER BY SUM(s.amount_sold) DESC
        ) <= 5
ORDER BY t.calendar_year, revenue DESC;

Listing 4: QUALIFY without displaying the row number

This is particularly convenient for common analytical patterns such as Top-N per group, deduplication, or selecting the latest record per business key.

In my opinion, QUALIFY is one of those features that should have been available in Oracle SQL a long time ago. It does not introduce fundamentally new functionality, but it eliminates unnecessary query blocks and makes the intention much clearer.

What About the Execution Plan?

Comparing the execution plans in Listings 2 and 3, we can see that they are almost identical. There are some minor differences in the estimated costs, but nothing significant. Most importantly, both plans contain the WINDOW SORT PUSHED RANK operation, so the Top-N optimization is still there.

However, one detail in the predicate information of Listing 3 (line 34) caught my attention:

2 - filter("from$_subquery$_006"."sys_qualify$_h$_predex$_0"=TRUE)

What exactly is sys_qualify$_h$_predex$_0? Where does this Boolean expression come from? By the way, we can also see Oracle’s long-awaited SQL Boolean data type in action here.

Whenever I investigate a new SQL feature, I usually try to find out what Oracle does with it internally. I look at query transformations, use DBMS_UTILITY.EXPAND_SQL_TEXT, and sometimes dig into the CBO trace.

Earlier this year, while preparing a presentation about different techniques for Top-N queries, I came across this very detail. And, as I already discussed in my earlier blog post about JOIN TO ONE, looking at Oracle’s transformed SQL can sometimes reveal interesting things – although we have to be careful when interpreting the results.

Let’s see what Oracle makes of our QUALIFY query and first pass it to DBMS_UTILITY.EXPAND_SQL_TEXT:

WITH FUNCTION get_sql(p_sql IN CLOB)
  RETURN CLOB
IS
  p_raw CLOB;
BEGIN
  DBMS_UTILITY.EXPAND_SQL_TEXT(p_sql, p_raw);
  RETURN p_raw;
END;
SELECT get_sql(q'[
  SELECT t.calendar_year,
         p.prod_name,
         SUM(s.amount_sold) AS revenue,
         ROW_NUMBER() OVER (
           PARTITION BY t.calendar_year
           ORDER BY SUM(s.amount_sold) DESC
         ) AS rn
  FROM sh.sales s
  JOIN sh.times t    ON t.time_id = s.time_id
  JOIN sh.products p ON p.prod_id = s.prod_id
  GROUP BY t.calendar_year, p.prod_name
  QUALIFY rn <= 5
  ORDER BY t.calendar_year, rn
]') AS expanded_sql;

Listing 5: Expanding the QUALIFY query with DBMS_UTILITY.EXPAND_SQL_TEXT

The result is surprisingly verbose. Click to see the full SQL text:

SELECT "A1"."CALENDAR_YEAR"     "CALENDAR_YEAR",
         "A1"."PROD_NAME"         "PROD_NAME",
         "A1"."REVENUE"           "REVENUE",
         "A1"."RN"                "RN"
    FROM (  SELECT "A2"."CALENDAR_YEAR_37" "CALENDAR_YEAR",
                   "A2"."PROD_NAME_46" "PROD_NAME",
                   SUM ("A2"."AMOUNT_SOLD_6") "REVENUE",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "A2"."CALENDAR_YEAR_37"
                             ORDER BY SUM ("A2"."AMOUNT_SOLD_6") DESC) "RN",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "A2"."CALENDAR_YEAR_37"
                             ORDER BY SUM ("A2"."AMOUNT_SOLD_6") DESC) <=  5  "sys_qualify$_h$_predex$_0",
                   "A2"."CALENDAR_YEAR_37" "sys_qualify$_h$_obyex$_00000",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "A2"."CALENDAR_YEAR_37"
                             ORDER BY SUM ("A2"."AMOUNT_SOLD_6") DESC) "sys_qualify$_h$_obyex$_00001"
              FROM (SELECT "A4"."PROD_ID_0" "QCSJ_C000000000500000",
                           "A4"."CUST_ID_1" "CUST_ID",
                           "A4"."QCSJ_C000000000300000_2" "QCSJ_C000000000300000",
                           "A4"."CHANNEL_ID_3" "CHANNEL_ID",
                           "A4"."PROMO_ID_4" "PROMO_ID",
                           "A4"."QUANTITY_SOLD_5" "QUANTITY_SOLD",
                           "A4"."AMOUNT_SOLD_6" "AMOUNT_SOLD_6",
                           "A4"."QCSJ_C000000000300001_7" "QCSJ_C000000000300001",
                           "A4"."DAY_NAME_8" "DAY_NAME",
                           "A4"."DAY_NUMBER_IN_WEEK_9" "DAY_NUMBER_IN_WEEK",
                           "A4"."DAY_NUMBER_IN_MONTH_10" "DAY_NUMBER_IN_MONTH",
                           "A4"."CALENDAR_WEEK_NUMBER_11" "CALENDAR_WEEK_NUMBER",
                           "A4"."FISCAL_WEEK_NUMBER_12" "FISCAL_WEEK_NUMBER",
                           "A4"."WEEK_ENDING_DAY_13" "WEEK_ENDING_DAY",
                           "A4"."WEEK_ENDING_DAY_ID_14" "WEEK_ENDING_DAY_ID",
                           "A4"."CALENDAR_MONTH_NUMBER_15" "CALENDAR_MONTH_NUMBER",
                           "A4"."FISCAL_MONTH_NUMBER_16" "FISCAL_MONTH_NUMBER",
                           "A4"."CALENDAR_MONTH_DESC_17" "CALENDAR_MONTH_DESC",
                           "A4"."CALENDAR_MONTH_ID_18" "CALENDAR_MONTH_ID",
                           "A4"."FISCAL_MONTH_DESC_19" "FISCAL_MONTH_DESC",
                           "A4"."FISCAL_MONTH_ID_20" "FISCAL_MONTH_ID",
                           "A4"."DAYS_IN_CAL_MONTH_21" "DAYS_IN_CAL_MONTH",
                           "A4"."DAYS_IN_FIS_MONTH_22" "DAYS_IN_FIS_MONTH",
                           "A4"."END_OF_CAL_MONTH_23" "END_OF_CAL_MONTH",
                           "A4"."END_OF_FIS_MONTH_24" "END_OF_FIS_MONTH",
                           "A4"."CALENDAR_MONTH_NAME_25" "CALENDAR_MONTH_NAME",
                           "A4"."FISCAL_MONTH_NAME_26" "FISCAL_MONTH_NAME",
                           "A4"."CALENDAR_QUARTER_DESC_27" "CALENDAR_QUARTER_DESC",
                           "A4"."CALENDAR_QUARTER_ID_28" "CALENDAR_QUARTER_ID",
                           "A4"."FISCAL_QUARTER_DESC_29" "FISCAL_QUARTER_DESC",
                           "A4"."FISCAL_QUARTER_ID_30" "FISCAL_QUARTER_ID",
                           "A4"."DAYS_IN_CAL_QUARTER_31" "DAYS_IN_CAL_QUARTER",
                           "A4"."DAYS_IN_FIS_QUARTER_32" "DAYS_IN_FIS_QUARTER",
                           "A4"."END_OF_CAL_QUARTER_33" "END_OF_CAL_QUARTER",
                           "A4"."END_OF_FIS_QUARTER_34" "END_OF_FIS_QUARTER",
                           "A4"."CALENDAR_QUARTER_NUMBER_35" "CALENDAR_QUARTER_NUMBER",
                           "A4"."FISCAL_QUARTER_NUMBER_36" "FISCAL_QUARTER_NUMBER",
                           "A4"."CALENDAR_YEAR_37" "CALENDAR_YEAR_37",
                           "A4"."CALENDAR_YEAR_ID_38" "CALENDAR_YEAR_ID",
                           "A4"."FISCAL_YEAR_39" "FISCAL_YEAR",
                           "A4"."FISCAL_YEAR_ID_40" "FISCAL_YEAR_ID",
                           "A4"."DAYS_IN_CAL_YEAR_41" "DAYS_IN_CAL_YEAR",
                           "A4"."DAYS_IN_FIS_YEAR_42" "DAYS_IN_FIS_YEAR",
                           "A4"."END_OF_CAL_YEAR_43" "END_OF_CAL_YEAR",
                           "A4"."END_OF_FIS_YEAR_44" "END_OF_FIS_YEAR",
                           "A3"."PROD_ID" "QCSJ_C000000000500001",
                           "A3"."PROD_NAME" "PROD_NAME_46",
                           "A3"."PROD_DESC" "PROD_DESC",
                           "A3"."PROD_SUBCATEGORY" "PROD_SUBCATEGORY",
                           "A3"."PROD_SUBCATEGORY_ID" "PROD_SUBCATEGORY_ID",
                           "A3"."PROD_SUBCATEGORY_DESC" "PROD_SUBCATEGORY_DESC",
                           "A3"."PROD_CATEGORY" "PROD_CATEGORY",
                           "A3"."PROD_CATEGORY_ID" "PROD_CATEGORY_ID",
                           "A3"."PROD_CATEGORY_DESC" "PROD_CATEGORY_DESC",
                           "A3"."PROD_WEIGHT_CLASS" "PROD_WEIGHT_CLASS",
                           "A3"."PROD_UNIT_OF_MEASURE" "PROD_UNIT_OF_MEASURE",
                           "A3"."PROD_PACK_SIZE" "PROD_PACK_SIZE",
                           "A3"."SUPPLIER_ID" "SUPPLIER_ID",
                           "A3"."PROD_STATUS" "PROD_STATUS",
                           "A3"."PROD_LIST_PRICE" "PROD_LIST_PRICE",
                           "A3"."PROD_MIN_PRICE" "PROD_MIN_PRICE",
                           "A3"."PROD_TOTAL" "PROD_TOTAL",
                           "A3"."PROD_TOTAL_ID" "PROD_TOTAL_ID",
                           "A3"."PROD_SRC_ID" "PROD_SRC_ID",
                           "A3"."PROD_EFF_FROM" "PROD_EFF_FROM",
                           "A3"."PROD_EFF_TO" "PROD_EFF_TO",
                           "A3"."PROD_VALID" "PROD_VALID"
                      FROM (SELECT "A6"."PROD_ID" "PROD_ID_0",
                                   "A6"."CUST_ID" "CUST_ID_1",
                                   "A6"."TIME_ID" "QCSJ_C000000000300000_2",
                                   "A6"."CHANNEL_ID" "CHANNEL_ID_3",
                                   "A6"."PROMO_ID" "PROMO_ID_4",
                                   "A6"."QUANTITY_SOLD" "QUANTITY_SOLD_5",
                                   "A6"."AMOUNT_SOLD" "AMOUNT_SOLD_6",
                                   "A5"."TIME_ID" "QCSJ_C000000000300001_7",
                                   "A5"."DAY_NAME" "DAY_NAME_8",
                                   "A5"."DAY_NUMBER_IN_WEEK" "DAY_NUMBER_IN_WEEK_9",
                                   "A5"."DAY_NUMBER_IN_MONTH" "DAY_NUMBER_IN_MONTH_10",
                                   "A5"."CALENDAR_WEEK_NUMBER" "CALENDAR_WEEK_NUMBER_11",
                                   "A5"."FISCAL_WEEK_NUMBER" "FISCAL_WEEK_NUMBER_12",
                                   "A5"."WEEK_ENDING_DAY" "WEEK_ENDING_DAY_13",
                                   "A5"."WEEK_ENDING_DAY_ID" "WEEK_ENDING_DAY_ID_14",
                                   "A5"."CALENDAR_MONTH_NUMBER" "CALENDAR_MONTH_NUMBER_15",
                                   "A5"."FISCAL_MONTH_NUMBER" "FISCAL_MONTH_NUMBER_16",
                                   "A5"."CALENDAR_MONTH_DESC" "CALENDAR_MONTH_DESC_17",
                                   "A5"."CALENDAR_MONTH_ID" "CALENDAR_MONTH_ID_18",
                                   "A5"."FISCAL_MONTH_DESC" "FISCAL_MONTH_DESC_19",
                                   "A5"."FISCAL_MONTH_ID" "FISCAL_MONTH_ID_20",
                                   "A5"."DAYS_IN_CAL_MONTH" "DAYS_IN_CAL_MONTH_21",
                                   "A5"."DAYS_IN_FIS_MONTH" "DAYS_IN_FIS_MONTH_22",
                                   "A5"."END_OF_CAL_MONTH" "END_OF_CAL_MONTH_23",
                                   "A5"."END_OF_FIS_MONTH" "END_OF_FIS_MONTH_24",
                                   "A5"."CALENDAR_MONTH_NAME" "CALENDAR_MONTH_NAME_25",
                                   "A5"."FISCAL_MONTH_NAME" "FISCAL_MONTH_NAME_26",
                                   "A5"."CALENDAR_QUARTER_DESC" "CALENDAR_QUARTER_DESC_27",
                                   "A5"."CALENDAR_QUARTER_ID" "CALENDAR_QUARTER_ID_28",
                                   "A5"."FISCAL_QUARTER_DESC" "FISCAL_QUARTER_DESC_29",
                                   "A5"."FISCAL_QUARTER_ID" "FISCAL_QUARTER_ID_30",
                                   "A5"."DAYS_IN_CAL_QUARTER" "DAYS_IN_CAL_QUARTER_31",
                                   "A5"."DAYS_IN_FIS_QUARTER" "DAYS_IN_FIS_QUARTER_32",
                                   "A5"."END_OF_CAL_QUARTER" "END_OF_CAL_QUARTER_33",
                                   "A5"."END_OF_FIS_QUARTER" "END_OF_FIS_QUARTER_34",
                                   "A5"."CALENDAR_QUARTER_NUMBER" "CALENDAR_QUARTER_NUMBER_35",
                                   "A5"."FISCAL_QUARTER_NUMBER" "FISCAL_QUARTER_NUMBER_36",
                                   "A5"."CALENDAR_YEAR" "CALENDAR_YEAR_37",
                                   "A5"."CALENDAR_YEAR_ID" "CALENDAR_YEAR_ID_38",
                                   "A5"."FISCAL_YEAR" "FISCAL_YEAR_39",
                                   "A5"."FISCAL_YEAR_ID" "FISCAL_YEAR_ID_40",
                                   "A5"."DAYS_IN_CAL_YEAR" "DAYS_IN_CAL_YEAR_41",
                                   "A5"."DAYS_IN_FIS_YEAR" "DAYS_IN_FIS_YEAR_42",
                                   "A5"."END_OF_CAL_YEAR" "END_OF_CAL_YEAR_43",
                                   "A5"."END_OF_FIS_YEAR" "END_OF_FIS_YEAR_44"
                              FROM "SH"."SALES" "A6", "SH"."TIMES" "A5"
                             WHERE "A5"."TIME_ID" = "A6"."TIME_ID") "A4",
                           "SH"."PRODUCTS" "A3"
                     WHERE "A3"."PROD_ID" = "A4"."PROD_ID_0") "A2"
          GROUP BY "A2"."CALENDAR_YEAR_37", "A2"."PROD_NAME_46") "A1"
   WHERE "A1"."sys_qualify$_h$_predex$_0" = TRUE
ORDER BY "A1"."sys_qualify$_h$_obyex$_00000",
         "A1"."sys_qualify$_h$_obyex$_00001"

Listing 5a: Expanded SQL text (full output)

Oracle generates multiple nested inline views, additional column aliases, and a number of internal expressions.

Among them are:

ROW_NUMBER() OVER (
  PARTITION BY ...
  ORDER BY ...
) AS rn,

ROW_NUMBER() OVER (
  PARTITION BY ...
  ORDER BY ...
) <= 5 AS "sys_qualify$_h$_predex$_0"

The outer query then applies:

WHERE "A1"."sys_qualify$_h$_predex$_0" = TRUE

In other words, Oracle represents the QUALIFY condition as an additional Boolean expression in an inner query block and filters on that expression in the outer query.

The CBO’s Final Query

Let’s compare this with the Final Query after Transformations from the CBO trace.

The Final query after transformations contains essentially the same QUALIFY transformation, but without the extensive expansion of the joins.

Here is the SQL query extracted from the CBO trace:

SELECT "from$_subquery$_006"."CALENDAR_YEAR"     "CALENDAR_YEAR",
         "from$_subquery$_006"."PROD_NAME"         "PROD_NAME",
         "from$_subquery$_006"."REVENUE"           "REVENUE",
         "from$_subquery$_006"."RN"                "RN"
    FROM (  SELECT "T"."CALENDAR_YEAR"
                       "CALENDAR_YEAR",
                   "P"."PROD_NAME"
                       "PROD_NAME",
                   SUM ("S"."AMOUNT_SOLD")
                       "REVENUE",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "T"."CALENDAR_YEAR"
                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC)
                       "RN",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "T"."CALENDAR_YEAR"
                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC) <=  5  "sys_qualify$_h$_predex$_0",
                   "T"."CALENDAR_YEAR"
                       "sys_qualify$_h$_obyex$_00000",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "T"."CALENDAR_YEAR"
                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC)
                       "sys_qualify$_h$_obyex$_00001"
              FROM "SH"."SALES" "S", "SH"."TIMES" "T", "SH"."PRODUCTS" "P"
             WHERE     "P"."PROD_ID" = "S"."PROD_ID"
                   AND "T"."TIME_ID" = "S"."TIME_ID"
          GROUP BY "T"."CALENDAR_YEAR", "P"."PROD_NAME") "from$_subquery$_006"
   WHERE "from$_subquery$_006"."sys_qualify$_h$_predex$_0" = TRUE
ORDER BY "from$_subquery$_006"."sys_qualify$_h$_obyex$_00000",
         "from$_subquery$_006"."sys_qualify$_h$_obyex$_00001"
;

Listing 6: Final Query after Transformations from the CBO trace

Compared to the output of EXPAND_SQL_TEXT, the CBO’s final query is much easier to read. The transformation of the QUALIFY condition, however, is essentially the same.

Same Logic, Different Execution Plan

But what happens if we execute these statements directly?

Interestingly, both produce almost identical execution plans:

---------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |   237 |  3884   (2)|
|   1 |  SORT ORDER BY                    |          |   237 |  3884   (2)|
|*  2 |   VIEW                            |          |   237 |  3883   (2)|
|   3 |    WINDOW SORT                    |          |   237 |  3883   (2)|
|   4 |     HASH GROUP BY                 |          |   237 |  3883   (2)|
|*  5 |      HASH JOIN                    |          |   918K|  3847   (1)|
|   6 |       PART JOIN FILTER CREATE     | :BF0000  |  1826 |    17   (0)|
|   7 |        TABLE ACCESS FULL          | TIMES    |  1826 |    17   (0)|
|*  8 |       HASH JOIN                   |          |   918K|  3828   (1)|
|   9 |        TABLE ACCESS FULL          | PRODUCTS |    72 |     3   (0)|
|  10 |        PARTITION RANGE JOIN-FILTER|          |   918K|  3823   (1)|
|  11 |         TABLE ACCESS FULL         | SALES    |   918K|  3823   (1)|
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("from$_subquery$_006"."sys_qualify$_h$_predex$_0"=TRUE)
   5 - access("T"."TIME_ID"="S"."TIME_ID")
   8 - access("P"."PROD_ID"="S"."PROD_ID")

Listing 7: Execution plan of the generated SQL

Notice something missing? Instead of WINDOW SORT PUSHED RANK, which we saw in the original execution plans in Listings 2 and 3, we now have a plain WINDOW SORT.

So, although the generated SQL produces the same results, it no longer benefits from the pushed-rank optimization. In my tests, this also resulted in measurably worse performance.

But why?

Let’s make a small change to the transformed SQL. Instead of filtering on the generated Boolean expression:

WHERE "from$_subquery$_006"."sys_qualify$_h$_predex$_0" = TRUE

we filter directly on the row number:

WHERE rn <= 5

Everything else remains unchanged.

  SELECT "from$_subquery$_006"."CALENDAR_YEAR"     "CALENDAR_YEAR",
         "from$_subquery$_006"."PROD_NAME"         "PROD_NAME",
         "from$_subquery$_006"."REVENUE"           "REVENUE",
         "from$_subquery$_006"."RN"                "RN"
    FROM (  SELECT "T"."CALENDAR_YEAR"
                       "CALENDAR_YEAR",
                   "P"."PROD_NAME"
                       "PROD_NAME",
                   SUM ("S"."AMOUNT_SOLD")
                       "REVENUE",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "T"."CALENDAR_YEAR"
                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC)
                       "RN",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "T"."CALENDAR_YEAR"
                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC) <=  5  "sys_qualify$_h$_predex$_0",
                   "T"."CALENDAR_YEAR"
                       "sys_qualify$_h$_obyex$_00000",
                   ROW_NUMBER ()
                       OVER (PARTITION BY "T"."CALENDAR_YEAR"
                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC)
                       "sys_qualify$_h$_obyex$_00001"
              FROM "SH"."SALES" "S", "SH"."TIMES" "T", "SH"."PRODUCTS" "P"
             WHERE     "P"."PROD_ID" = "S"."PROD_ID"
                   AND "T"."TIME_ID" = "S"."TIME_ID"
          GROUP BY "T"."CALENDAR_YEAR", "P"."PROD_NAME") "from$_subquery$_006"
   WHERE rn <= 5
ORDER BY "from$_subquery$_006"."sys_qualify$_h$_obyex$_00000",
         "from$_subquery$_006"."sys_qualify$_h$_obyex$_00001"
;
---------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |    25 |  3902   (2)|
|   1 |  SORT ORDER BY                    |          |    25 |  3902   (2)|
|*  2 |   VIEW                            |          |    25 |  3901   (2)|
|*  3 |    WINDOW SORT PUSHED RANK        |          |   237 |  3901   (2)|
|   4 |     HASH GROUP BY                 |          |   237 |  3901   (2)|
|*  5 |      HASH JOIN                    |          |   918K|  3847   (1)|
|   6 |       PART JOIN FILTER CREATE     | :BF0000  |  1826 |    17   (0)|
|   7 |        TABLE ACCESS FULL          | TIMES    |  1826 |    17   (0)|
|*  8 |       HASH JOIN                   |          |   918K|  3828   (1)|
|   9 |        TABLE ACCESS FULL          | PRODUCTS |    72 |     3   (0)|
|  10 |        PARTITION RANGE JOIN-FILTER|          |   918K|  3823   (1)|
|  11 |         TABLE ACCESS FULL         | SALES    |   918K|  3823   (1)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("RN"<=5)
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."CALENDAR_YEAR" ORDER BY SUM("S"."AMOUNT_SOLD") 
              DESC )<=5)
   5 - access("T"."TIME_ID"="S"."TIME_ID")
   8 - access("P"."PROD_ID"="S"."PROD_ID")
 

Listing 8: Restoring WINDOW SORT PUSHED RANK by filtering directly on RN

And suddenly, the optimizer produces:

WINDOW SORT PUSHED RANK

So the difference is not caused by the additional inline view or the repeated ROW_NUMBER() expressions. The crucial difference appears to be the way the filtering condition is represented.

In this example, the optimizer recognizes the Top-N pattern when the filter references the row number directly. But when the same condition is expressed through a Boolean flag, the pushed-rank optimization is lost.

This is another example of why we should be careful when interpreting SQL produced by DBMS_UTILITY.EXPAND_SQL_TEXT or the CBO trace. Even the Final Query after Transformations is not necessarily optimizer-equivalent to the original statement when executed independently.

Apparently, there is more to the optimizer’s internal representation than what we can see in the generated SQL text.

Conclusion

I really like the new QUALIFY clause. It makes SQL more concise, avoids unnecessary query blocks, and expresses a very common analytical requirement directly where it belongs.

The experiment also revealed two interesting things. First, expressing a Top-N condition through a Boolean flag can prevent Oracle from applying the WINDOW SORT PUSHED RANK optimization. This is something to watch out for when manually filtering on Boolean flags derived from analytic functions. But then again, why would you want to do that anyway? You now have QUALIFY!

Second, SQL produced by DBMS_UTILITY.EXPAND_SQL_TEXT or extracted from the CBO trace should not necessarily be treated as optimizer-equivalent to the original statement. Even the Final Query after Transformations may produce a different execution plan when executed independently.

What looks like a straightforward query rewrite can apparently hide quite a bit of optimizer magic.