一个oracle秀逗的sql在dm7上的表现

myth8860

aio_o_ 发表于 2013-11-22 11:35
跟了下oracle，建的临时表太大的，弄了8000W行出来....

with t as(select 2*level+1 a from dual connect by level<50),
        p as (select a from t where a>10
        minus
        select t1.a*t2.a from t t1,t t2 )
        select p.a,p1.a,p2.a from p,p p1,p p2
       where p.a<p1.a and p1.a<p2.a
        and (p.a+p1.a)/2 in (select/*+no_unnest*/ a from p)
        and (p1.a+p2.a)/2 in (select/*+no_unnest*/  a from p)
        and (p.a+p2.a)/2 in (select/*+no_unnest*/  a from p)
     and (p.a+p1.a+p2.a)/3 in (select/*+no_unnest*/  a from p);

--兔子给出了解决办法

lyzhanhun

Naldonado 发表于 2013-11-22 15:26
你搞复杂了！

思路错啦 弄了个简单点的 另外求教最后输出怎么弄

declare
v_height int;
v_width  int;
v_cnt    int;
v_str    varchar;
begin
v_height = 4;
v_width  = 4;
v_cnt    = 3;
v_str    = '     *  *     * ';

with t as (select instr(v_str, '*', 1, level) as x from dual connect by level <= 3),
p as (select rownum as r from dual connect by level <= 16)
select count(*) as cnt, r as pos from p,t where abs(x % 4 - R % 4) < 2 and abs((x - 1) / 4 - (R - 1) / 4) < 2 and r <> x group by r;
--select INS(v_str , pos, 1, cast(cnt as varchar)) from res;

end;

Naldonado

lyzhanhun 发表于 2013-11-22 19:43
思路错啦 弄了个简单点的 另外求教最后输出怎么弄

declare

Var V_Width Number;
Exec :V_Width := 4;
Var V_Height Number;
EXEC :v_height := 4;
VAR V_CNT number;
Exec :V_Cnt := 3;
Var V_Str Varchar2(1000);
exec :v_str :='     *  *     * ';

With Tmp1 As (Select :V_Str V_Str,to_number(:V_Height) V_Height,to_number(:V_Width) V_Width,:V_Width * :V_Height V_Len From Dual),
Tmp2 As(Select Level R,Decode(Mod(Level,V_Width),0,V_Width,Mod(Level,V_Width)) X,Trunc((Level-1)/V_Width+1) Y,
  Decode(Substr(V_Str,Level,1),' ','90','*','9','别闹') Value
  FROM TMP1 CONNECT BY level<=V_LEN) ,
Tmp3 As (Select T2.X,T2.Y,
  Decode(T2.Value,90,(Select Count(*) From Tmp2 T
  Where T.X Between T2.X-1 And T2.X+1 And T.Y Between T2.Y-1 And T2.Y+1 And T.Value='9'),T2.Value) Value
  From Tmp2 T2)
Select Listagg(Decode(Value,'9','*',0,' ',Value),'') Within Group(Order By Y,X) str From Tmp3;

--tmp1:将需要的一些常量通过临时表保存起来，其中对宽和高转换为数字类型，便于最后的输出排序
--tmp2：通过数学公式的方法，将宽和高输出，凑成坐标。并对每个值加以分析，如果是空格定义值为90，‘*’定义为9，防止恶意份子破坏，其他值也做了输出。
--tmp3：通过标量子查询的方法，对坐标中的value值为90加以分析，算其周围8个坐标是否有9（也就是雷），求数据量
--最后用Listagg的方法，通过排序将分析好的tmp3中的value值输出

我提交的答案也搞复杂了。。。。还搞笑了。。。

lyzhanhun

Naldonado 发表于 2013-11-23 10:03
Var V_Width Number;
Exec :V_Width := 4;
Var V_Height Number;

dm 里面没有ListAgg函数

刚改了下
declare
v_height int;
v_width  int;
v_cnt    int;
v_str    varchar;
lob      clob;
begin
v_height = 4;
v_width  = 4;
v_cnt    = 3;
v_str    = '     *  *     * ';

with t as (select instr(v_str, '*', 1, level) as x from dual  connect by level <= v_cnt),
tt as (select x, x - v_width * ((x - 1) / v_width) as xx, (x - 1) / v_width as yy from t),
p as (select rownum as r from dual connect by level <= v_width * v_height),
res as  (select count(*) as cnt, r as pos from p,tt where abs(xx  - r + v_width * ((r - 1) / v_width)) < 2 and abs(yy - (R - 1) / v_width) < 2 and r not in (select x from tt)  group by r)
select (select INS(v_str , r, 1, cast(cnt as varchar)) into v_str from p where r = pos),rownum from res;
end;

不过似乎子查询里的into没有效果,周一上班再试试，下面例子可以验证

SQL>declare
2   p int;
3   begin
4   p = 4;
5   select * from (select 3 into p from dual);
6   print 'p= ' ||p;
7   end;
8   /

p= 4

行号       3
---------- -----------
1          3

Naldonado

lyzhanhun 发表于 2013-11-23 15:19
dm 里面没有ListAgg函数

刚改了下

没listagg不要紧啊，可以做个排序再wm_concat就可以了，wm_concat可以。

〇〇

lyzhanhun 发表于 2013-11-23 15:19
dm 里面没有ListAgg函数

刚改了下

原来ins有这个功能，学习了
SQL>select ins('aaa',1,1,1) from dual;

行号       INS('aaa',1,1,1)
---------- ----------------
1          1aa

已用时间: 1.365(毫秒). 执行号:13.
SQL>select ins('aaa',1,1,'b') from dual;

行号       INS('aaa',1,1,'b')
---------- ------------------
1          baa

已用时间: 1.058(毫秒). 执行号:14.
SQL>select ins('aaa',1,2,'b') from dual;

行号       INS('aaa',1,2,'b')
---------- ------------------
1          ba

已用时间: 0.913(毫秒). 执行号:15.

〇〇

Naldonado 发表于 2013-11-24 10:58
没listagg不要紧啊，可以做个排序再wm_concat就可以了，wm_concat可以。

好像是的
SQL>select wm_concat(a) from (select level a connect by level<=3);

行号       WM_CONCAT(A)
---------- ------------
1          1,2,3

已用时间: 19.893(毫秒). 执行号:16.
SQL>select wm_concat(a) from (select level a connect by level<=3 order by 1 desc);

行号       WM_CONCAT(A)
---------- ------------
1          3,2,1

已用时间: 32.419(毫秒). 执行号:17.

aio_o_

myth8860 发表于 2013-11-22 19:14
with t as(select 2*level+1 a from dual connect by level10
        minus
        select t1.a*t2.a ...

哦，可能是子查询提升那块有问题，提升之后所需的空间，按倍数增长就8500W多了。
不让它提升，就nest里面，就不会去建大临时表了。