Tag Archives: lateral

Another bug with lateral

Posted on by Posted in 12c, bug, CBO, curious, oracle, troubleshooting 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 2 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

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

we will get different results:

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

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: 

******* 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"

*************************

[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 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: 

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

[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&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

[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&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], [], [], [], [], [], [], [], [], [], []

[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&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

[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&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;

[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&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], [], [], [], [], [], [], [], [], [], [], []

[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&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

[collapse]