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:



With row_limiting_clause and multiset: with t_unique( a ) as ( select min(t1.a) from xt_test t1 union all select (select min(t1.a) from xt_test t1 where t1.a>t.a) from t_unique t where a is not null ) select/*+ use_nl(rids tt) */ * from t_unique v ,table( cast( multiset( select/*+ index_desc(tt ix_xt_test_ab) */ tt.rowid rid from xt_test tt where tt.a=v.a order by tt.b desc fetch first 5 rows only ) as sys.odcivarchar2list ) ) rids ,xt_test tt where tt.rowid=rids.column_value order by tt.a,tt.b desc [collapse]

With row_limiting_clause and lateral: with t_unique( a ) as ( select min(t1.a) from xt_test t1 union all select next_a from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a>t.a) r where t.a is not null ) select/*+ use_nl(v r t) leading(v r t) */ t.* from t_unique v ,lateral( select/*+ index_desc(tt ix_xt_test_ab) */ rowid rid from xt_test tt where tt.a=v.a order by b desc fetch first 5 rows only ) r ,xt_test t where r.rid=t.rowid [collapse]

Unfortunately, the recursive_subquery_clause with scalar subqueries sometimes doesn’t work:



Spoiler SQL> with t_unique( a ) as ( 2 select min(t1.a) 3 from xt_test t1 4 union all 5 select (select min(t1.a) from xt_test t1 where t1.a>t.a) 6 from t_unique t 7 where a is not null 8 ) 9 select/*+ use_nl(v r) */ * 10 from t_unique v 11 ,lateral( 12 select/*+ index_desc(tt ix_xt_test_ab) */ tt.* 13 from xt_test tt 14 where tt.a=v.a 15 order by tt.a, b desc 16 fetch first 5 rows only 17 ) r 18 order by r.a,r.b desc; from xt_test t1 * ERROR at line 3: ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], [], [], [], [], [] [collapse]

Spoiler SQL> with t_unique( a ) as ( 2 select min(t1.a) 3 from xt_test t1 4 union all 5 select (select min(t1.a) from xt_test t1 where t1.a>t.a) 6 from t_unique t 7 where a is not null 8 ), v as ( 9 select--+ materialize 10 * 11 from t_unique 12 ) 13 select/*+ use_nl(v r) */ * 14 from v 15 ,lateral( 16 select/*+ index_desc(tt ix_xt_test_ab) */ tt.* 17 from xt_test tt 18 where tt.a=v.a 19 order by tt.a, b desc 20 fetch first 5 rows only 21 ) r 22 order by r.a,r.b desc; 150 rows selected. Elapsed: 00:00:01.01 Statistics ---------------------------------------------------------- 10 recursive calls 8 db block gets 11824 consistent gets 1 physical reads 624 redo size 4608 bytes sent via SQL*Net to client 462 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 64 sorts (memory) 0 sorts (disk) 150 rows processed [collapse]

UPDATE: There is better solution:

Spoiler SQL> with t_unique( a ) as ( 2 select min(t1.a) 3 from xt_test t1 4 union all 5 select (select min(t1.a) from xt_test t1 where t1.a>t.a) 6 from t_unique t 7 where a is not null 8 ), v as ( 9 select * from t_unique 10 union all 11 select null from dual where 1=0 -- <<-- workaround 12 ) 13 select/*+ use_nl(v r) */ * 14 from v 15 ,lateral( 16 select/*+ index_desc(tt ix_xt_test_ab) */ tt.* 17 from xt_test tt 18 where tt.a=v.a 19 order by tt.a, b desc 20 fetch first 5 rows only 21 ) r 22 order by r.a,r.b desc; [collapse]

But I think oracle will fix it soon, because this ORA-600 can be solved easily with hint “materialize”, but it’s not so good:

And note that we can’t use now row_limiting_clause in cursor’s:



cursor(...row_limiting_clause) SQL> with 2 t_unique( a ) as ( 3 select min(t1.a) 4 from xt_test t1 5 union all 6 select next_a 7 from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a>t.a) r 8 where t.a is not null 9 ) 10 select 11 cursor( 12 select * 13 from xt_test t 14 where t.a=v.a 15 order by a,b desc 16 fetch first 5 rows only 17 ) c 18 from t_unique v 19 ; with * ERROR at line 1: ORA-03001: unimplemented feature ORA-00600: internal error code, arguments: [kokbcvb1], [], [], [], [], [], [], [], [], [], [], [] [collapse]