Search

Top 60 Oracle Blogs

Recent comments

first_rows

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:

CBO, FIRST_ROWS and VIEW misestimate

There are several bugs with the optimizer in FIRST_ROWS mode. Here is one I encountered during a 10.2.0.4 to 12.2.0.1 migration when a view had an ‘order by’ in its definition.

Here is the test case that reproduces the problem.

A big table:

SQL> create table DEMO1 (n constraint DEMO1_N primary key,x,y) as select 1/rownum,'x','y' from xmltable('1 to 1000000');
Table DEMO1 created.

with a view on it, and that view has an order by:

SQL> create view DEMOV as select * from DEMO1 order by n desc;
View DEMOV created.

and another table to join to:

SQL> create table DEMO2 (x constraint DEMO2_X primary key) as select dummy from dual;
Table DEMO2 created.

My query reads the view in a subquery, adds a call to a PL/SQL function, and joins the result with the other table: