{"id":2847,"date":"2026-10-14T07:00:00","date_gmt":"2026-10-14T05:00:00","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=2847"},"modified":"2026-10-12T17:13:25","modified_gmt":"2026-10-12T15:13:25","slug":"oracle-26ai-qualify-under-the-hood-joelkallmanday","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/oracle-26ai-qualify-under-the-hood-joelkallmanday\/","title":{"rendered":"Oracle 26ai: QUALIFY Under the Hood #JoelKallmanDay"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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

But there was one thing I kept missing: the ability to filter directly on the results of analytic functions. With the new QUALIFY<\/code> 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.<\/p>\n\n\n\n

Let’s see how it works \u2013 and then take a look at something rather surprising under the hood.<\/p>\n\n\n\n\n\n\n\n

The Problem: Filtering on Analytic Functions<\/h2>\n\n\n\n

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

We can calculate the revenue per product and year and assign a row number within each year:<\/p>\n\n\n

\nSELECT t.calendar_year,\n       p.prod_name,\n       SUM(s.amount_sold) AS revenue,\n       ROW_NUMBER() OVER (\n         PARTITION BY t.calendar_year\n         ORDER BY SUM(s.amount_sold) DESC\n       ) AS rn\nFROM sh.sales s\nJOIN sh.times t    ON t.time_id = s.time_id\nJOIN sh.products p ON p.prod_id = s.prod_id\nGROUP BY t.calendar_year, p.prod_name;\n<\/pre><\/div>\n\n\n
Listing 1: Calculating revenue and row numbers per year<\/em><\/strong><\/p>\n\n\n\n
So far, so good. But how do we restrict the result to the top five products per year?<\/p>\n\n\n\n
We cannot simply add the filter to the WHERE or HAVING clause:<\/p>\n\n\n
\nWHERE   ROW_NUMBER() OVER (PARTITION BY t.calendar_year\n        *\nERROR at line 9:\nORA-30483: window  functions are not allowed here\n<\/pre><\/div>\n\n\n
The WHERE<\/code> clause cannot reference the result of an analytic function. It is because analytic functions are evaluated after the WHERE<\/code> clause. The same applies to HAVING<\/code>, which is evaluated after grouping and aggregation, but before analytic functions. Unlike aggregate functions, analytic functions do not participate in grouping.<\/p>\n\n\n\n
Traditionally, we have to wrap the query in an inline view or a common table expression:<\/p>\n\n\n
\nWITH ranked_products AS (\n  SELECT t.calendar_year,\n         p.prod_name,\n         SUM(s.amount_sold) AS revenue,\n         ROW_NUMBER() OVER (\n           PARTITION BY t.calendar_year\n           ORDER BY SUM(s.amount_sold) DESC\n         ) AS rn\n  FROM sh.sales s\n  JOIN sh.times t    ON t.time_id = s.time_id\n  JOIN sh.products p ON p.prod_id = s.prod_id\n  GROUP BY t.calendar_year, p.prod_name\n)\nSELECT calendar_year, prod_name, revenue, rn\nFROM ranked_products\nWHERE rn <= 5\nORDER BY calendar_year, rn;\n\n---------------------------------------------------------------------------\n| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                  |          |    25 |  3902   (2)|\n|   1 |  SORT ORDER BY                    |          |    25 |  3902   (2)|\n|*  2 |   VIEW                            |          |    25 |  3901   (2)|\n|*  3 |    WINDOW SORT PUSHED RANK        |          |   237 |  3901   (2)|\n|   4 |     HASH GROUP BY                 |          |   237 |  3901   (2)|\n|*  5 |      HASH JOIN                    |          |   918K|  3847   (1)|\n|   6 |       PART JOIN FILTER CREATE     | :BF0000  |  1826 |    17   (0)|\n|   7 |        TABLE ACCESS FULL          | TIMES    |  1826 |    17   (0)|\n|*  8 |       HASH JOIN                   |          |   918K|  3828   (1)|\n|   9 |        TABLE ACCESS FULL          | PRODUCTS |    72 |     3   (0)|\n|  10 |        PARTITION RANGE JOIN-FILTER|          |   918K|  3823   (1)|\n|  11 |         TABLE ACCESS FULL         | SALES    |   918K|  3823   (1)|\n---------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - filter("RN"<=5)\n   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."CALENDAR_YEAR" ORDER BY SUM("S"."AMOUNT_SOLD") \n              DESC )<=5)\n   5 - access("T"."TIME_ID"="S"."TIME_ID")\n   8 - access("P"."PROD_ID"="S"."PROD_ID")\n<\/pre><\/div>\n\n\n
Listing 2:<\/em><\/strong> Top-5 query using a CTE and its execution plan<\/em><\/strong><\/p>\n\n\n\n
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<\/code>. 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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
The Solution: QUALIFY<\/h2>\n\n\n\n
Oracle 26ai introduces the QUALIFY<\/code> clause, which allows us to apply filtering conditions to the results of analytic functions directly within the same query block.<\/p>\n\n\n\n
Our example becomes:<\/p>\n\n\n
\nSELECT t.calendar_year,\n       p.prod_name,\n       SUM(s.amount_sold) AS revenue,\n       ROW_NUMBER() OVER (\n         PARTITION BY t.calendar_year\n         ORDER BY SUM(s.amount_sold) DESC\n       ) AS rn\nFROM sh.sales s\nJOIN sh.times t    ON t.time_id = s.time_id\nJOIN sh.products p ON p.prod_id = s.prod_id\nGROUP BY t.calendar_year, p.prod_name\nQUALIFY rn <= 5\nORDER BY t.calendar_year, rn;\n---------------------------------------------------------------------------\n| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                  |          |    25 |  3884   (2)|\n|   1 |  SORT ORDER BY                    |          |    25 |  3884   (2)|\n|*  2 |   VIEW                            |          |    25 |  3883   (2)|\n|*  3 |    WINDOW SORT PUSHED RANK        |          |   237 |  3883   (2)|\n|   4 |     HASH GROUP BY                 |          |   237 |  3883   (2)|\n|*  5 |      HASH JOIN                    |          |   918K|  3847   (1)|\n|   6 |       PART JOIN FILTER CREATE     | :BF0000  |  1826 |    17   (0)|\n|   7 |        TABLE ACCESS FULL          | TIMES    |  1826 |    17   (0)|\n|*  8 |       HASH JOIN                   |          |   918K|  3828   (1)|\n|   9 |        TABLE ACCESS FULL          | PRODUCTS |    72 |     3   (0)|\n|  10 |        PARTITION RANGE JOIN-FILTER|          |   918K|  3823   (1)|\n|  11 |         TABLE ACCESS FULL         | SALES    |   918K|  3823   (1)|\n---------------------------------------------------------------------------\n\nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - filter("from$_subquery$_006"."sys_qualify$_h$_predex$_0"=TRUE)\n   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."CALENDAR_YEAR" ORDER BY SUM("S"."AMOUNT_SOLD") \n              DESC )<=5)\n   5 - access("T"."TIME_ID"="S"."TIME_ID")\n   8 - access("P"."PROD_ID"="S"."PROD_ID")\n\n<\/pre><\/div>\n\n\n
Listing 3: Top-5 query using QUALIFY and its execution plan<\/em><\/strong><\/p>\n\n\n\n
No inline view or additional CTE needed in our SQL.<\/p>\n\n\n\n
The QUALIFY<\/code> clause is evaluated after the analytic functions, so we can reference their results directly. We can also use the alias rn<\/code>, which makes the condition particularly readable.<\/p>\n\n\n\n
This makes QUALIFY<\/code> somewhat similar to HAVING<\/code>: while HAVING<\/code> filters grouped results, QUALIFY<\/code> filters results based on analytic functions.<\/p>\n\n\n\n
And just as with HAVING<\/code>, we do not necessarily have to expose the expression used for filtering in the select list.<\/p>\n\n\n\n
For example, if we are not interested in displaying the row number:<\/p>\n\n\n
\nSELECT t.calendar_year,\n       p.prod_name,\n       SUM(s.amount_sold) AS revenue\nFROM sh.sales s\nJOIN sh.times t    ON t.time_id = s.time_id\nJOIN sh.products p ON p.prod_id = s.prod_id\nGROUP BY t.calendar_year, p.prod_name\nQUALIFY ROW_NUMBER() OVER (\n          PARTITION BY t.calendar_year\n          ORDER BY SUM(s.amount_sold) DESC\n        ) <= 5\nORDER BY t.calendar_year, revenue DESC;\n<\/pre><\/div>\n\n\n
Listing 4: QUALIFY without displaying the row number<\/em><\/strong><\/p>\n\n\n\n
This is particularly convenient for common analytical patterns such as Top-N per group, deduplication, or selecting the latest record per business key.<\/p>\n\n\n\n
In my opinion, QUALIFY<\/code> 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.<\/p>\n\n\n\n
What About the Execution Plan?<\/h2>\n\n\n\n
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<\/code> operation, so the Top-N optimization is still there.<\/p>\n\n\n\n
However, one detail in the predicate information of Listing 3 (line 34) caught my attention:<\/p>\n\n\n
\n2 - filter(\"from$_subquery$_006\".\"sys_qualify$_h$_predex$_0\"=TRUE)\n<\/pre><\/div>\n\n\n
What exactly is sys_qualify$_h$_predex$_0<\/code>? 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.<\/p>\n\n\n\n
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<\/code>, and sometimes dig into the CBO trace.<\/p>\n\n\n\n
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<\/code><\/a>, looking at Oracle’s transformed SQL can sometimes reveal interesting things \u2013 although we have to be careful when interpreting the results.<\/p>\n\n\n\n
Let’s see what Oracle makes of our QUALIFY<\/code> query and first pass it to DBMS_UTILITY.EXPAND_SQL_TEXT<\/code>:<\/p>\n\n\n
\nWITH FUNCTION get_sql(p_sql IN CLOB)\n  RETURN CLOB\nIS\n  p_raw CLOB;\nBEGIN\n  DBMS_UTILITY.EXPAND_SQL_TEXT(p_sql, p_raw);\n  RETURN p_raw;\nEND;\nSELECT get_sql(q'[\n  SELECT t.calendar_year,\n         p.prod_name,\n         SUM(s.amount_sold) AS revenue,\n         ROW_NUMBER() OVER (\n           PARTITION BY t.calendar_year\n           ORDER BY SUM(s.amount_sold) DESC\n         ) AS rn\n  FROM sh.sales s\n  JOIN sh.times t    ON t.time_id = s.time_id\n  JOIN sh.products p ON p.prod_id = s.prod_id\n  GROUP BY t.calendar_year, p.prod_name\n  QUALIFY rn <= 5\n  ORDER BY t.calendar_year, rn\n]') AS expanded_sql;\n<\/pre><\/div>\n\n\n
Listing 5: Expanding the QUALIFY query with DBMS_UTILITY.EXPAND_SQL_TEXT<\/em><\/strong><\/p>\n\n\n\n
The result is surprisingly verbose. Click to see the full SQL text:<\/p>\n\n\n
\n\nSELECT &amp;quot;A1&amp;quot;.&amp;quot;CALENDAR_YEAR&amp;quot;     &amp;quot;CALENDAR_YEAR&amp;quot;,\n         &amp;quot;A1&amp;quot;.&amp;quot;PROD_NAME&amp;quot;         &amp;quot;PROD_NAME&amp;quot;,\n         &amp;quot;A1&amp;quot;.&amp;quot;REVENUE&amp;quot;           &amp;quot;REVENUE&amp;quot;,\n         &amp;quot;A1&amp;quot;.&amp;quot;RN&amp;quot;                &amp;quot;RN&amp;quot;\n    FROM (  SELECT &amp;quot;A2&amp;quot;.&amp;quot;CALENDAR_YEAR_37&amp;quot; &amp;quot;CALENDAR_YEAR&amp;quot;,\n                   &amp;quot;A2&amp;quot;.&amp;quot;PROD_NAME_46&amp;quot; &amp;quot;PROD_NAME&amp;quot;,\n                   SUM (&amp;quot;A2&amp;quot;.&amp;quot;AMOUNT_SOLD_6&amp;quot;) &amp;quot;REVENUE&amp;quot;,\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY &amp;quot;A2&amp;quot;.&amp;quot;CALENDAR_YEAR_37&amp;quot;\n                             ORDER BY SUM (&amp;quot;A2&amp;quot;.&amp;quot;AMOUNT_SOLD_6&amp;quot;) DESC) &amp;quot;RN&amp;quot;,\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY &amp;quot;A2&amp;quot;.&amp;quot;CALENDAR_YEAR_37&amp;quot;\n                             ORDER BY SUM (&amp;quot;A2&amp;quot;.&amp;quot;AMOUNT_SOLD_6&amp;quot;) DESC) &amp;lt;=  5  &amp;quot;sys_qualify$_h$_predex$_0&amp;quot;,\n                   &amp;quot;A2&amp;quot;.&amp;quot;CALENDAR_YEAR_37&amp;quot; &amp;quot;sys_qualify$_h$_obyex$_00000&amp;quot;,\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY &amp;quot;A2&amp;quot;.&amp;quot;CALENDAR_YEAR_37&amp;quot;\n                             ORDER BY SUM (&amp;quot;A2&amp;quot;.&amp;quot;AMOUNT_SOLD_6&amp;quot;) DESC) &amp;quot;sys_qualify$_h$_obyex$_00001&amp;quot;\n              FROM (SELECT &amp;quot;A4&amp;quot;.&amp;quot;PROD_ID_0&amp;quot; &amp;quot;QCSJ_C000000000500000&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CUST_ID_1&amp;quot; &amp;quot;CUST_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;QCSJ_C000000000300000_2&amp;quot; &amp;quot;QCSJ_C000000000300000&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CHANNEL_ID_3&amp;quot; &amp;quot;CHANNEL_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;PROMO_ID_4&amp;quot; &amp;quot;PROMO_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;QUANTITY_SOLD_5&amp;quot; &amp;quot;QUANTITY_SOLD&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;AMOUNT_SOLD_6&amp;quot; &amp;quot;AMOUNT_SOLD_6&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;QCSJ_C000000000300001_7&amp;quot; &amp;quot;QCSJ_C000000000300001&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAY_NAME_8&amp;quot; &amp;quot;DAY_NAME&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAY_NUMBER_IN_WEEK_9&amp;quot; &amp;quot;DAY_NUMBER_IN_WEEK&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAY_NUMBER_IN_MONTH_10&amp;quot; &amp;quot;DAY_NUMBER_IN_MONTH&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_WEEK_NUMBER_11&amp;quot; &amp;quot;CALENDAR_WEEK_NUMBER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_WEEK_NUMBER_12&amp;quot; &amp;quot;FISCAL_WEEK_NUMBER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;WEEK_ENDING_DAY_13&amp;quot; &amp;quot;WEEK_ENDING_DAY&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;WEEK_ENDING_DAY_ID_14&amp;quot; &amp;quot;WEEK_ENDING_DAY_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_MONTH_NUMBER_15&amp;quot; &amp;quot;CALENDAR_MONTH_NUMBER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_MONTH_NUMBER_16&amp;quot; &amp;quot;FISCAL_MONTH_NUMBER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_MONTH_DESC_17&amp;quot; &amp;quot;CALENDAR_MONTH_DESC&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_MONTH_ID_18&amp;quot; &amp;quot;CALENDAR_MONTH_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_MONTH_DESC_19&amp;quot; &amp;quot;FISCAL_MONTH_DESC&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_MONTH_ID_20&amp;quot; &amp;quot;FISCAL_MONTH_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAYS_IN_CAL_MONTH_21&amp;quot; &amp;quot;DAYS_IN_CAL_MONTH&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAYS_IN_FIS_MONTH_22&amp;quot; &amp;quot;DAYS_IN_FIS_MONTH&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;END_OF_CAL_MONTH_23&amp;quot; &amp;quot;END_OF_CAL_MONTH&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;END_OF_FIS_MONTH_24&amp;quot; &amp;quot;END_OF_FIS_MONTH&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_MONTH_NAME_25&amp;quot; &amp;quot;CALENDAR_MONTH_NAME&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_MONTH_NAME_26&amp;quot; &amp;quot;FISCAL_MONTH_NAME&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_QUARTER_DESC_27&amp;quot; &amp;quot;CALENDAR_QUARTER_DESC&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_QUARTER_ID_28&amp;quot; &amp;quot;CALENDAR_QUARTER_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_QUARTER_DESC_29&amp;quot; &amp;quot;FISCAL_QUARTER_DESC&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_QUARTER_ID_30&amp;quot; &amp;quot;FISCAL_QUARTER_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAYS_IN_CAL_QUARTER_31&amp;quot; &amp;quot;DAYS_IN_CAL_QUARTER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAYS_IN_FIS_QUARTER_32&amp;quot; &amp;quot;DAYS_IN_FIS_QUARTER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;END_OF_CAL_QUARTER_33&amp;quot; &amp;quot;END_OF_CAL_QUARTER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;END_OF_FIS_QUARTER_34&amp;quot; &amp;quot;END_OF_FIS_QUARTER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_QUARTER_NUMBER_35&amp;quot; &amp;quot;CALENDAR_QUARTER_NUMBER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_QUARTER_NUMBER_36&amp;quot; &amp;quot;FISCAL_QUARTER_NUMBER&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_YEAR_37&amp;quot; &amp;quot;CALENDAR_YEAR_37&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;CALENDAR_YEAR_ID_38&amp;quot; &amp;quot;CALENDAR_YEAR_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_YEAR_39&amp;quot; &amp;quot;FISCAL_YEAR&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;FISCAL_YEAR_ID_40&amp;quot; &amp;quot;FISCAL_YEAR_ID&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAYS_IN_CAL_YEAR_41&amp;quot; &amp;quot;DAYS_IN_CAL_YEAR&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;DAYS_IN_FIS_YEAR_42&amp;quot; &amp;quot;DAYS_IN_FIS_YEAR&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;END_OF_CAL_YEAR_43&amp;quot; &amp;quot;END_OF_CAL_YEAR&amp;quot;,\n                           &amp;quot;A4&amp;quot;.&amp;quot;END_OF_FIS_YEAR_44&amp;quot; &amp;quot;END_OF_FIS_YEAR&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_ID&amp;quot; &amp;quot;QCSJ_C000000000500001&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_NAME&amp;quot; &amp;quot;PROD_NAME_46&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_DESC&amp;quot; &amp;quot;PROD_DESC&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_SUBCATEGORY&amp;quot; &amp;quot;PROD_SUBCATEGORY&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_SUBCATEGORY_ID&amp;quot; &amp;quot;PROD_SUBCATEGORY_ID&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_SUBCATEGORY_DESC&amp;quot; &amp;quot;PROD_SUBCATEGORY_DESC&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_CATEGORY&amp;quot; &amp;quot;PROD_CATEGORY&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_CATEGORY_ID&amp;quot; &amp;quot;PROD_CATEGORY_ID&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_CATEGORY_DESC&amp;quot; &amp;quot;PROD_CATEGORY_DESC&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_WEIGHT_CLASS&amp;quot; &amp;quot;PROD_WEIGHT_CLASS&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_UNIT_OF_MEASURE&amp;quot; &amp;quot;PROD_UNIT_OF_MEASURE&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_PACK_SIZE&amp;quot; &amp;quot;PROD_PACK_SIZE&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;SUPPLIER_ID&amp;quot; &amp;quot;SUPPLIER_ID&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_STATUS&amp;quot; &amp;quot;PROD_STATUS&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_LIST_PRICE&amp;quot; &amp;quot;PROD_LIST_PRICE&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_MIN_PRICE&amp;quot; &amp;quot;PROD_MIN_PRICE&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_TOTAL&amp;quot; &amp;quot;PROD_TOTAL&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_TOTAL_ID&amp;quot; &amp;quot;PROD_TOTAL_ID&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_SRC_ID&amp;quot; &amp;quot;PROD_SRC_ID&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_EFF_FROM&amp;quot; &amp;quot;PROD_EFF_FROM&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_EFF_TO&amp;quot; &amp;quot;PROD_EFF_TO&amp;quot;,\n                           &amp;quot;A3&amp;quot;.&amp;quot;PROD_VALID&amp;quot; &amp;quot;PROD_VALID&amp;quot;\n                      FROM (SELECT &amp;quot;A6&amp;quot;.&amp;quot;PROD_ID&amp;quot; &amp;quot;PROD_ID_0&amp;quot;,\n                                   &amp;quot;A6&amp;quot;.&amp;quot;CUST_ID&amp;quot; &amp;quot;CUST_ID_1&amp;quot;,\n                                   &amp;quot;A6&amp;quot;.&amp;quot;TIME_ID&amp;quot; &amp;quot;QCSJ_C000000000300000_2&amp;quot;,\n                                   &amp;quot;A6&amp;quot;.&amp;quot;CHANNEL_ID&amp;quot; &amp;quot;CHANNEL_ID_3&amp;quot;,\n                                   &amp;quot;A6&amp;quot;.&amp;quot;PROMO_ID&amp;quot; &amp;quot;PROMO_ID_4&amp;quot;,\n                                   &amp;quot;A6&amp;quot;.&amp;quot;QUANTITY_SOLD&amp;quot; &amp;quot;QUANTITY_SOLD_5&amp;quot;,\n                                   &amp;quot;A6&amp;quot;.&amp;quot;AMOUNT_SOLD&amp;quot; &amp;quot;AMOUNT_SOLD_6&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;TIME_ID&amp;quot; &amp;quot;QCSJ_C000000000300001_7&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAY_NAME&amp;quot; &amp;quot;DAY_NAME_8&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAY_NUMBER_IN_WEEK&amp;quot; &amp;quot;DAY_NUMBER_IN_WEEK_9&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAY_NUMBER_IN_MONTH&amp;quot; &amp;quot;DAY_NUMBER_IN_MONTH_10&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_WEEK_NUMBER&amp;quot; &amp;quot;CALENDAR_WEEK_NUMBER_11&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_WEEK_NUMBER&amp;quot; &amp;quot;FISCAL_WEEK_NUMBER_12&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;WEEK_ENDING_DAY&amp;quot; &amp;quot;WEEK_ENDING_DAY_13&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;WEEK_ENDING_DAY_ID&amp;quot; &amp;quot;WEEK_ENDING_DAY_ID_14&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_MONTH_NUMBER&amp;quot; &amp;quot;CALENDAR_MONTH_NUMBER_15&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_MONTH_NUMBER&amp;quot; &amp;quot;FISCAL_MONTH_NUMBER_16&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_MONTH_DESC&amp;quot; &amp;quot;CALENDAR_MONTH_DESC_17&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_MONTH_ID&amp;quot; &amp;quot;CALENDAR_MONTH_ID_18&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_MONTH_DESC&amp;quot; &amp;quot;FISCAL_MONTH_DESC_19&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_MONTH_ID&amp;quot; &amp;quot;FISCAL_MONTH_ID_20&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAYS_IN_CAL_MONTH&amp;quot; &amp;quot;DAYS_IN_CAL_MONTH_21&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAYS_IN_FIS_MONTH&amp;quot; &amp;quot;DAYS_IN_FIS_MONTH_22&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;END_OF_CAL_MONTH&amp;quot; &amp;quot;END_OF_CAL_MONTH_23&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;END_OF_FIS_MONTH&amp;quot; &amp;quot;END_OF_FIS_MONTH_24&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_MONTH_NAME&amp;quot; &amp;quot;CALENDAR_MONTH_NAME_25&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_MONTH_NAME&amp;quot; &amp;quot;FISCAL_MONTH_NAME_26&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_QUARTER_DESC&amp;quot; &amp;quot;CALENDAR_QUARTER_DESC_27&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_QUARTER_ID&amp;quot; &amp;quot;CALENDAR_QUARTER_ID_28&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_QUARTER_DESC&amp;quot; &amp;quot;FISCAL_QUARTER_DESC_29&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_QUARTER_ID&amp;quot; &amp;quot;FISCAL_QUARTER_ID_30&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAYS_IN_CAL_QUARTER&amp;quot; &amp;quot;DAYS_IN_CAL_QUARTER_31&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAYS_IN_FIS_QUARTER&amp;quot; &amp;quot;DAYS_IN_FIS_QUARTER_32&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;END_OF_CAL_QUARTER&amp;quot; &amp;quot;END_OF_CAL_QUARTER_33&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;END_OF_FIS_QUARTER&amp;quot; &amp;quot;END_OF_FIS_QUARTER_34&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_QUARTER_NUMBER&amp;quot; &amp;quot;CALENDAR_QUARTER_NUMBER_35&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_QUARTER_NUMBER&amp;quot; &amp;quot;FISCAL_QUARTER_NUMBER_36&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_YEAR&amp;quot; &amp;quot;CALENDAR_YEAR_37&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;CALENDAR_YEAR_ID&amp;quot; &amp;quot;CALENDAR_YEAR_ID_38&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_YEAR&amp;quot; &amp;quot;FISCAL_YEAR_39&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;FISCAL_YEAR_ID&amp;quot; &amp;quot;FISCAL_YEAR_ID_40&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAYS_IN_CAL_YEAR&amp;quot; &amp;quot;DAYS_IN_CAL_YEAR_41&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;DAYS_IN_FIS_YEAR&amp;quot; &amp;quot;DAYS_IN_FIS_YEAR_42&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;END_OF_CAL_YEAR&amp;quot; &amp;quot;END_OF_CAL_YEAR_43&amp;quot;,\n                                   &amp;quot;A5&amp;quot;.&amp;quot;END_OF_FIS_YEAR&amp;quot; &amp;quot;END_OF_FIS_YEAR_44&amp;quot;\n                              FROM &amp;quot;SH&amp;quot;.&amp;quot;SALES&amp;quot; &amp;quot;A6&amp;quot;, &amp;quot;SH&amp;quot;.&amp;quot;TIMES&amp;quot; &amp;quot;A5&amp;quot;\n                             WHERE &amp;quot;A5&amp;quot;.&amp;quot;TIME_ID&amp;quot; = &amp;quot;A6&amp;quot;.&amp;quot;TIME_ID&amp;quot;) &amp;quot;A4&amp;quot;,\n                           &amp;quot;SH&amp;quot;.&amp;quot;PRODUCTS&amp;quot; &amp;quot;A3&amp;quot;\n                     WHERE &amp;quot;A3&amp;quot;.&amp;quot;PROD_ID&amp;quot; = &amp;quot;A4&amp;quot;.&amp;quot;PROD_ID_0&amp;quot;) &amp;quot;A2&amp;quot;\n          GROUP BY &amp;quot;A2&amp;quot;.&amp;quot;CALENDAR_YEAR_37&amp;quot;, &amp;quot;A2&amp;quot;.&amp;quot;PROD_NAME_46&amp;quot;) &amp;quot;A1&amp;quot;\n   WHERE &amp;quot;A1&amp;quot;.&amp;quot;sys_qualify$_h$_predex$_0&amp;quot; = TRUE\nORDER BY &amp;quot;A1&amp;quot;.&amp;quot;sys_qualify$_h$_obyex$_00000&amp;quot;,\n         &amp;quot;A1&amp;quot;.&amp;quot;sys_qualify$_h$_obyex$_00001&amp;quot;\n<\/pre>\n\n\n\n
Listing 5a: Expanded SQL text (full output)<\/em><\/strong><\/p>\n\n\n\n
Oracle generates multiple nested inline views, additional column aliases, and a number of internal expressions.<\/p>\n\n\n\n
Among them are:<\/p>\n\n\n
\nROW_NUMBER() OVER (\n  PARTITION BY ...\n  ORDER BY ...\n) AS rn,\n\nROW_NUMBER() OVER (\n  PARTITION BY ...\n  ORDER BY ...\n) <= 5 AS "sys_qualify$_h$_predex$_0"\n<\/pre><\/div>\n\n\n
The outer query then applies:<\/p>\n\n\n
\nWHERE \"A1\".\"sys_qualify$_h$_predex$_0\" = TRUE\n<\/pre><\/div>\n\n\n
In other words, Oracle represents the QUALIFY<\/code> condition as an additional Boolean expression in an inner query block and filters on that expression in the outer query.<\/p>\n\n\n\n
The CBO’s Final Query<\/h2>\n\n\n\n
Let\u2019s compare this with the Final Query after Transformations from the CBO trace.<\/p>\n\n\n\n
The Final query after transformations<\/em> contains essentially the same QUALIFY<\/code> transformation, but without the extensive expansion of the joins.<\/p>\n\n\n\n
Here is the SQL query extracted from the CBO trace:<\/p>\n\n\n
\nSELECT "from$_subquery$_006"."CALENDAR_YEAR"     "CALENDAR_YEAR",\n         "from$_subquery$_006"."PROD_NAME"         "PROD_NAME",\n         "from$_subquery$_006"."REVENUE"           "REVENUE",\n         "from$_subquery$_006"."RN"                "RN"\n    FROM (  SELECT "T"."CALENDAR_YEAR"\n                       "CALENDAR_YEAR",\n                   "P"."PROD_NAME"\n                       "PROD_NAME",\n                   SUM ("S"."AMOUNT_SOLD")\n                       "REVENUE",\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY "T"."CALENDAR_YEAR"\n                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC)\n                       "RN",\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY "T"."CALENDAR_YEAR"\n                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC) <=  5  "sys_qualify$_h$_predex$_0",\n                   "T"."CALENDAR_YEAR"\n                       "sys_qualify$_h$_obyex$_00000",\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY "T"."CALENDAR_YEAR"\n                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC)\n                       "sys_qualify$_h$_obyex$_00001"\n              FROM "SH"."SALES" "S", "SH"."TIMES" "T", "SH"."PRODUCTS" "P"\n             WHERE     "P"."PROD_ID" = "S"."PROD_ID"\n                   AND "T"."TIME_ID" = "S"."TIME_ID"\n          GROUP BY "T"."CALENDAR_YEAR", "P"."PROD_NAME") "from$_subquery$_006"\n   WHERE "from$_subquery$_006"."sys_qualify$_h$_predex$_0" = TRUE\nORDER BY "from$_subquery$_006"."sys_qualify$_h$_obyex$_00000",\n         "from$_subquery$_006"."sys_qualify$_h$_obyex$_00001"\n;\n<\/pre><\/div>\n\n\n
Listing 6: Final Query after Transformations from the CBO trace<\/em><\/strong><\/p>\n\n\n\n
Compared to the output of EXPAND_SQL_TEXT<\/code>, the CBO’s final query is much easier to read. The transformation of the QUALIFY<\/code> condition, however, is essentially the same.<\/p>\n\n\n\n
Same Logic, Different Execution Plan<\/h2>\n\n\n\n
But what happens if we execute these statements directly?<\/p>\n\n\n\n
Interestingly, both produce almost identical execution plans:<\/p>\n\n\n
\n---------------------------------------------------------------------------\n| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                  |          |   237 |  3884   (2)|\n|   1 |  SORT ORDER BY                    |          |   237 |  3884   (2)|\n|*  2 |   VIEW                            |          |   237 |  3883   (2)|\n|   3 |    WINDOW SORT                    |          |   237 |  3883   (2)|\n|   4 |     HASH GROUP BY                 |          |   237 |  3883   (2)|\n|*  5 |      HASH JOIN                    |          |   918K|  3847   (1)|\n|   6 |       PART JOIN FILTER CREATE     | :BF0000  |  1826 |    17   (0)|\n|   7 |        TABLE ACCESS FULL          | TIMES    |  1826 |    17   (0)|\n|*  8 |       HASH JOIN                   |          |   918K|  3828   (1)|\n|   9 |        TABLE ACCESS FULL          | PRODUCTS |    72 |     3   (0)|\n|  10 |        PARTITION RANGE JOIN-FILTER|          |   918K|  3823   (1)|\n|  11 |         TABLE ACCESS FULL         | SALES    |   918K|  3823   (1)|\n---------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - filter(\"from$_subquery$_006\".\"sys_qualify$_h$_predex$_0\"=TRUE)\n   5 - access(\"T\".\"TIME_ID\"=\"S\".\"TIME_ID\")\n   8 - access(\"P\".\"PROD_ID\"=\"S\".\"PROD_ID\")\n<\/pre><\/div>\n\n\n
Listing 7: Execution plan of the generated SQL<\/em><\/strong><\/p>\n\n\n\n
Notice something missing? Instead of WINDOW SORT PUSHED RANK<\/code>, which we saw in the original execution plans in Listings 2 and 3, we now have a plain WINDOW SORT<\/code>.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
But why?<\/p>\n\n\n\n
Let’s make a small change to the transformed SQL. Instead of filtering on the generated Boolean expression:<\/p>\n\n\n
\nWHERE \"from$_subquery$_006\".\"sys_qualify$_h$_predex$_0\" = TRUE\n<\/pre><\/div>\n\n\n
we filter directly on the row number:<\/p>\n\n\n
\nWHERE rn <= 5\n<\/pre><\/div>\n\n\n
Everything else remains unchanged.<\/p>\n\n\n
\n  SELECT "from$_subquery$_006"."CALENDAR_YEAR"     "CALENDAR_YEAR",\n         "from$_subquery$_006"."PROD_NAME"         "PROD_NAME",\n         "from$_subquery$_006"."REVENUE"           "REVENUE",\n         "from$_subquery$_006"."RN"                "RN"\n    FROM (  SELECT "T"."CALENDAR_YEAR"\n                       "CALENDAR_YEAR",\n                   "P"."PROD_NAME"\n                       "PROD_NAME",\n                   SUM ("S"."AMOUNT_SOLD")\n                       "REVENUE",\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY "T"."CALENDAR_YEAR"\n                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC)\n                       "RN",\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY "T"."CALENDAR_YEAR"\n                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC) <=  5  "sys_qualify$_h$_predex$_0",\n                   "T"."CALENDAR_YEAR"\n                       "sys_qualify$_h$_obyex$_00000",\n                   ROW_NUMBER ()\n                       OVER (PARTITION BY "T"."CALENDAR_YEAR"\n                             ORDER BY SUM ("S"."AMOUNT_SOLD") DESC)\n                       "sys_qualify$_h$_obyex$_00001"\n              FROM "SH"."SALES" "S", "SH"."TIMES" "T", "SH"."PRODUCTS" "P"\n             WHERE     "P"."PROD_ID" = "S"."PROD_ID"\n                   AND "T"."TIME_ID" = "S"."TIME_ID"\n          GROUP BY "T"."CALENDAR_YEAR", "P"."PROD_NAME") "from$_subquery$_006"\n   WHERE rn <= 5\nORDER BY "from$_subquery$_006"."sys_qualify$_h$_obyex$_00000",\n         "from$_subquery$_006"."sys_qualify$_h$_obyex$_00001"\n;\n---------------------------------------------------------------------------\n| Id  | Operation                         | Name     | Rows  | Cost (%CPU)|\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT                  |          |    25 |  3902   (2)|\n|   1 |  SORT ORDER BY                    |          |    25 |  3902   (2)|\n|*  2 |   VIEW                            |          |    25 |  3901   (2)|\n|*  3 |    WINDOW SORT PUSHED RANK        |          |   237 |  3901   (2)|\n|   4 |     HASH GROUP BY                 |          |   237 |  3901   (2)|\n|*  5 |      HASH JOIN                    |          |   918K|  3847   (1)|\n|   6 |       PART JOIN FILTER CREATE     | :BF0000  |  1826 |    17   (0)|\n|   7 |        TABLE ACCESS FULL          | TIMES    |  1826 |    17   (0)|\n|*  8 |       HASH JOIN                   |          |   918K|  3828   (1)|\n|   9 |        TABLE ACCESS FULL          | PRODUCTS |    72 |     3   (0)|\n|  10 |        PARTITION RANGE JOIN-FILTER|          |   918K|  3823   (1)|\n|  11 |         TABLE ACCESS FULL         | SALES    |   918K|  3823   (1)|\n---------------------------------------------------------------------------\nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n   2 - filter("RN"<=5)\n   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "T"."CALENDAR_YEAR" ORDER BY SUM("S"."AMOUNT_SOLD") \n              DESC )<=5)\n   5 - access("T"."TIME_ID"="S"."TIME_ID")\n   8 - access("P"."PROD_ID"="S"."PROD_ID")\n \n<\/pre><\/div>\n\n\n
Listing 8: Restoring WINDOW SORT PUSHED RANK by filtering directly on RN<\/em><\/strong><\/p>\n\n\n\n
And suddenly, the optimizer produces:<\/p>\n\n\n
\nWINDOW SORT PUSHED RANK\n<\/pre><\/div>\n\n\n
So the difference is not caused by the additional inline view or the repeated ROW_NUMBER()<\/code> expressions. The crucial difference appears to be the way the filtering condition is represented.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
This is another example of why we should be careful when interpreting SQL produced by DBMS_UTILITY.EXPAND_SQL_TEXT<\/code> or the CBO trace. Even the Final Query after Transformations<\/em> is not necessarily optimizer-equivalent to the original statement when executed independently.<\/p>\n\n\n\n
Apparently, there is more to the optimizer’s internal representation than what we can see in the generated SQL text.<\/p>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
I really like the new QUALIFY<\/code> clause. It makes SQL more concise, avoids unnecessary query blocks, and expresses a very common analytical requirement directly where it belongs.<\/p>\n\n\n\n
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<\/strong><\/code> 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<\/strong><\/code>!<\/p>\n\n\n\n
Second, SQL produced by DBMS_UTILITY.EXPAND_SQL_TEXT<\/code> or extracted from the CBO trace should not necessarily be treated as optimizer-equivalent to the original statement. Even the Final Query after Transformations<\/em> may produce a different execution plan when executed independently.<\/p>\n\n\n\n
What looks like a straightforward query rewrite can apparently hide quite a bit of optimizer magic.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":2902,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[162,50,3],"tags":[165],"class_list":["post-2847","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-26ai","category-data-warehouse","category-sql","tag-query-optimizer"],"_links":{"self":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2847","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/comments?post=2847"}],"version-history":[{"count":71,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2847\/revisions"}],"predecessor-version":[{"id":2923,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/posts\/2847\/revisions\/2923"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media\/2902"}],"wp:attachment":[{"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/media?parent=2847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/categories?post=2847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.sqlora.com\/en\/wp-json\/wp\/v2\/tags?post=2847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}