Tag Archives: lateral

Another bug with lateral

Posted on by Posted in 12c, bug, CBO, curious, oracle, troubleshooting 1,645 Page views Leave a comment

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.

Lateral view decorrelation(VW_DCL) causes wrong results with rownum

Posted on by Posted in 12c, bug, CBO, oracle, query optimizing, rownum, troubleshooting 1,987 Page views 3 Comments

Everyone knows that rownum in inline views blocks many query transformations, for example pushing/pulling predicates, scalar subquery unnesting, etc, and many people use it for such purposes as a workaround to avoid unwanted transformations(or even CBO bugs).

Obviously, the main reason of that is different calculation of rownum:

If we pull the predicate “column_value = 3″ from the following query to higher level
[sourcecode language=”sql” highlight=””]
select *
from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
where rownum <= 2
and column_value = 3;

COLUMN_VALUE
————
3
3
[/sourcecode]
we will get different results:
[sourcecode language=”sql” highlight=”8″]
select *
from (select *
from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
where rownum <= 2
)
where column_value = 3;

no rows selected
[/sourcecode]
Doc ID 62340.1

[collapse]

But we recently encountered a bug with it: lateral view with ROWNUM returns wrong results in case of lateral view decorrelation.
Compare results of this query with and without no_decorrelation hint:

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
  *
from t,
     lateral(select/*+ no_decorrelate */ rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id
            )(+)
order by 1,2;

        ID         RN
---------- ----------
         1          1
         1          2
         2
         3          1
         3          2

with 
 t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
  *
from t,
     lateral(select rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id
            )(+)
order by 1,2;

        ID         RN
---------- ----------
         1          1
         1          2
         2
         3          3
         3          4

Of course, we can draw conclusions even from these results: we can see that in case of decorrelation(query with hint) rownum was calculated before the join. But to be sure we can check optimizer’s trace 10053:

Final query after transformations:

[sourcecode language=”sql”]
******* UNPARSED QUERY IS *******
SELECT VALUE(KOKBF$2) "ID", "VW_DCL_76980902"."RN" "RN"
FROM TABLE("ODCINUMBERLIST"(1, 2, 3)) "KOKBF$2",
(SELECT ROWNUM "RN_0", VALUE(KOKBF$0) "ITEM_3"
FROM TABLE("ODCINUMBERLIST"(1, 3)) "KOKBF$0",
TABLE("ODCINUMBERLIST"(1, 1, 3, 3)) "KOKBF$1"
WHERE VALUE(KOKBF$0) = VALUE(KOKBF$1)
) "VW_DCL_76980902"
WHERE "VW_DCL_76980902"."ITEM_3"(+) = VALUE(KOKBF$2)
ORDER BY VALUE(KOKBF$2), "VW_DCL_76980902"."RN"

*************************
[/sourcecode]

[collapse]

I’ll modify it a bit just to make it more readable:
we can see that 

select
  *
from t,
     lateral(select rownum rn 
             from t1,t2 
             where t1.a=t2.b and t1.a = t.id)(+)
order by 1,2;

was transformed to 

select
  t.id, dcl.rn
from t,
     (select rownum rn 
      from t1,t2 
      where t1.a=t2.b) dcl
where dcl.a(+) = t.id
order by 1,2;

And it confirms that rownum was calculated on the different dataset (t1-t2 join) without join filter by table t.
I created SR with Severity 1 (SR #3-19117219271) more than a month ago, but unfortunately Oracle development doesn’t want to fix this bug and moreover they say that is not a bug. So I think this is a dangerous precedent and probably soon we will not be able to be sure in the calculation of rownum and old fixes…

Oracle 12c: Lateral, row_limiting_clause

Posted on by Posted in 12c, CBO, query optimizing 3,463 Page views 3 Comments

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&amp;lt;=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:

[sourcecode language=”sql”]
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&amp;gt;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
[/sourcecode]

[collapse]
With row_limiting_clause and lateral:

[sourcecode language=”sql”]
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&amp;gt;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
[/sourcecode]

[collapse]

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

Spoiler

[sourcecode language=”sql”]
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&amp;gt;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], [], [], [], [], [], [], [], [], [], []
[/sourcecode]

[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

[sourcecode language=”sql”]
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&amp;gt;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
[/sourcecode]

[collapse]

UPDATE: There is a better solution:

Spoiler

[sourcecode language=”sql” highlight=”11″]
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&amp;gt;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 — &amp;lt;&amp;lt;– 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;
[/sourcecode]

[collapse]

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

cursor(...row_limiting_clause)

[sourcecode language=”sql”]
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&amp;gt;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], [], [], [], [], [], [], [], [], [], [], []
[/sourcecode]

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

[sourcecode language=”sql”]
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&amp;gt;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

[/sourcecode]

[collapse]