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]

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:

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]

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]

And, just for fun, with inline pl/sql function(inconsistent):

SQL> with 2 function f(v_a int) 3 return sys.ku$_vcnt 4 as 5 res sys.ku$_vcnt; 6 begin 7 select tt.rowid as rid 8 bulk collect into res 9 from xt_test tt 10 where tt.a = v_a 11 order by a,b desc 12 fetch first 5 rows only; 13 return res; 14 end; 15 16 t_unique( a ) as ( 17 select min(t1.a) 18 from xt_test t1 19 union all 20 select next_a 21 from t_unique t, lateral(select min(t1.a) next_a from xt_test t1 where t1.a>t.a) r 22 where t.a is not null 23 ) 24 select/*+ use_nl(v r t) leading(v r t) */ t.* 25 from t_unique v 26 ,table(f(v.a)) r 27 ,xt_test t 28 where r.column_value=t.rowid; 29 / 150 rows selected. Elapsed: 00:00:00.06 Statistics ---------------------------------------------------------- 31 recursive calls 0 db block gets 173 consistent gets 0 physical reads 0 redo size 5657 bytes sent via SQL*Net to client 642 bytes received via SQL*Net from client 11 SQL*Net roundtrips to/from client 32 sorts (memory) 0 sorts (disk) 150 rows processed

[collapse]

Pingback: Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting Ltée()

Pingback: Oracle Database 12c (12.1) Installation and New Features | DBLinks Consulting LTDDBLinks Consulting LTD()

Pingback: Oracle SQL | Amazing optimization of getting distinct values from the index, and TopN for each of them()