Compare the results of the following query with the clause “fetch first 2 rows only”
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
sys.ku$_objnumpair(1,1),
sys.ku$_objnumpair(1,2),
sys.ku$_objnumpair(1,3),
sys.ku$_objnumpair(3,1),
sys.ku$_objnumpair(3,2),
sys.ku$_objnumpair(3,3)
)))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
*
from t,
lateral(select t1.a,t2.b
from t1,t2
where t1.a = t2.a
and t1.a = t.id
order by t2.b
fetch first 2 rows only
)(+)
order by id;
ID A B
---------- ---------- ----------
1 1 1
1 3 1
2 1 1
2 3 1
3 1 1
3 3 1
4 1 1
4 3 1
5 1 1
5 3 1
6 1 1
6 3 1
7 1 1
7 3 1
14 rows selected.
with this one (i’ve just commented out the line with “fetch-first-rows-only”:
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
sys.ku$_objnumpair(1,1),
sys.ku$_objnumpair(1,2),
sys.ku$_objnumpair(1,3),
sys.ku$_objnumpair(3,1),
sys.ku$_objnumpair(3,2),
sys.ku$_objnumpair(3,3)
)))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
*
from t,
lateral(select t1.a,t2.b
from t1,t2
where t1.a = t2.a
and t1.a = t.id
order by t2.b
-- fetch first 2 rows only
)(+)
order by id;
ID A B
---------- ---------- ----------
1 1 2
1 1 3
1 1 1
2
3 3 2
3 3 1
3 3 3
4
5
6
7
11 rows selected.
Obviously, the first query should return less rows than second one, but we can see that it returned more rows and join predicate “and t1.a = t.id” was ignored, because A and B are not empty and “A” is not equal to t.ID.