11g如何实现outer apply语法

sql_tigerliu

newkid 发表于 2020-8-3 21:34
我记得以前在某个版本可以用，应该是我记错了，这个方法在11G确实和标量子查询一样受限制，那还是用PIPELIN ...

苏老师， 这个SQL，从逻辑上来看，结果集应该是lv越大， 对应的sum_v也越大，但实际不是。 如果加上materialize hint， 结果集就对了，看上去像是一个bug：我是在19.19版本测试的
with tmp as
  (select level as lv from dual connect by level <=20)
,
tmp1 as
  (select -- /*+ materialize */
       level as lv, trunc(dbms_random.value(1,10)) as val from dual connect by level <=20)
select * from tmp a
outer apply
            (select sum(val) as sum_v ,count(lv) as cnt_v
            from tmp1 b
            where a.lv>=b.lv
            )t2;

newkid

没加hint时被当成in line了，这种lateral view的特点就是会执行很多次，tmp有20行，tmp1就执行了20次，随机数每次都不一样，难免会出现sum_v比上次还更小的情形。

newkid

把这个sum用listagg拆开就看得很清楚了：


with tmp as
  (select level as lv from dual connect by level <=20)
,
tmp1 as
  (select -- /*+ materialize */
       level as lv, trunc(dbms_random.value(1,10)) as val
         from dual connect by level <=20)
select * from tmp a
outer apply
            (select sum(val) as sum_v ,listagg(b.val,'+') within group(order by lv) as val
            from tmp1 b
            where a.lv>=b.lv
            )t2;
         

sql_tigerliu

newkid 发表于 2023-8-26 20:55
把这个sum用listagg拆开就看得很清楚了：with tmp as  (select level as lv from dual connect by level

明白了，这个结果集显示更直观