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 a 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]