Skip to content

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… 

CBO, FIRST_ROWS and VIEW misestimate

By Franck Pachot . 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… 

Result Cache and 12c ‘fetch first n rows’

By Franck Pachot . At our bi-annual dbiXchange I was talking with Nicolas Jardot about his presentation on Result Cache (don’t forget Jérome witt session about RC at DOAG) where he has shown an unexpected behavior on ‘fetch first n rows queries’. That behavior – if it is not a bug – can also be a good thing when using…