Previously i showed how we can optimize getting TopN rows sorted by field “B” for each distinct value “A” with undocumented “lateral” in previous versions of Oracle RDBMS.
But now it is documented!
Very simple example:
with t as (select level a from dual connect by level<=10) select * from t ,lateral( select * from dba_objects o where object_id=t.a ) ;
Moreover, we can make now this optimization more stable and simple with row_limiting_clause:
Unfortunately, the recursive_subquery_clause with scalar subqueries sometimes doesn’t work:
But I think oracle will fix it soon, and this ORA-600 can be solved with hint “materialize”, but it’s not so good:
And note that we can’t use now row_limiting_clause in cursor’s: