listagg 这个函数未执行 为什么也会报错

〇〇

bell6248 发表于 2016-10-13 18:13
看了执行计划， 才知道在select list中的分析函数即使是使用了类似判断（decode, case when end），  ...

好，sort表示执行了分析函数，学习了

lzd_7993

sort看执行分析函数

newkid

12c下面执行不报错。

不周山木寸

udfrog 发表于 2016-10-13 16:55
建议你试试其他分析函数，不然你用tochar就是样本替换错误。
CBO里面的策略何止千万，谁特么知道里面的 ...

试了其他的 果然还是分析函数的锅。。。

不周山木寸

bell6248 发表于 2016-10-13 18:13
看了执行计划， 才知道在select list中的分析函数即使是使用了类似判断（decode, case when end），  ...

谢谢版主的回答 ，试了其他的分析函数也有这问题，这是不是跟分析函数的实现原理有关？

不周山木寸

newkid 发表于 2016-10-13 23:25
12c下面执行不报错。

12c没试过 不过在12c版本执行计划会有sort产生吗？

niitmb

尝试以下几种改法，都不尽人意，仍然报错
1.在LISTAGG前，添加满足条件的WHEN分支
CASE WHEN COL='1234' THEN 'OK'
        WHEN COL='5678' THEN LISTAGG(COL2,',') WITHIN GROUP(ORDER BY COL2) OVER (PARTITION BY PART)  


2.将LISTAGG放到ELSE分支
CASE WHEN COL='1234' THEN 'OK'
      ELSE
      LISTAGG(COL2,',') WITHIN GROUP(ORDER BY COL2) OVER (PARTITION BY PART)  END AS COL2LIST


3.改成simple写法
CASE COL WHEN '1234' THEN 'OK'
               WHEN '5678' THEN LISTAGG(COL2,',') WITHIN GROUP(ORDER BY COL2) OVER (PARTITION BY PART)  


4.将表T1单独创建出来再执行

这似乎说明oracle所谓的“short-circuit evaluation”并不奏效




另外，查询v$sql发现，以上4种写法对应SQL的exectuions都会增加，也再次印证listagg分支会被实际执行，因超过长度限制而报错

niitmb

尝试以下几种改法，都不尽人意，仍然报错
1.在LISTAGG前，添加满足条件的WHEN分支
CASE WHEN COL='1234' THEN 'OK'
        WHEN COL='5678' THEN LISTAGG(COL2,',') WITHIN GROUP(ORDER BY COL2) OVER (PARTITION BY PART)  


2.将LISTAGG放到ELSE分支
CASE WHEN COL='1234' THEN 'OK'
      ELSE
      LISTAGG(COL2,',') WITHIN GROUP(ORDER BY COL2) OVER (PARTITION BY PART)  END AS COL2LIST


3.改成simple写法
CASE COL WHEN '1234' THEN 'OK'
               WHEN '5678' THEN LISTAGG(COL2,',') WITHIN GROUP(ORDER BY COL2) OVER (PARTITION BY PART)  


4.将表T1单独创建出来再执行

这似乎说明oracle所谓的“short-circuit evaluation”并不奏效


另外，查询v$sql发现，以上4种写法对应SQL的exectuions都会增加，也再次印证listagg分支会被实际执行，因超过长度限制而报错

〇〇

WITH T1 AS
(
  SELECT '1234' AS COL,'甘肃省立第一中学' AS COL2,'GROUP' AS PART
  FROM DUAL
  CONNECT BY LEVEL <=200
  union all
  SELECT '5678' AS COL,'北大' AS COL2,'GROUP' AS PART
  FROM DUAL
  CONNECT BY LEVEL <=200
)
,t2 as
(select t1.*,rownum rn from t1 where col='5678' )
,t3 as
(select t1.*,rownum rn from t1 where col<>'5678' or col is null)
select COL,COL2,PART,COL2 COL2LIST from t3
union all
SELECT COL,COL2,PART,
LISTAGG(COL2,',') WITHIN GROUP(ORDER BY COL2) OVER (PARTITION BY PART)  AS COL2LIST
FROM T2;
在11.2不错

oracle_cj

用xmlagg代替listsgg解决问题，不过有点影响性能