19c: scalable Top-N queries without further hints to the query planner

By Franck Pachot

.
The FETCH FIRST … ROWS ONLY syntax arrived in Oracle 12c and is much more convenient than using a subquery with ‘ORDER BY’ wrapped in a “WHERE ROWNUM < …” around it. But as I mentioned in a previous post it required the FIRST_ROWS() hint to get correct estimations. In SQL you don’t want to overload your code for performance, right? The RDBMS optimizer does the job for you. This was a bug with this new FETCH FIRST syntax, that is fixed (See Nigel Bayliss post about this) in 19c:

SQL> select bugno,value,optimizer_feature_enable,description from  V$SYSTEM_FIX_CONTROL where bugno=22174392;

      BUGNO    VALUE    OPTIMIZER_FEATURE_ENABLE                                                      DESCRIPTION
___________ ________ ___________________________ ________________________________________________________________
   22174392        1 19.1.0                      first k row optimization for window function rownum predicate

You can use this query on database metadata to check that you have the fix enabled (VALUE=1 means that the bug is ON). Yes, Oracle Database is not open source, but a lot of information is disclosed: you can query, with SQL, all optimizer enhancements and bug fixes, and versions they appear. And you can even enable or disable them at query level:

select /*+ opt_param('_fix_control' '22174392:OFF') */
continentexp,countriesandterritories,cases from covid where cases>0
order by daterep desc, cases desc fetch first 5 rows only

This simulates previous versions where the fix were not there.

Here is an example on the COVID table I’ve created in a previous post:https://www.dbi-services.com/blog/oracle-select-from-file/
I’m running this on the Oracle Cloud 20c preview but you can run the same on any recent version.

FETCH FIRST n ROWS

I’m looking at the Top-5 countries with the highest covid cases in the latest date I have in my database. This means ORDER BY the date and number of cases (both in descending order) and fetching only the first 5 rows.

SQL> select continentexp,countriesandterritories,cases
  2  from covid where cases>0
  3  order by daterep desc, cases desc fetch first 5 rows only
  4  /


   CONTINENTEXP     COUNTRIESANDTERRITORIES    CASES
_______________ ___________________________ ________
America         United_States_of_America       57258
Asia            India                          28701
America         Brazil                         24831
Africa          South_Africa                   12058
Europe          Russia                          6615


SQL> select * from dbms_xplan.display_cursor(format=>'allstats last')
  2  /


                                                                                                         PLAN_TABLE_OUTPUT
__________________________________________________________________________________________________________________________
SQL_ID  753q1ymf0sv0w, child number 1
-------------------------------------
select continentexp,countriesandterritories,cases from covid where
cases>0 order by daterep desc, cases desc fetch first 5 rows only

Plan hash value: 1833981741

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |      1 |        |      5 |00:00:00.01 |     239 |       |       |          |
|*  1 |  VIEW                    |       |      1 |      5 |      5 |00:00:00.01 |     239 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK|       |      1 |  20267 |      5 |00:00:00.01 |     239 |   124K|   124K|  110K (0)|
|*  3 |    TABLE ACCESS FULL     | COVID |      1 |  20267 |  18150 |00:00:00.01 |     239 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("DATEREP") DESC ,INTERNAL_FUNCTION("CASES") DESC )0)

I have queried the execution plan because the RDBMS optimization is not a black box: you can ask for an explanation. Here, it reads the whole table (TABLE ACCESS FULL), SORT it, and FILTER the rows up to number 5.

This is not efficient at all. In a relational database, rather than streaming all changes to another data store, we add purpose-built indexes to scale with a new query use-case:

SQL> create index covid_date_cases on covid(daterep,cases)
  2  /
Index COVID_DATE_CASES created.

That’s all. This index will be automatically maintained with strong consistency, and transparently for any modifications to the table.

NOSORT STOPKEY

I’m running exactly the same query:

SQL> select continentexp,countriesandterritories,cases
  2  from covid where cases>0
  3  order by daterep desc, cases desc fetch first 5 rows only
  4  /

   CONTINENTEXP     COUNTRIESANDTERRITORIES    CASES
_______________ ___________________________ ________
America         United_States_of_America       57258
Asia            India                          28701
America         Brazil                         24831
Africa          South_Africa                   12058
Europe          Russia                          6615

SQL> select * from dbms_xplan.display_cursor(format=>'allstats last')
  2  /

                                                                                              PLAN_TABLE_OUTPUT
_______________________________________________________________________________________________________________
SQL_ID  753q1ymf0sv0w, child number 2
-------------------------------------
select continentexp,countriesandterritories,cases from covid where
cases>0 order by daterep desc, cases desc fetch first 5 rows only

Plan hash value: 1929215041

------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |      1 |        |      5 |00:00:00.01 |       7 |
|*  1 |  VIEW                         |                  |      1 |      5 |      5 |00:00:00.01 |       7 |
|*  2 |   WINDOW NOSORT STOPKEY       |                  |      1 |      6 |      5 |00:00:00.01 |       7 |
|   3 |    TABLE ACCESS BY INDEX ROWID| COVID            |      1 |  20267 |      5 |00:00:00.01 |       7 |
|*  4 |     INDEX FULL SCAN DESCENDING| COVID_DATE_CASES |      1 |      6 |      5 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("DATEREP") DESC,INTERNAL_FUNCTION("CASES") DESC )0)
       filter("CASES">0)

There is a big difference here. I don’t need to read the table anymore. The index provides the entries already sorted and a FULL SCAN DESCENDING will just read the first ones. Look at the ‘Buffer’ column here which is the read units. In the previous test, without the index, it was 239 blocks. But what was bad there is that the FULL TABLE SCAN has a O(N) time complexity. Now with the index, I read only 7 blocks (3 to go down the index B*Tree and 4 to get the remaining column values). And this has a constant time complexity: even with billions of rows in the table this query will read less than 10 blocks, and most of them probably from memory. This will take less than a millisecond whatever the size of the table is.

3 small conclusions here:

• If you think your RDBMS doesn’t scale, be sure that you run the latest version of it. Mainstream database have constant improvement and bug fixes.
• You don’t need another database each time you have another use case. Creating indexes can solve many problems and you have full agility when the RDBMS can create them online, and when there’s zero code to change.
• You don’t need to test on billions of rows. The execution plan operations tell you what scales or not. You don’t need to tell the optimizer how to proceed, but he tells you how he plans and how he did.